自连接
自己和自己连接
例如,查询30号部门下的员工的领导是谁
select worker.ename,manager.ename
from emp worker,emp manager
where worker.mgr=manager.empno and worker.deptno=30;
解释:worker和manager是表emp的别名,worker.mgr=manager.empno这里是连接条件
查询结果如下:
ENAME ENAME
---------- ----------
ALLEN BLAKE
WARD BLAKE
MARTIN BLAKE
BLAKE KING
TURNER BLAKE
等值连接
查询所有部门的员工
select dept.deptno,dept.dname,ename
from dept,emp
where dept.deptno = emp.deptno
order by deptno;
查询结果如下:
DEPTNO DNAME ENAME
--------- -------------- ----------
10 ACCOUNTING CLARK
10 ACCOUNTING JAMES
10 ACCOUNTING KING
10 ACCOUNTING MILLER
20 RESEARCH FORD
20 RESEARCH JONES
20 RESEARCH SMITH
20 RESEARCH SCOTT
20 RESEARCH ADAMS
30 SALES WARD
30 SALES ALLEN
30 SALES TURNER
30 SALES BLAKE
30 SALES MARTIN
外连接
还是查询所有部门的员工,但是没有员工的部门也显示出来
select dept.deptno,dept.dname,ename
from dept,emp
where emp.deptno (+)=dept.deptno
order by deptno
查询结果:
DEPTNO DNAME ENAME
---------- -------------- ----------
10 ACCOUNTING CLARK
10 ACCOUNTING JAMES
10 ACCOUNTING KING
10 ACCOUNTING MILLER
20 RESEARCH FORD
20 RESEARCH JONES
20 RESEARCH SMITH
20 RESEARCH SCOTT
20 RESEARCH ADAMS
30 SALES WARD
30 SALES ALLEN
30 SALES TURNER
30 SALES BLAKE
30 SALES MARTIN
40 OPERATIONS