一 交叉连接(笛卡尔积)
概念:对于多表查询中不指定连接条件,就会导致将一个表中的所有行都连接到另外一个表中的所有行上。
即所有情况的组合。
此连接使用的不是很多。作为了解。
SQL> select *from emp,dept;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- ---------- -------------- -------
5741 QIN_JIALI CLERK 7654 20-3月 -85 1900 600 20 0 other BOSTON
7369 SMITH CLERK 7902 17-12月-80 1200 20 0 other BOSTON
7499 ALLEN SALESMAN 7698 20-2月 -81 2000 300 30 0 other BOSTON
7521 WARD SALESMAN 7698 22-2月 -81 1650 500 30 0 other BOSTON
7566 JONES MANAGER 7839 02-4月 -81 4575 20 0 other BOSTON
7654 MARTIN SALESMAN 7698 28-9月 -81 1650 1400 40 0 other BOSTON
7698 BLAKE MANAGER 7839 01-5月 -81 4450 30 0 other BOSTON
7782 CLARK MANAGER 7839 09-6月 -81 4050 10 0 other BOSTON
7788 SCOTT ANALYST 7566 19-4月 -87 3400 20 0 other BOSTON
7839 KING PRESIDENT 17-11月-81 7000 10 0 other BOSTON
7844 TURNER SALESMAN 7698 08-9月 -81 1900 30 0 other BOSTON
7876 ADAMS CLERK 7788 23-5月 -87 1500 20 0 other BOSTON
7900 JAMES CLERK 7698 03-12月-81 1350 30 0 other BOSTON
7902 FORD ANALYST 7566 03-12月-81 3400 20 0 other BOSTON
7934 MILLER CLERK 7782 23-1月 -82 1700 10 0 other BOSTON
5741 QIN_JIALI CLERK 7654 20-3月 -85 1900 600 20 10 ACCOUNTING NEW YOR
7369 SMITH CLERK 7902 17-12月-80 1200 20 10 ACCOUNTING NEW YOR
7499 ALLEN SALESMAN 7698 20-2月 -81 2000 300 30 10 ACCOUNTING NEW YOR
7521 WARD SALESMAN 7698 22-2月 -81 1650 500 30 10 ACCOUNTING NEW YOR
7566 JONES MANAGER 7839 02-4月 -81 4575 20 10 ACCOUNTING NEW YOR
7654 MARTIN SALESMAN 7698 28-9月 -81 1650 1400 40 10 ACCOUNTING NEW YOR
7698 BLAKE MANAGER 7839 01-5月 -81 4450 30 10 ACCOUNTING NEW YOR
7782 CLARK MANAGER 7839 09-6月 -81 4050 10 10 ACCOUNTING NEW YOR
7788 SCOTT ANALYST 7566 19-4月 -87 3400 20 10 ACCOUNTING NEW YOR
7839 KING PRESIDENT 17-11月-81 7000 10 10 ACCOUNTING NEW YOR
7844 TURNER SALESMAN 7698 08-9月 -81 1900 30 10 ACCOUNTING NEW YOR
7876 ADAMS CLERK 7788 23-5月 -87 1500 20 10 ACCOUNTING NEW YOR
7900 JAMES CLERK 7698 03-12月-81 1350 30 10 ACCOUNTING NEW YOR
7902 FORD ANALYST 7566 03-12月-81 3400 20 10 ACCOUNTING NEW YOR
7934 MILLER CLERK 7782 23-1月 -82 1700 10 10 ACCOUNTING NEW YOR
5741 QIN_JIALI CLERK 7654 20-3月 -85 1900 600 20 20 RESEARCH DALLAS
7369 SMITH CLERK 7902 17-12月-80 1200 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 20-2月 -81 2000 300 30 20 RESEARCH DALLAS
7521 WARD SALESMAN 7698 22-2月 -81 1650 500 30 20 RESEARCH DALLAS
7566 JONES MANAGER 7839 02-4月 -81 4575 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 28-9月 -81 1650 1400 40 20 RESEARCH DALLAS
7698 BLAKE MANAGER 7839 01-5月 -81 4450 30 20 RESEARCH DALLAS
7782 CLARK MANAGER 7839 09-6月 -81 4050 10 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 19-4月 -87 3400 20 20 RESEARCH DALLAS
7839 KING PRESIDENT 17-11月-81 7000 10 20 RESEARCH DALLAS
7844 TURNER SALESMAN 7698 08-9月 -81 1900 30 20 RESEARCH DALLAS
7876 ADAMS CLERK 7788 23-5月 -87 1500 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 03-12月-81 1350 30 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 03-12月-81 3400 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 23-1月 -82 1700 10 20 RESEARCH DALLAS
5741 QIN_JIALI CLERK 7654 20-3月 -85 1900 600 20 30 SALES CHICAGO
7369 SMITH CLERK 7902 17-12月-80 1200 20 30 SALES CHICAGO
7499 ALLEN SALESMAN 7698 20-2月 -81 2000 300 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 22-2月 -81 1650 500 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 02-4月 -81 4575 20 30 SALES CHICAGO
7654 MARTIN SALESMAN 7698 28-9月 -81 1650 1400 40 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-5月 -81 4450 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 09-6月 -81 4050 10 30 SALES CHICAGO
7788 SCOTT ANALYST 7566 19-4月 -87 3400 20 30 SALES CHICAGO
7839 KING PRESIDENT 17-11月-81 7000 10 30 SALES CHICAGO
7844 TURNER SALESMAN 7698 08-9月 -81 1900 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 23-5月 -87 1500 20 30 SALES CHICAGO
7900 JAMES CLERK 7698 03-12月-81 1350 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 03-12月-81 3400 20 30 SALES CHICAGO
7934 MILLER CLERK 7782 23-1月 -82 1700 10 30 SALES CHICAGO
5741 QIN_JIALI CLERK 7654 20-3月 -85 1900 600 20 40 OPERATIONS BOSTON
7369 SMITH CLERK 7902 17-12月-80 1200 20 40 OPERATIONS BOSTON
7499 ALLEN SALESMAN 7698 20-2月 -81 2000 300 30 40 OPERATIONS BOSTON
7521 WARD SALESMAN 7698 22-2月 -81 1650 500 30 40 OPERATIONS BOSTON
7566 JONES MANAGER 7839 02-4月 -81 4575 20 40 OPERATIONS BOSTON
7654 MARTIN SALESMAN 7698 28-9月 -81 1650 1400 40 40 OPERATIONS BOSTON
7698 BLAKE MANAGER 7839 01-5月 -81 4450 30 40 OPERATIONS BOSTON
7782 CLARK MANAGER 7839 09-6月 -81 4050 10 40 OPERATIONS BOSTON
7788 SCOTT ANALYST 7566 19-4月 -87 3400 20 40 OPERATIONS BOSTON
7839 KING PRESIDENT 17-11月-81 7000 10 40 OPERATIONS BOSTON
7844 TURNER SALESMAN 7698 08-9月 -81 1900 30 40 OPERATIONS BOSTON
7876 ADAMS CLERK 7788 23-5月 -87 1500 20 40 OPERATIONS BOSTON
7900 JAMES CLERK 7698 03-12月-81 1350 30 40 OPERATIONS BOSTON
7902 FORD ANALYST 7566 03-12月-81 3400 20 40 OPERATIONS BOSTON
7934 MILLER CLERK 7782 23-1月 -82 1700 10 40 OPERATIONS BOSTON
已选择75行。
二 内连接
通过多张表进行字段匹配,若成功则显示,匹配不成功则不显示。
第一种情况:
SQL> select *from emp e,dept d where e.deptno = d.deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- ---------- -------------- -------------
7934 MILLER CLERK 7782 23-1月 -82 1700 10 10 ACCOUNTING NEW YORK
7782 CLARK MANAGER 7839 09-6月 -81 4050 10 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT 17-11月-81 7000 10 10 ACCOUNTING NEW YORK
7566 JONES MANAGER 7839 02-4月 -81 4575 20 20 RESEARCH DALLAS
5741 QIN_JIALI CLERK 7654 20-3月 -85 1900 600 20 20 RESEARCH DALLAS
7369 SMITH CLERK 7902 17-12月-80 1200 20 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 03-12月-81 3400 20 20 RESEARCH DALLAS
7876 ADAMS CLERK 7788 23-5月 -87 1500 20 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 19-4月 -87 3400 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 20-2月 -81 2000 300 30 30 SALES CHICAGO
7844 TURNER SALESMAN 7698 08-9月 -81 1900 30 30 SALES CHICAGO
7900 JAMES CLERK 7698 03-12月-81 1350 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 22-2月 -81 1650 500 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-5月 -81 4450 30 30 SALES CHICAGO
7654 MARTIN SALESMAN 7698 28-9月 -81 1650 1400 40 40 OPERATIONS BOSTON
已选择15行。
第二种情况:
SQL> select *from emp e inner join dept d on e.deptno=d.deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- ---------- -------------- -------------
7934 MILLER CLERK 7782 23-1月 -82 1700 10 10 ACCOUNTING NEW YORK
7782 CLARK MANAGER 7839 09-6月 -81 4050 10 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT 17-11月-81 7000 10 10 ACCOUNTING NEW YORK
7566 JONES MANAGER 7839 02-4月 -81 4575 20 20 RESEARCH DALLAS
5741 QIN_JIALI CLERK 7654 20-3月 -85 1900 600 20 20 RESEARCH DALLAS
7369 SMITH CLERK 7902 17-12月-80 1200 20 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 03-12月-81 3400 20 20 RESEARCH DALLAS
7876 ADAMS CLERK 7788 23-5月 -87 1500 20 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 19-4月 -87 3400 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 20-2月 -81 2000 300 30 30 SALES CHICAGO
7844 TURNER SALESMAN 7698 08-9月 -81 1900 30 30 SALES CHICAGO
7900 JAMES CLERK 7698 03-12月-81 1350 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 22-2月 -81 1650 500 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-5月 -81 4450 30 30 SALES CHICAGO
7654 MARTIN SALESMAN 7698 28-9月 -81 1650 1400 40 40 OPERATIONS BOSTON
已选择15行。
三 不等值连接
SQL> select *from emp e,dept d where e.deptno != d.deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- ---------- -------------- --------
5741 QIN_JIALI CLERK 7654 20-3月 -85 1900 600 20 10 ACCOUNTING NEW YORK
7369 SMITH CLERK 7902 17-12月-80 1200 20 10 ACCOUNTING NEW YORK
7499 ALLEN SALESMAN 7698 20-2月 -81 2000 300 30 10 ACCOUNTING NEW YORK
7521 WARD SALESMAN 7698 22-2月 -81 1650 500 30 10 ACCOUNTING NEW YORK
7566 JONES MANAGER 7839 02-4月 -81 4575 20 10 ACCOUNTING NEW YORK
7654 MARTIN SALESMAN 7698 28-9月 -81 1650 1400 40 10 ACCOUNTING NEW YORK
7698 BLAKE MANAGER 7839 01-5月 -81 4450 30 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 19-4月 -87 3400 20 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 08-9月 -81 1900 30 10 ACCOUNTING NEW YORK
7876 ADAMS CLERK 7788 23-5月 -87 1500 20 10 ACCOUNTING NEW YORK
7900 JAMES CLERK 7698 03-12月-81 1350 30 10 ACCOUNTING NEW YORK
7902 FORD ANALYST 7566 03-12月-81 3400 20 10 ACCOUNTING NEW YORK
7499 ALLEN SALESMAN 7698 20-2月 -81 2000 300 30 20 RESEARCH DALLAS
7521 WARD SALESMAN 7698 22-2月 -81 1650 500 30 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 28-9月 -81 1650 1400 40 20 RESEARCH DALLAS
7698 BLAKE MANAGER 7839 01-5月 -81 4450 30 20 RESEARCH DALLAS
7782 CLARK MANAGER 7839 09-6月 -81 4050 10 20 RESEARCH DALLAS
7839 KING PRESIDENT 17-11月-81 7000 10 20 RESEARCH DALLAS
7844 TURNER SALESMAN 7698 08-9月 -81 1900 30 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 03-12月-81 1350 30 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 23-1月 -82 1700 10 20 RESEARCH DALLAS
5741 QIN_JIALI CLERK 7654 20-3月 -85 1900 600 20 30 SALES CHICAGO
7369 SMITH CLERK 7902 17-12月-80 1200 20 30 SALES CHICAGO
7566 JONES MANAGER 7839 02-4月 -81 4575 20 30 SALES CHICAGO
7654 MARTIN SALESMAN 7698 28-9月 -81 1650 1400 40 30 SALES CHICAGO
7782 CLARK MANAGER 7839 09-6月 -81 4050 10 30 SALES CHICAGO
7788 SCOTT ANALYST 7566 19-4月 -87 3400 20 30 SALES CHICAGO
7839 KING PRESIDENT 17-11月-81 7000 10 30 SALES CHICAGO
7876 ADAMS CLERK 7788 23-5月 -87 1500 20 30 SALES CHICAGO
7902 FORD ANALYST 7566 03-12月-81 3400 20 30 SALES CHICAGO
7934 MILLER CLERK 7782 23-1月 -82 1700 10 30 SALES CHICAGO
5741 QIN_JIALI CLERK 7654 20-3月 -85 1900 600 20 40 OPERATIONS BOSTON
7369 SMITH CLERK 7902 17-12月-80 1200 20 40 OPERATIONS BOSTON
7499 ALLEN SALESMAN 7698 20-2月 -81 2000 300 30 40 OPERATIONS BOSTON
7521 WARD SALESMAN 7698 22-2月 -81 1650 500 30 40 OPERATIONS BOSTON
7566 JONES MANAGER 7839 02-4月 -81 4575 20 40 OPERATIONS BOSTON
7698 BLAKE MANAGER 7839 01-5月 -81 4450 30 40 OPERATIONS BOSTON
7782 CLARK MANAGER 7839 09-6月 -81 4050 10 40 OPERATIONS BOSTON
7788 SCOTT ANALYST 7566 19-4月 -87 3400 20 40 OPERATIONS BOSTON
7839 KING PRESIDENT 17-11月-81 7000 10 40 OPERATIONS BOSTON
7844 TURNER SALESMAN 7698 08-9月 -81 1900 30 40 OPERATIONS BOSTON
7876 ADAMS CLERK 7788 23-5月 -87 1500 20 40 OPERATIONS BOSTON
7900 JAMES CLERK 7698 03-12月-81 1350 30 40 OPERATIONS BOSTON
7902 FORD ANALYST 7566 03-12月-81 3400 20 40 OPERATIONS BOSTON
7934 MILLER CLERK 7782 23-1月 -82 1700 10 40 OPERATIONS BOSTON
已选择45行。
四 外连接
a) 左外连接
概念:以左表为基准,去匹配右表,若匹配成功,则显示;若匹配不成功,则只显示左表部分(无数据部分则用null填充);
针对oracle,有两种写法:
a) select *from emp e left outer join dept d on e.deptno=d.deptno;
SQL> select *from emp e left outer join dept d on e.deptno=d.deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- ---------- -------------- ------------
7934 MILLER CLERK 7782 23-1月 -82 1700 10 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT 17-11月-81 7000 10 10 ACCOUNTING NEW YORK
7782 CLARK MANAGER 7839 09-6月 -81 4050 10 10 ACCOUNTING NEW YORK
7902 FORD ANALYST 7566 03-12月-81 3400 20 20 RESEARCH DALLAS
7876 ADAMS CLERK 7788 23-5月 -87 1500 20 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 19-4月 -87 3400 20 20 RESEARCH DALLAS
7566 JONES MANAGER 7839 02-4月 -81 4575 20 20 RESEARCH DALLAS
7369 SMITH CLERK 7902 17-12月-80 1200 20 20 RESEARCH DALLAS
5741 QIN_JIALI CLERK 7654 20-3月 -85 1900 600 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 03-12月-81 1350 30 30 SALES CHICAGO
7844 TURNER SALESMAN 7698 08-9月 -81 1900 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-5月 -81 4450 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 22-2月 -81 1650 500 30 30 SALES CHICAGO
7499 ALLEN SALESMAN 7698 20-2月 -81 2000 300 30 30 SALES CHICAGO
7654 MARTIN SALESMAN 7698 28-9月 -81 1650 1400 40 40 OPERATIONS BOSTON
已选择15行。
b):select *from emp e,dept d where e.deptno=d.deptno(+)
SQL> select *from emp e,dept d where e.deptno=d.deptno(+)
2 /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- ---------- -------------- ---------
7934 MILLER CLERK 7782 23-1月 -82 1700 10 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT 17-11月-81 7000 10 10 ACCOUNTING NEW YORK
7782 CLARK MANAGER 7839 09-6月 -81 4050 10 10 ACCOUNTING NEW YORK
7902 FORD ANALYST 7566 03-12月-81 3400 20 20 RESEARCH DALLAS
7876 ADAMS CLERK 7788 23-5月 -87 1500 20 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 19-4月 -87 3400 20 20 RESEARCH DALLAS
7566 JONES MANAGER 7839 02-4月 -81 4575 20 20 RESEARCH DALLAS
7369 SMITH CLERK 7902 17-12月-80 1200 20 20 RESEARCH DALLAS
5741 QIN_JIALI CLERK 7654 20-3月 -85 1900 600 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 03-12月-81 1350 30 30 SALES CHICAGO
7844 TURNER SALESMAN 7698 08-9月 -81 1900 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-5月 -81 4450 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 22-2月 -81 1650 500 30 30 SALES CHICAGO
7499 ALLEN SALESMAN 7698 20-2月 -81 2000 300 30 30 SALES CHICAGO
7654 MARTIN SALESMAN 7698 28-9月 -81 1650 1400 40 40 OPERATIONS BOSTON
已选择15行。
b) 右外连接
a) select *from emp e right outer join dept d on e.deptno=d.deptno;
SQL> select *from emp e right outer join dept d on e.deptno=d.deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- ---------- -------------- ----------
0 other BOSTON
7934 MILLER CLERK 7782 23-1月 -82 1700 10 10 ACCOUNTING NEW YORK
7782 CLARK MANAGER 7839 09-6月 -81 4050 10 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT 17-11月-81 7000 10 10 ACCOUNTING NEW YORK
7566 JONES MANAGER 7839 02-4月 -81 4575 20 20 RESEARCH DALLAS
5741 QIN_JIALI CLERK 7654 20-3月 -85 1900 600 20 20 RESEARCH DALLAS
7369 SMITH CLERK 7902 17-12月-80 1200 20 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 03-12月-81 3400 20 20 RESEARCH DALLAS
7876 ADAMS CLERK 7788 23-5月 -87 1500 20 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 19-4月 -87 3400 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 20-2月 -81 2000 300 30 30 SALES CHICAGO
7844 TURNER SALESMAN 7698 08-9月 -81 1900 30 30 SALES CHICAGO
7900 JAMES CLERK 7698 03-12月-81 1350 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 22-2月 -81 1650 500 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-5月 -81 4450 30 30 SALES CHICAGO
7654 MARTIN SALESMAN 7698 28-9月 -81 1650 1400 40 40 OPERATIONS BOSTON
已选择16行。
b):select *from emp e,dept d where e.deptno(+)=d.deptno;
SQL> select *from emp e,dept d where e.deptno(+)=d.deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- ---------- -------------- ------------
0 other BOSTON
7934 MILLER CLERK 7782 23-1月 -82 1700 10 10 ACCOUNTING NEW YORK
7782 CLARK MANAGER 7839 09-6月 -81 4050 10 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT 17-11月-81 7000 10 10 ACCOUNTING NEW YORK
7566 JONES MANAGER 7839 02-4月 -81 4575 20 20 RESEARCH DALLAS
5741 QIN_JIALI CLERK 7654 20-3月 -85 1900 600 20 20 RESEARCH DALLAS
7369 SMITH CLERK 7902 17-12月-80 1200 20 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 03-12月-81 3400 20 20 RESEARCH DALLAS
7876 ADAMS CLERK 7788 23-5月 -87 1500 20 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 19-4月 -87 3400 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 20-2月 -81 2000 300 30 30 SALES CHICAGO
7844 TURNER SALESMAN 7698 08-9月 -81 1900 30 30 SALES CHICAGO
7900 JAMES CLERK 7698 03-12月-81 1350 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 22-2月 -81 1650 500 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-5月 -81 4450 30 30 SALES CHICAGO
7654 MARTIN SALESMAN 7698 28-9月 -81 1650 1400 40 40 OPERATIONS BOSTON
已选择16行。
c)全连接=左连接+右连接-重复的
SQL> select *from emp e full outer join dept d on e.deptno=d.deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- ---------- -------------- ------------
5741 QIN_JIALI CLERK 7654 20-3月 -85 1900 600 20 20 RESEARCH DALLAS
7369 SMITH CLERK 7902 17-12月-80 1200 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 20-2月 -81 2000 300 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 22-2月 -81 1650 500 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 02-4月 -81 4575 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 28-9月 -81 1650 1400 40 40 OPERATIONS BOSTON
7698 BLAKE MANAGER 7839 01-5月 -81 4450 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 09-6月 -81 4050 10 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 19-4月 -87 3400 20 20 RESEARCH DALLAS
7839 KING PRESIDENT 17-11月-81 7000 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 08-9月 -81 1900 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 23-5月 -87 1500 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 03-12月-81 1350 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 03-12月-81 3400 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 23-1月 -82 1700 10 10 ACCOUNTING NEW YORK
0 other BOSTON
已选择16行。
d) 自连接
将一张表,通过别名视为不同的表;
缺点:比较耗费性能。
--将一张表,即看成员工编号,又看成领导编号。
--通过编号进行连接
SQL> select *from emp e,emp d where e.mgr=d.empno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO EMPNO ENAME JOB MGR HIREDATE
SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- ---------- ---------- --------- ---------- ------------
--------- ---------- ----------
7902 FORD ANALYST 7566 03-12月-81 3400 20 7566 JONES MANAGER 7839 02-4月 -81
4575 20
7788 SCOTT ANALYST 7566 19-4月 -87 3400 20 7566 JONES MANAGER 7839 02-4月 -81
4575 20
5741 QIN_JIALI CLERK 7654 20-3月 -85 1900 600 20 7654 MARTIN SALESMAN 7698 28-9月 -81
1650 1400 40
7900 JAMES CLERK 7698 03-12月-81 1350 30 7698 BLAKE MANAGER 7839 01-5月 -81
4450 30
7844 TURNER SALESMAN 7698 08-9月 -81 1900 30 7698 BLAKE MANAGER 7839 01-5月 -81
4450 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1650 1400 40 7698 BLAKE MANAGER 7839 01-5月 -81
4450 30
7521 WARD SALESMAN 7698 22-2月 -81 1650 500 30 7698 BLAKE MANAGER 7839 01-5月 -81
4450 30
7499 ALLEN SALESMAN 7698 20-2月 -81 2000 300 30 7698 BLAKE MANAGER 7839 01-5月 -81
4450 30
7934 MILLER CLERK 7782 23-1月 -82 1700 10 7782 CLARK MANAGER 7839 09-6月 -81
4050 10
7876 ADAMS CLERK 7788 23-5月 -87 1500 20 7788 SCOTT ANALYST 7566 19-4月 -87
3400 20
7782 CLARK MANAGER 7839 09-6月 -81 4050 10 7839 KING PRESIDENT 17-11月-81
7000 10
7698 BLAKE MANAGER 7839 01-5月 -81 4450 30 7839 KING PRESIDENT 17-11月-81
7000 10
7566 JONES MANAGER 7839 02-4月 -81 4575 20 7839 KING PRESIDENT 17-11月-81
7000 10
7369 SMITH CLERK 7902 17-12月-80 1200 20 7902 FORD ANALYST 7566 03-12月-81
3400 20
已选择14行。