Oracle的高级查询
简单连接查询(join)
在emp表与dept表之间存在着多对一的关联关系（现实中还有其他的关联）,往往我们希望查询出更多信息,这时候我们就要用到连接查询。
查询员工及部门的详细信息但是会产生一个笛卡尔积的效果
SQL> select * from emp,dept;
怎么避免笛卡尔积呢?加入where查询条件 引用关系的比较
SQL> select * from emp ,dept where emp.deptno = dept.deptno;
别名查询 为表起别名 采用别名查询
SQL> select * from emp e,dept d where e.deptno=d.deptno;
//注意 以下写法是有问题的，出现的异常是：ORA-00918: 未明确定义列
SQL> select e.empno,e.ename,deptno,d.dname from emp e,dept d where e.deptno=d.deptno;
备注说明：deptno在两个表中都存在，所以一定要使用前缀区分。
SQL> select e.empno,e.ename,e.deptno,d.dname from emp e,dept d where e.deptno=d.deptno;
综上所述 创建连接查询时应遵循如下规则：
from子句应当包括所有的表名
where子句应定义连接条件 两个表1一个等值条件 三个表 2个等值条件…依次类推。
备注：连接 n个表,至少需要 n-1个连接条件。 例如：连接三个表，至少需要两个连接条件。
当列名为多个表共有时,列名必须被限制。
使用join连接查询
语法：
From join_table join_type join_table on join_condition
备注： Join_table连接的表名 join_type连接的类型
高级连接查询
根据连接的join_type可分为内连接、外连接和交叉连接
内连接(inner join)
内连接根据比较方式的不同：把内连接分为了等值连接、不等值连接和自然连接
等值连接：在连接条件中使用等号“=”运算符比较被连接列的列值，其查询的是两个表中的所有列，包括重复列。
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
----- ---------- --------- ----- ----------- --------- --------- ------ ------ -------------- -------------
7369 SMITH CLERK 7902 1980-12-17 800.00 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 1981-4-2 2975.00 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 1982-12-9 3000.00 20 20 RESEARCH DALLAS
7839 KING PRESIDENT 1981-11-17 5000.00 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 1983-1-12 1100.00 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 1981-12-3 950.00 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 1981-12-3 3000.00 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 1982-1-23 1300.00 10 10 ACCOUNTING NEW YORK
14 rows selected
不等值连接：在连接条件使用除等于运算符以外的其他比较运算符比较被连接的列的列值，这些运算符包括>、>=、<、!>、!<和<>，不消除重复列。
select * from emp e inner join dept d on e.deptno>d.deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
----- ---------- --------- ----- ----------- --------- --------- ------ ------ -------------- -------------
7369 SMITH CLERK 7902 1980-12-17 800.00 20 10 ACCOUNTING NEW YORK
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 10 ACCOUNTING NEW YORK
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 20 RESEARCH DALLAS
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 10 ACCOUNTING NEW YORK
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 20 RESEARCH DALLAS
7566 JONES MANAGER 7839 1981-4-2 2975.00 20 10 ACCOUNTING NEW YORK
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 10 ACCOUNTING NEW YORK
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 20 RESEARCH DALLAS
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 10 ACCOUNTING NEW YORK
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 1982-12-9 3000.00 20 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 20 RESEARCH DALLAS
7876 ADAMS CLERK 7788 1983-1-12 1100.00 20 10 ACCOUNTING NEW YORK
7900 JAMES CLERK 7698 1981-12-3 950.00 30 10 ACCOUNTING NEW YORK
7900 JAMES CLERK 7698 1981-12-3 950.00 30 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 1981-12-3 3000.00 20 10 ACCOUNTING NEW YORK
17 rows selected
自然连接：如果两个表中有相同的字段的话，用自然连接可省去where条件句，并删除连接表中的重复列
SQL> select * from emp natural join dept;
DEPTNO EMPNO ENAME JOB MGR HIREDATE SAL COMM DNAME LOC
------ ----- ---------- --------- ----- ----------- --------- --------- -------------- -------------
20 7369 SMITH CLERK 7902 1980-12-17 800.00 RESEARCH DALLAS
30 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 SALES CHICAGO
30 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 SALES CHICAGO
20 7566 JONES MANAGER 7839 1981-4-2 2975.00 RESEARCH DALLAS
30 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 SALES CHICAGO
30 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 SALES CHICAGO
10 7782 CLARK MANAGER 7839 1981-6-9 2450.00 ACCOUNTING NEW YORK
20 7788 SCOTT ANALYST 7566 1982-12-9 3000.00 RESEARCH DALLAS
10 7839 KING PRESIDENT 1981-11-17 5000.00 ACCOUNTING NEW YORK
30 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 SALES CHICAGO
20 7876 ADAMS CLERK 7788 1983-1-12 1100.00 RESEARCH DALLAS
30 7900 JAMES CLERK 7698 1981-12-3 950.00 SALES CHICAGO
20 7902 FORD ANALYST 7566 1981-12-3 3000.00 RESEARCH DALLAS
10 7934 MILLER CLERK 7782 1982-1-23 1300.00 ACCOUNTING NEW YORK
14 rows selected
备注：Distinct是去掉重复的行,而自然连接是去掉重复的列。
自连接：自连接是自己表与自己表连接，用别名区分开，属于内连接
如：查询员工的基本信息，附加其上级的姓名
SQL> select e1.empno,e1.ename,e1.job,e2.ename as "MGR",e1.hiredate,e1.sal,e1.comm,e1.deptno from emp e1,emp e2 where e1.mgr=e2.empno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ---------- ----------- --------- --------- ------
7369 SMITH CLERK FORD 1980-12-17 800.00 20
7499 ALLEN SALESMAN BLAKE 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN BLAKE 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER KING 1981-4-2 2975.00 20
7654 MARTIN SALESMAN BLAKE 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER KING 1981-5-1 2850.00 30
7782 CLARK MANAGER KING 1981-6-9 2450.00 10
7788 SCOTT ANALYST JONES 1982-12-9 3000.00 20
7844 TURNER SALESMAN BLAKE 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK SCOTT 1983-1-12 1100.00 20
7900 JAMES CLERK BLAKE 1981-12-3 950.00 30
7902 FORD ANALYST JONES 1981-12-3 3000.00 20
7934 MILLER CLERK CLARK 1982-1-23 1300.00 10
13 rows selected
外连接(outer join)
左外连接（LEFT OUTER JOIN）：查询显示左边表中的所有信息，右表中的信息如果符合查询条件的显示出来，如果不符合条件，则显示NULL
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
----- ---------- --------- ----- ----------- --------- --------- ------ ------ -------------- -------------
7369 SMITH CLERK 7902 1980-12-17 800.00 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 1981-4-2 2975.00 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 1982-12-9 3000.00 20 20 RESEARCH DALLAS
7839 KING PRESIDENT 1981-11-17 5000.00 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 1983-1-12 1100.00 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 1981-12-3 950.00 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 1981-12-3 3000.00 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 1982-1-23 1300.00 10 10 ACCOUNTING NEW YORK
14 rows selected
右外连接(RIGHT OUTER JOIN)：查询显示右边表中的所有信息，左边中的信息如果符合查询条件的显示出来，如果不符合条件，则显示NULL
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
----- ---------- --------- ----- ----------- --------- --------- ------ ------ -------------- -------------
7369 SMITH CLERK 7902 1980-12-17 800.00 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 1981-4-2 2975.00 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 1982-12-9 3000.00 20 20 RESEARCH DALLAS
7839 KING PRESIDENT 1981-11-17 5000.00 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 1983-1-12 1100.00 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 1981-12-3 950.00 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 1981-12-3 3000.00 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 1982-1-23 1300.00 10 10 ACCOUNTING NEW YORK
40 OPERATIONS BOSTON
15 rows selected
完全连接(FULL OUTER JOIN)：完全连接就是把左外连接和右外连接综合到一起
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
---------- ---------- --------- ---------- ----------- ---------- ---------- ---------- ------ -------------- -------------
7369 SMITH CLERK 7902 1980-12-17 800 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 1981-2-20 1600 300 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 1981-2-22 1250 500 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 1981-4-2 2975 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 1981-9-28 1250 1400 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 1981-5-1 2850 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 1981-6-9 2450 10 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 1982-12-9 3000 20 20 RESEARCH DALLAS
7839 KING PRESIDENT 1981-11-17 5000 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 1981-9-8 1500 0 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 1983-1-12 1100 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 1981-12-3 950 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 1981-12-3 3000 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 1982-1-23 1300 10 10 ACCOUNTING NEW YORK
40 OPERATIONS BOSTON
15 rows selected
交叉连接
相当于查询笛卡尔积，一般用不到，在这里不做介绍
简单连接查询(join)
在emp表与dept表之间存在着多对一的关联关系（现实中还有其他的关联）,往往我们希望查询出更多信息,这时候我们就要用到连接查询。
查询员工及部门的详细信息但是会产生一个笛卡尔积的效果
SQL> select * from emp,dept;
怎么避免笛卡尔积呢?加入where查询条件 引用关系的比较
SQL> select * from emp ,dept where emp.deptno = dept.deptno;
别名查询 为表起别名 采用别名查询
SQL> select * from emp e,dept d where e.deptno=d.deptno;
//注意 以下写法是有问题的，出现的异常是：ORA-00918: 未明确定义列
SQL> select e.empno,e.ename,deptno,d.dname from emp e,dept d where e.deptno=d.deptno;
备注说明：deptno在两个表中都存在，所以一定要使用前缀区分。
SQL> select e.empno,e.ename,e.deptno,d.dname from emp e,dept d where e.deptno=d.deptno;
综上所述 创建连接查询时应遵循如下规则：
from子句应当包括所有的表名
where子句应定义连接条件 两个表1一个等值条件 三个表 2个等值条件…依次类推。
备注：连接 n个表,至少需要 n-1个连接条件。 例如：连接三个表，至少需要两个连接条件。
当列名为多个表共有时,列名必须被限制。
使用join连接查询
语法：
From join_table join_type join_table on join_condition
备注： Join_table连接的表名 join_type连接的类型
高级连接查询
根据连接的join_type可分为内连接、外连接和交叉连接
内连接(inner join)
内连接根据比较方式的不同：把内连接分为了等值连接、不等值连接和自然连接
等值连接：在连接条件中使用等号“=”运算符比较被连接列的列值，其查询的是两个表中的所有列，包括重复列。
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
----- ---------- --------- ----- ----------- --------- --------- ------ ------ -------------- -------------
7369 SMITH CLERK 7902 1980-12-17 800.00 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 1981-4-2 2975.00 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 1982-12-9 3000.00 20 20 RESEARCH DALLAS
7839 KING PRESIDENT 1981-11-17 5000.00 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 1983-1-12 1100.00 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 1981-12-3 950.00 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 1981-12-3 3000.00 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 1982-1-23 1300.00 10 10 ACCOUNTING NEW YORK
14 rows selected
不等值连接：在连接条件使用除等于运算符以外的其他比较运算符比较被连接的列的列值，这些运算符包括>、>=、<、!>、!<和<>，不消除重复列。
select * from emp e inner join dept d on e.deptno>d.deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
----- ---------- --------- ----- ----------- --------- --------- ------ ------ -------------- -------------
7369 SMITH CLERK 7902 1980-12-17 800.00 20 10 ACCOUNTING NEW YORK
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 10 ACCOUNTING NEW YORK
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 20 RESEARCH DALLAS
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 10 ACCOUNTING NEW YORK
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 20 RESEARCH DALLAS
7566 JONES MANAGER 7839 1981-4-2 2975.00 20 10 ACCOUNTING NEW YORK
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 10 ACCOUNTING NEW YORK
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 20 RESEARCH DALLAS
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 10 ACCOUNTING NEW YORK
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 1982-12-9 3000.00 20 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 20 RESEARCH DALLAS
7876 ADAMS CLERK 7788 1983-1-12 1100.00 20 10 ACCOUNTING NEW YORK
7900 JAMES CLERK 7698 1981-12-3 950.00 30 10 ACCOUNTING NEW YORK
7900 JAMES CLERK 7698 1981-12-3 950.00 30 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 1981-12-3 3000.00 20 10 ACCOUNTING NEW YORK
17 rows selected
自然连接：如果两个表中有相同的字段的话，用自然连接可省去where条件句，并删除连接表中的重复列
SQL> select * from emp natural join dept;
DEPTNO EMPNO ENAME JOB MGR HIREDATE SAL COMM DNAME LOC
------ ----- ---------- --------- ----- ----------- --------- --------- -------------- -------------
20 7369 SMITH CLERK 7902 1980-12-17 800.00 RESEARCH DALLAS
30 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 SALES CHICAGO
30 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 SALES CHICAGO
20 7566 JONES MANAGER 7839 1981-4-2 2975.00 RESEARCH DALLAS
30 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 SALES CHICAGO
30 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 SALES CHICAGO
10 7782 CLARK MANAGER 7839 1981-6-9 2450.00 ACCOUNTING NEW YORK
20 7788 SCOTT ANALYST 7566 1982-12-9 3000.00 RESEARCH DALLAS
10 7839 KING PRESIDENT 1981-11-17 5000.00 ACCOUNTING NEW YORK
30 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 SALES CHICAGO
20 7876 ADAMS CLERK 7788 1983-1-12 1100.00 RESEARCH DALLAS
30 7900 JAMES CLERK 7698 1981-12-3 950.00 SALES CHICAGO
20 7902 FORD ANALYST 7566 1981-12-3 3000.00 RESEARCH DALLAS
10 7934 MILLER CLERK 7782 1982-1-23 1300.00 ACCOUNTING NEW YORK
14 rows selected
备注：Distinct是去掉重复的行,而自然连接是去掉重复的列。
自连接：自连接是自己表与自己表连接，用别名区分开，属于内连接
如：查询员工的基本信息，附加其上级的姓名
SQL> select e1.empno,e1.ename,e1.job,e2.ename as "MGR",e1.hiredate,e1.sal,e1.comm,e1.deptno from emp e1,emp e2 where e1.mgr=e2.empno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ---------- ----------- --------- --------- ------
7369 SMITH CLERK FORD 1980-12-17 800.00 20
7499 ALLEN SALESMAN BLAKE 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN BLAKE 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER KING 1981-4-2 2975.00 20
7654 MARTIN SALESMAN BLAKE 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER KING 1981-5-1 2850.00 30
7782 CLARK MANAGER KING 1981-6-9 2450.00 10
7788 SCOTT ANALYST JONES 1982-12-9 3000.00 20
7844 TURNER SALESMAN BLAKE 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK SCOTT 1983-1-12 1100.00 20
7900 JAMES CLERK BLAKE 1981-12-3 950.00 30
7902 FORD ANALYST JONES 1981-12-3 3000.00 20
7934 MILLER CLERK CLARK 1982-1-23 1300.00 10
13 rows selected
外连接(outer join)
左外连接（LEFT OUTER JOIN）：查询显示左边表中的所有信息，右表中的信息如果符合查询条件的显示出来，如果不符合条件，则显示NULL
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
----- ---------- --------- ----- ----------- --------- --------- ------ ------ -------------- -------------
7369 SMITH CLERK 7902 1980-12-17 800.00 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 1981-4-2 2975.00 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 1982-12-9 3000.00 20 20 RESEARCH DALLAS
7839 KING PRESIDENT 1981-11-17 5000.00 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 1983-1-12 1100.00 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 1981-12-3 950.00 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 1981-12-3 3000.00 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 1982-1-23 1300.00 10 10 ACCOUNTING NEW YORK
14 rows selected
右外连接(RIGHT OUTER JOIN)：查询显示右边表中的所有信息，左边中的信息如果符合查询条件的显示出来，如果不符合条件，则显示NULL
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
----- ---------- --------- ----- ----------- --------- --------- ------ ------ -------------- -------------
7369 SMITH CLERK 7902 1980-12-17 800.00 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 1981-4-2 2975.00 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 1982-12-9 3000.00 20 20 RESEARCH DALLAS
7839 KING PRESIDENT 1981-11-17 5000.00 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 1983-1-12 1100.00 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 1981-12-3 950.00 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 1981-12-3 3000.00 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 1982-1-23 1300.00 10 10 ACCOUNTING NEW YORK
40 OPERATIONS BOSTON
15 rows selected
完全连接(FULL OUTER JOIN)：完全连接就是把左外连接和右外连接综合到一起
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
---------- ---------- --------- ---------- ----------- ---------- ---------- ---------- ------ -------------- -------------
7369 SMITH CLERK 7902 1980-12-17 800 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 1981-2-20 1600 300 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 1981-2-22 1250 500 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 1981-4-2 2975 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 1981-9-28 1250 1400 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 1981-5-1 2850 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 1981-6-9 2450 10 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 1982-12-9 3000 20 20 RESEARCH DALLAS
7839 KING PRESIDENT 1981-11-17 5000 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 1981-9-8 1500 0 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 1983-1-12 1100 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 1981-12-3 950 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 1981-12-3 3000 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 1982-1-23 1300 10 10 ACCOUNTING NEW YORK
40 OPERATIONS BOSTON
15 rows selected
交叉连接
相当于查询笛卡尔积，一般用不到，在这里不做介绍
4555
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
