oracle多表查询题,Oracle专题7之多表查询

用ON子句建立相等连接。例如：SELECT e.ename, e.sal, d.dname from dept d join emp e on e.deptno = d.deptno;

SQL> SELECT e.ename, e.sal, d.dname from dept d join emp e on e.deptno = d.deptno;

ENAME SAL DNAME

MILLER 1300 ACCOUNTING

CLARK 2450 ACCOUNTING

KING 5000 ACCOUNTING

JONES 2975 RESEARCH

EASON 3000 RESEARCH

G_EASON 800 RESEARCH

FORD 3000 RESEARCH

ADAMS 1100 RESEARCH

SCOTT 3000 RESEARCH

ALLEN 1600 SALES

TURNER 1500 SALES

JAMES 950 SALES

WARD 1250 SALES

BLAKE 2850 SALES

MARTIN 1250 SALES

已选择15行。

d、左(外)连接

左连接用于返回满足连接条件的数据，以及不满足连接条件的左边表的其他数据。(即：左表的全部记录，右表满足条件的记录)

在SQL：1999标准中，左连接是通过LEFT[OUTER] JOIN选项来实现的。如：select e.ename, e.sal, d.dname from dept d left join emp e on e.deptno = d.deptno;

SQL> select e.ename, e.sal, d.dname from dept d left join emp e on e.deptno = d.deptno;

ENAME SAL DNAME

MILLER 1300 ACCOUNTING

CLARK 2450 ACCOUNTING

KING 5000 ACCOUNTING

JONES 2975 RESEARCH

EASON 3000 RESEARCH

G_EASON 800 RESEARCH

FORD 3000 RESEARCH

ADAMS 1100 RESEARCH

SCOTT 3000 RESEARCH

ALLEN 1600 SALES

TURNER 1500 SALES

JAMES 950 SALES

WARD 1250 SALES

BLAKE 2850 SALES

MARTIN 1250 SALES

OPERATIONS

已选择16行。

e、右(外)连接

右连接用于返回满足连接条件的左边表的数据，以及不满足连接条件的右边表的其他数据。(即：右表中的全部记录，左表满足条件的记录。)示例：select e.ename, e.sal, d.dname from dept d right join emp e on e.deptno = d.deptno;

SQL> select e.ename, e.sal, d.dname from dept d right join emp e on e.deptno = d.deptno;

ENAME SAL DNAME

MILLER 1300 ACCOUNTING

KING 5000 ACCOUNTING

CLARK 2450 ACCOUNTING

FORD 3000 RESEARCH

ADAMS 1100 RESEARCH

SCOTT 3000 RESEARCH

JONES 2975 RESEARCH

G_EASON 800 RESEARCH

EASON 3000 RESEARCH

JAMES 950 SALES

TURNER 1500 SALES

BLAKE 2850 SALES

MARTIN 1250 SALES

WARD 1250 SALES

ALLEN 1600 SALES

已选择15行。

f、完全(外)连接

完全连接用于返回满足连接条件的数据，以及不满足链接条件的左表和右表中的其他数据。(即：左表和右表的全部记录。)

在SQL：1999标准中，完全连接是通过制定FULL [OUTER] JOIN 选项来实现的。示例：select e.ename, e.sal, d.dname from dept d full join emp e on e.deptno = d.deptno;

SQL> select e.ename, e.sal, d.dname from dept d full join emp e on e.deptno = d.deptno;

ENAME SAL DNAME

EASON 3000 RESEARCH

G_EASON 800 RESEARCH

ALLEN 1600 SALES

WARD 1250 SALES

JONES 2975 RESEARCH

MARTIN 1250 SALES

BLAKE 2850 SALES

CLARK 2450 ACCOUNTING

SCOTT 3000 RESEARCH

KING 5000 ACCOUNTING

TURNER 1500 SALES

ADAMS 1100 RESEARCH

JAMES 950 SALES

FORD 3000 RESEARCH

MILLER 1300 ACCOUNTING

OPERATIONS

已选择16行。

6、set运算符

