自连接:使用自连接可以将自身表的一个镜像当作另一个表来对待,从而能够得到一些特殊的数据。
| 名称 | 类型 | 描述 |
1 | EMPNO | NUMBER(4) | 雇员编号 |
2 | ENAME | VARCHAR2(10) | 雇员姓名 |
3 | JOB | VARCHAR2(9) | 雇员职位 |
4 | MGR | NUMBER(4) | 雇员对应的领导编号,领导也是雇员 |
5 | HIREDATE | DATE | 雇员的雇佣日期 |
6 | SAL | NUMBER(7,2) | 基本工资 |
7 | COMM | NUMBER(7,2) | 奖金 |
8 | DEPTNO | NUMBER(2) | 雇员所在的部门编号 |
emp表可以想象成两张表,进行连接
SQL> select * from emp; [上司表]
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已选择14行。
SQL> select * from emp; [下属表]
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已选择14行。
注意:下属表的MGR(上司编号)字段对应上司表的EMPNO(员工编号)
简单连接:(所有没有上司、没有下属的都不列出来)
SQL> select eshang.ename 上司,exia.ename 下属
2 from emp eshang,emp exia
3 where eshang.empno=exia.mgr; //上司表里的员工编号在下属表里是领导(mgr)编号
上司 下属
---------- ----------
JONES FORD
JONES SCOTT
BLAKE TURNER
BLAKE ALLEN
BLAKE WARD
BLAKE JAMES
BLAKE MARTIN
CLARK MILLER
SCOTT ADAMS
KING BLAKE
KING JONES
上司 下属
---------- ----------
KING CLARK
FORD SMITH
已选择13行。
简单连接等价于内连接:(所有没有上司、没有下属的都不列出来)
SQL> select eshang.ename 上司,exia.ename 下属 from emp eshang inner join emp exia on eshang.empno=exia.mgr;
上司 下属
---------- ----------
FORD SMITH
BLAKE ALLEN
BLAKE WARD
KING JONES
BLAKE MARTIN
KING BLAKE
KING CLARK
JONES SCOTT
BLAKE TURNER
SCOTT ADAMS
上司 下属
---------- ----------
BLAKE JAMES
JONES FORD
CLARK MILLER
已选择13行。
左外连接:(没有下属也列出来)
SQL> select eshang.ename 上司,exia.ename 下属 from emp eshang left join emp exia on eshang.empno=exia.mgr;
上司 下属
---------- ----------
SMITH //诸如SMITH没有下属
ALLEN
WARD
JONES FORD
JONES SCOTT
MARTIN
BLAKE TURNER
BLAKE ALLEN
BLAKE WARD
BLAKE JAMES
BLAKE MARTIN
上司 下属
---------- ----------
CLARK MILLER
SCOTT ADAMS
KING BLAKE
KING JONES
KING CLARK
TURNER
ADAMS
JAMES
FORD SMITH
MILLER
已选择21行。
右外连接:(没有上司也列出来)
SQL> select eshang.ename 上司,exia.ename 下属 from emp eshang right join emp exia on eshang.empno=exia.mgr;
上司 下属
---------- ----------
JONES FORD
JONES SCOTT
BLAKE JAMES
BLAKE TURNER
BLAKE MARTIN
BLAKE WARD
BLAKE ALLEN
CLARK MILLER
SCOTT ADAMS
KING CLARK
KING BLAKE
上司 下属
---------- ----------
KING JONES
FORD SMITH
KING //KING没有上司
已选择14行。
完全外连接:(所有没有上司、没有下属的也都列出来)
SQL> select eshang.ename 上司,exia.ename 下属 from emp eshang full join emp exia on eshang.empno=exia.mgr;
上司 下属
---------- ----------
FORD SMITH
BLAKE ALLEN
BLAKE WARD
KING JONES
BLAKE MARTIN
KING BLAKE
KING CLARK
JONES SCOTT
KING
BLAKE TURNER
SCOTT ADAMS
上司 下属
---------- ----------
BLAKE JAMES
JONES FORD
CLARK MILLER
TURNER
WARD
MARTIN
ALLEN
MILLER
SMITH
ADAMS
JAMES
已选择22行。