left/right join 与+使用通俗说明
来源:互联网 发布:telnet端口23在哪 编辑:程序博客网 时间:2024/06/03 07:57
(+)的使用----哪边的行数少,哪边要用+
select d.deptno,d.dname,e.ename from emp e,dept d where d.deptno=e.deptno(+);
select d.deptno,d.dname,e.ename from emp e,dept d where e.deptno(+)=d.deptno;
DEPTNO DNAME ENAME
---------- -------------- ----------
10 ACCOUNTING CLARK
10 ACCOUNTING KING
10 ACCOUNTING MILLER
20 RESEARCH JONES
20 RESEARCH FORD
20 RESEARCH ADAMS
20 RESEARCH SMITH
20 RESEARCH SCOTT
30 SALES WARD
30 SALES TURNER
30 SALES ALLEN
30 SALES JAMES
30 SALES BLAKE
30 SALES MARTIN
40 OPERATIONS
#####################################
ANSI写法
左边的表有多出的行,就是left join
右边的表有多出的行,就是right join
注意on后的连接条件和 where过滤条件,
select d.deptno,d.dname,e.ename from dept d left join emp e on d.deptno=e.deptno;
select d.deptno,d.dname,e.ename from emp e right join dept d on d.deptno=e.deptno;
DEPTNO DNAME ENAME
---------- -------------- ----------
10 ACCOUNTING CLARK
10 ACCOUNTING KING
10 ACCOUNTING MILLER
20 RESEARCH JONES
20 RESEARCH FORD
20 RESEARCH ADAMS
20 RESEARCH SMITH
20 RESEARCH SCOTT
30 SALES WARD
30 SALES TURNER
30 SALES ALLEN
30 SALES JAMES
30 SALES BLAKE
30 SALES MARTIN
40 OPERATIONS
查询出所有部门的职位为经理的雇员数
这里d.deptno=e.deptno and job='MANAGER'就都是连接条件,所以写在on后用and
select d.deptno,count(e.empno) from emp e right join dept d on d.deptno=e.deptno and job='MANAGER' group by d.deptno;
select d.deptno,count(e.empno) from emp e right join dept d on job='MANAGER' and d.deptno=e.deptno group by d.deptno;
DEPTNO COUNT(E.EMPNO)
---------- --------------
10 1
20 1
30 1
40 0
如果用d.deptno=e.deptno where job='MANAGER', where后字段成了过滤字段,
先使用连接查询出信息,然后满足WHERE后条件,再统计部门。不能查出没有员工的部门了。
select d.deptno,count(e.empno) from emp e right join dept d on d.deptno=e.deptno where job='MANAGER' group by d.deptno;
DEPTNO COUNT(E.EMPNO)
---------- --------------
30 1
20 1
10 1
查询出没有雇员的部门
首先使用d.deptno=e.deptno这个连接条件,查出的信息再使用where e.empno is null来进行过虑。
select d.deptno,d.dname from emp e right join dept d on d.deptno=e.deptno where e.empno is null;
select d.deptno,d.dname,e.ename from emp e,dept d where d.deptno=e.deptno(+);
select d.deptno,d.dname,e.ename from emp e,dept d where e.deptno(+)=d.deptno;
DEPTNO DNAME ENAME
---------- -------------- ----------
10 ACCOUNTING CLARK
10 ACCOUNTING KING
10 ACCOUNTING MILLER
20 RESEARCH JONES
20 RESEARCH FORD
20 RESEARCH ADAMS
20 RESEARCH SMITH
20 RESEARCH SCOTT
30 SALES WARD
30 SALES TURNER
30 SALES ALLEN
30 SALES JAMES
30 SALES BLAKE
30 SALES MARTIN
40 OPERATIONS
#####################################
ANSI写法
左边的表有多出的行,就是left join
右边的表有多出的行,就是right join
注意on后的连接条件和 where过滤条件,
select d.deptno,d.dname,e.ename from dept d left join emp e on d.deptno=e.deptno;
select d.deptno,d.dname,e.ename from emp e right join dept d on d.deptno=e.deptno;
DEPTNO DNAME ENAME
---------- -------------- ----------
10 ACCOUNTING CLARK
10 ACCOUNTING KING
10 ACCOUNTING MILLER
20 RESEARCH JONES
20 RESEARCH FORD
20 RESEARCH ADAMS
20 RESEARCH SMITH
20 RESEARCH SCOTT
30 SALES WARD
30 SALES TURNER
30 SALES ALLEN
30 SALES JAMES
30 SALES BLAKE
30 SALES MARTIN
40 OPERATIONS
查询出所有部门的职位为经理的雇员数
这里d.deptno=e.deptno and job='MANAGER'就都是连接条件,所以写在on后用and
select d.deptno,count(e.empno) from emp e right join dept d on d.deptno=e.deptno and job='MANAGER' group by d.deptno;
select d.deptno,count(e.empno) from emp e right join dept d on job='MANAGER' and d.deptno=e.deptno group by d.deptno;
DEPTNO COUNT(E.EMPNO)
---------- --------------
10 1
20 1
30 1
40 0
如果用d.deptno=e.deptno where job='MANAGER', where后字段成了过滤字段,
先使用连接查询出信息,然后满足WHERE后条件,再统计部门。不能查出没有员工的部门了。
select d.deptno,count(e.empno) from emp e right join dept d on d.deptno=e.deptno where job='MANAGER' group by d.deptno;
DEPTNO COUNT(E.EMPNO)
---------- --------------
30 1
20 1
10 1
查询出没有雇员的部门
首先使用d.deptno=e.deptno这个连接条件,查出的信息再使用where e.empno is null来进行过虑。
select d.deptno,d.dname from emp e right join dept d on d.deptno=e.deptno where e.empno is null;
- left/right join 与+使用通俗说明
- left/right join 与+使用通俗说明
- Left join 与 right join
- LEFT JOIN 与 RIGHT JOIN 的使用区别
- left join ,right join,inner join实例说明
- sql语句中left join、right join 以及inner join之间的使用与区别
- left join ,right join
- LEFT JOIN right join
- left join、right join
- [ASP.NET] Sql Server中的Left Join 与Right Join 与 Inner Join 与 Full Join方法使用结果
- full join 与 left join 和 right join
- mysql数据库join、left join与right join 的区别
- inner join、left join 与right join的区别
- left join、right join 与inner join的区别
- LEFT JOIN/RIGHT JOIN/INNER JOIN的使用
- sql连接left join、right join、inner join的使用
- sql inner[right][left] join 的使用
- join,left join,right join
- 字符数组存放测试
- flex
- leetcode Permutations
- MATLAB小贴士(2)
- LAV Filter 源代码分析 4: LAV Video (2)
- left/right join 与+使用通俗说明
- Dijkstra算法
- 用ajax技术实现 无刷新实现用户登录
- 34 个简单实用的 Ubuntu 快捷键
- 博客迁移GitHub
- 解决Windows和Mac系统时间不同步的方法
- hadoop namenode 格式化问题
- OS X Mavericks 免费:究竟打了谁的脸?
- Sublime配置文件翻译