set运算符，即集合运算符专门用于合并多条SELECT语句的结果，包括4种：UNION/UNION ALL 并集、INTERSECT 交集、MINUS 差集。

例如有集合A和集合B，那么集合A有1、2、3，集合B有3、4。

A UNION B有：1、2、3、4；A UNION ALL B有：1、2、3、3 、4；

A INTERSECT B有：3；A MINUS B有：1、2；B MINUS A有：4；

a、UNION

UNION操作符用于取得两个结果集的并集，当使用该操作符时，会自动去掉结果集中的重复行，并且会以第一列的结果进行升序排序。

举例：合并显示emp02表和emp02表中所有雇员的部门编号、员工号、员工姓名。

SQL> create table emp01 as select * from emp where deptno in(10,20);

表已创建。

SQL> create table emp02 as select * from emp where deptno in(20,30);

表已创建。

SQL> select deptno, empno, ename from emp01 union select deptno, empno, ename from emp02;

DEPTNO EMPNO ENAME

10 7782 CLARK

10 7839 KING

10 7934 MILLER

20 7369 G_EASON

20 7566 JONES

20 7788 SCOTT

20 7876 ADAMS

20 7902 FORD

20 7951 EASON

30 7499 ALLEN

30 7521 WARD

30 7654 MARTIN

30 7698 BLAKE

30 7844 TURNER

30 7900 JAMES

已选择15行。

b、UNION ALL

union all 操作符用于取得两个结果集的并集，单与union操作符不同，该操作符不会取消重复行，并且不会对结果集数据进行排序。

SQL> select deptno, empno, ename from emp01 union all select deptno, empno, ename from emp02;

DEPTNO EMPNO ENAME

20 7951 EASON

20 7369 G_EASON

20 7566 JONES

......

30 7844 TURNER

20 7876 ADAMS

30 7900 JAMES

20 7902 FORD

已选择21行。

c、INTERSECT

intersect操作符用于取得两个结果集的交集，当使用该操作符时，只会显示同时存在于两个结果集中的数据，并且会以第一列的结果进行升序排序。

SQL> select deptno, empno, ename from emp01 intersect select deptno, empno, ename from emp02;

DEPTNO EMPNO ENAME

20 7369 G_EASON

20 7566 JONES

20 7788 SCOTT

20 7876 ADAMS

20 7902 FORD

20 7951 EASON

已选择6行。

d、MINUS

minus操作符用于取得两个结果集中的差集，当使用该操作符时，只会显示在第一个结果集中存在，在第二个结果集中不存在的数据，并且会以第一列的结果集进行升序排序。

SQL> select deptno, empno, ename from emp01 minus select deptno, empno, ename from emp02;

DEPTNO EMPNO ENAME

10 7782 CLARK

10 7839 KING

10 7934 MILLER

e、控制结果排序

当使用集合操作符UNION、INTERSECT和MINUS时，默认情况下会自动基于第一列进行升序排序；而当使用集合操作符UNION ALL时，不会进行排序，为了控制结果的排序顺序，可以使用ORDER BY子句。(如果两个表查询的结果中列名相同，则可以使用列名名称；如果两个表中的列名不同，则必须使用列位置，1表示基于第1列，2表示基于第2列)

示例：select deptno, empno, ename from emp01 union all select deptno, empno, ename from emp02 order by 2;

SQL> select deptno, empno, ename from emp01 union all select deptno, empno, ename from emp02 order by empn

DEPTNO EMPNO ENAME

20 7369 G_EASON

20 7369 G_EASON

30 7499 ALLEN

30 7521 WARD

20 7566 JONES

......

20 7902 FORD

10 7934 MILLER

20 7951 EASON

20 7951 EASON

已选择21行。

SQL> select deptno, empno, ename from emp01 union all select deptno, empno, ename from emp02 order by

DEPTNO EMPNO ENAME

20 7369 G_EASON

20 7369 G_EASON

30 7499 ALLEN

30 7521 WARD

......

20 7902 FORD

10 7934 MILLER

20 7951 EASON

20 7951 EASON

已选择21行。

f、使用set操作符的注意事项

在SELECT列表中的列名和表达式在数量和数据类型上要相对应。