要将来自多个表的数据组织到一起,就像将一个结果集叠加到另外一个上面一样。 这些表不必有相同的关键字,但是他们对应列的数据类型必须相同。
使用union all 把多个表中的行组合到一起。
select ename, deptno
from emp
where deptno = 10
union all
select '-----', deptno
from dept
union all
select dname, deptno from dept;
SQL> select distinct deptno
2 from (select deptno from dept
3 union all
4 select deptno from dept);
DEPTNO
------
30
20
40
10
SQL>
SQL> select deptno
2 from dept
3 union
4 select deptno from dept
5 ;
DEPTNO
------
10
20
30
40
SQL>
多表有一些相同的列,或者有些列的值相同,需要通过关联这些列得到结果。
select a.ename ,b.dname from emp a ,dept b where a.deptno = b.deptno and a.deptno = 10 ;
select a.ename ,b.dname from emp a inner join dept b on a.deptno = b.deptno where a.deptno = 10 ;
第二种解决方式是利用显示的JOIN子句(inner 关键字可省略),如果希望将联接逻辑关系放在from子句中,而不是在where 子句中,可以使用JOIN子句, 这两种方式都符合ANSI标准。
先创建一个表EMP2
create table emp2 as
select ename, job, sal, comm
from emp where job = 'CLERK';
要求返回与emp2(ename, job, sal)中数据相匹配的emp(ename, job, sal,deptno)信息
有in , exists 和 inner join 三种写法,为了加强理解,我们来看下三种写法及其对应的执行计划。
ORACLE VERSION : Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
SQL> explain plan for
2 select ename, job, sal, deptno
3 from emp
4 where (ename, job, sal) in (select ename, job, sal from emp2);
Explained
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4039873364
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 260 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 4 | 260 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 546 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP2 | 4 | 104 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ENAME"="ENAME" AND "JOB"="JOB" AND "SAL"="SAL")
Note
-----
- dynamic sampling used for this statement (level=2)
19 rows selected
SQL>
SQL> explain plan for
2 select ename, job, sal, deptno
3 from emp a where exists (select * from emp2 b
4 where b.ename= a.ename
5 and b.job = a.job
6 and b.sal = a.sal) ;
Explained
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4039873364
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 260 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 4 | 260 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 546 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP2 | 4 | 104 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"."ENAME"="A"."ENAME" AND "B"."JOB"="A"."JOB" AND
"B"."SAL"="A"."SAL")
Note
-----
- dynamic sampling used for this statement (level=2)
20 rows selected
SQL>
因为子查询的join列(emp2.ename ,emp2.job ,emp2.sal)没有重复行,说这个查询可以直接改写为inner join
SQL> explain plan for
2 select a.ename, a.job, a.sal, a.deptno
3 from emp a join emp2 b on
4 (a.ename = b.ename
5 and a.job= b.job
6 and a.sal =b.sal);
Explained
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 166525280
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 260 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 4 | 260 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP2 | 4 | 104 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 546 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ENAME"="B"."ENAME" AND "A"."JOB"="B"."JOB" AND
"A"."SAL"="B"."SAL")
Note
-----
- dynamic sampling used for this statement (level=2)
20 rows selected
SQL>
或许与大家想象的不一样,以上三个PLAN中join写法利用了hash join(哈希连接),其他两种运用的是 hash join semi(哈希半连接) 。 说明在这个语句中 in 和 exists的效率是一样的。
所以,在不知道哪种写法高效时应该查看Plan,而不是去记固定的结论。
有人对这几种连接方式,特别是left join 和 right join 分不清楚,下面通过案例来分析一下。
SQL>CREATE TABLE L AS
SELECT 'left_1' AS str,'1' AS v FROM dual UNION ALL
SELECT 'left_2','2' AS v FROM dual UNION ALL
SELECT 'left_3','3' AS v FROM dual UNION ALL
SELECT 'left_4','4' AS v FROM dual;
/*右表*/
SQL>CREATE TABLE R AS
SELECT 'right_3' AS str,'3' AS v,1 AS status FROM dual UNION ALL
SELECT 'right_4' AS str,'4' AS v,0 AS status FROM dual UNION ALL
SELECT 'right_5' AS str,'5' AS v,0 AS status FROM dual UNION ALL
SELECT 'right_6' AS str,'6' AS v,0 AS status FROM dual;
SQL> select * from l ;
STR V
------ -
left_1 1
left_2 2
left_3 3
left_4 4
SQL> select * from r;
STR V STATUS
------- - ----------
right_3 3 1
right_4 4 0
right_5 5 0
right_6 6 0
SQL>
该方式返回两表相匹配的数据。
inner join写法:
select l.str, r.str from l inner join r on l.v = r.v order by 1, 2;
where写法:
select l.str, r.str from l, r where l.v = r.v order by 1, 2;
输出:
STR STR
------ -------
left_3 right_3
left_4 right_4
该方式以左表为主表,左表返回所有的数据,右表只返回与左表匹配的数据。
SQL> select l.str, r.str from l left join r on l.v = r.v order by 1, 2;
STR STR
------ -------
left_1
left_2
left_3 right_3
left_4 right_4
SQL>
(+)写法:
SQL> select l.str, r.str from l, r where l.v = r.v(+) order by 1, 2;
STR STR
------ -------
left_1
left_2
left_3 right_3
left_4 right_4
SQL>
该方式以右表为主表,右表返回所有的数据,左表只返回与左表匹配的数据。
select l.str, r.str from l right join r on l.v = r.v order by 1, 2;
(+)写法:
select l.str, r.str from l, r where l.v(+) = r.v order by 1, 2;
SQL> select l.str, r.str from l right join r on l.v = r.v order by 1, 2;
STR STR
------ -------
left_3 right_3
left_4 right_4
right_5
right_6
SQL> select l.str, r.str from l, r where l.v(+) = r.v order by 1, 2;
STR STR
------ -------
left_3 right_3
left_4 right_4
right_5
right_6
SQL>
该方式的左表和右表都返回所有的数据,但只有相匹配的数据显示在同一行,非匹配的行只显示一个表的数据。
SQL> select l.str, r.str from l full join r on l.v = r.v order by 1, 2;
STR STR
------ -------
left_1
left_2
left_3 right_3
left_4 right_4
right_5
right_6
6 rows selected
SQL>
注意 FULL JOIN没有(+)写法
表emp中有个字段 mgr,是主管的编码(对应于emp.empno)
如何根据这个信息返回主管的姓名呢?
自关联,也就是两次查询表emp,分别取不同的别名,这样就可以当做是两个 表,后面的任务就是将这两个表 join连接起来即可。
为了便于理解,这里我们使用汉字作为别名,并把相关列一起返回。
select 员工.empno as 员工编码 ,
员工.ename as 员工name,
员工.job as 工作,
员工.mgr as 员工表主管编码,
主管.empno as 主管表主管编码,
主管.ename as 主管姓名
from emp 员工
left join emp 主管 on (员工.mgr = 主管.empno)
order by 1;
上述的操作,可以理解为我们是在两个不同的数据集中取数据。
create or replace view 员工 as select * from emp ;
create or replace view 主管 as select * from emp ;
select 员工.empno as 员工编码 ,
员工.ename as 员工name,
员工.job as 工作,
员工.mgr as 员工表主管编码,
主管.empno as 主管表主管编码,
主管.ename as 主管姓名
from 员工
left join 主管 on (员工.mgr = 主管.empno)
order by 1;
有些单位的部门如40中一个员工也没有,只是设置了一个部门名字,如何通过关联查询把这些信息查询出来呢?
数据库版本 11.2.0.4.0
alter table dept add constraints pk_dept primary key(deptno)
执行以下SQL并查询执行计划
explain plan for
select * from dept
where deptno not in (select deptno from emp where deptno is not null);
explain plan for
select * from dept
where not exists (select null from emp where emp.deptno = dept.deptno)
Left join 取出的是左表中所有的数据,其中右表不匹配的就表示左表not in 右表
explain plan for
select dept.* from dept
left join emp on emp.deptno = dept.deptno
where emp.deptno is null ;
select * from table(dbms_xplan.display());
三个PLAN应用的都是 MERGE JOINANTI,说明这三种方法的效率是一样的。
若果想改写,那么就要比对前后的PLAN,根据PLAN来判断并测试哪种方法的效率高,而不是凭借某些结论来碰运气。
对于左连语句,见下面的数据
SQL> select l.str, r.str ,r.status from l
left join r
on l.v = r.v
order by 1, 2;
STR STR STATUS
------ ------- -------
left_1
left_2
left_3 right_3 1
left_4 right_4 0
SQL>
对于L表,4条数据全部返回了,而对于R表,我们如果 只需要显示 status=1的部分,该如何写SQL呢?
常见的错误写法, 会有人直接在上面的语句中加入条件 status=1
select l.str, r.str ,r.status from l
left join r
on l.v = r.v
where r.status=1
order by 1, 2;
我们来看下返回结果:
STR STR STATUS
------ ------- -------
left_3 right_3 1
很明显这不是我们想要的数据集。这是很多人写查询或者改查询时常遇到的一种错误, 问题在于所加条件的位置及写法。
那该如何做呢?
left join写法
select l.str, r.str, r.status
from l
left join r
on (l.v = r.v and r.status = 1)
order by 1, 2;
(+)写法
select l.str, r.str, r.status
from l, r
where l.v = r.v(+)
and r.status(+) = 1
order by 1, 2;
查找视图V 和 emp表中不同的数据
我们先创建一个视图
create or replace view v as
select * from emp where deptno !=10
union all
select * from emp where ename='SCOTT';
我们可以知道 视图V中,SCOTT有两条记录, EMP中有一条
SQL> select * from v where ename='SCOTT';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7788 SCOTT ANALYST 7566 1987-04-19 3000.00 20
7788 SCOTT ANALYST 7566 1987-04-19 3000.00 20
SQL> select * from emp where ename='SCOTT';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7788 SCOTT ANALYST 7566 1987-04-19 3000.00 20
SQL>
比较两个数据集的不同时,通常类似下面的FULL JOIN 语句。
SQL> select v.EMPNO, v.ENAME, emp.empno, emp.ename
2 from v
3 full join emp
4 on v.EMPNO = emp.empno
5 where (v.EMPNO is null or emp.empno is null);
EMPNO ENAME EMPNO ENAME
----- ---------- ----- ----------
7782 CLARK
7839 KING
7934 MILLER
SQL>
但是这种语句查不到 SCOTT的区别 。
增加一列显示相同数据的条数,再进行比较。
select v_new.empno,
v_new.ENAME,
v_new.cnt,
emp_new.empno,
emp_new.ename,
emp_new.cnt
from (select v.empno, v.ENAME, count(1) as cnt
from v
group by v.empno, v.ENAME) v_new
full join (select a.empno, a.ENAME, count(1) as cnt
from emp a
group by a.empno, a.ENAME) emp_new
on (v_new.EMPNO = emp_new.empno and v_new.cnt = emp_new.cnt)
where (v_new.EMPNO is null or emp_new.empno is null);
结果集:
EMPNO ENAME CNT EMPNO ENAME CNT
----- ---------- ---------- ----- ---------- ----------
7788 SCOTT 1
7934 MILLER 1
7782 CLARK 1
7839 KING 1
7788 SCOTT 2
SQL>
同时返回多个表中丢失的数据。 要从DEPT中返回EMP不存在的行(所有没有员工的部门)需要做外连接。
首先我们在EMP中增加一行deptno为空的数据,如下:
insert into emp
(empno, ename, job, mgr, hiredate, sal, comm, deptno)
select 6666, 'XGJ', 'JEDI', null, hiredate, sal, comm, null
from emp
where ename = 'KING';
commit ;
此时,我们来看下 我们要查询的表中数据:
这时,如果我们使用下面的语句关联查询 ,就会发现少了emp=6666和 deptno=40的数据
select e.empno, e.ename, b.deptno, b.dname
from emp e
join dept b
on e.deptno = b.deptno;
select e.empno, e.ename, b.deptno, b.dname
from emp e
full join dept b
on e.deptno = b.deptno;
select e.empno, e.ename, b.deptno, b.dname
from emp e
left join dept b
on e.deptno = b.deptno
union all
select e.empno, e.ename, b.deptno, b.dname
from emp e
right join dept b
on e.deptno = b.deptno
where e.empno is null ;
在这里不建议使用union ,因为union会去掉重复记录。 如果确定需要去掉重复记录再使用。
NULL值永远不会等于或者不等于任何值,也包括null自己,但是需要像计算真实值一样计算可为空列的返回值。
返回所有比ALLEN提成低的员工, 提成 comm字段 ,有空值 。
数据如下:
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-04-02 2975.00 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-05-01 2850.00 30
7782 CLARK MANAGER 7839 1981-06-09 2450.00 10
7788 SCOTT ANALYST 7566 1987-04-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-05-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-03 950.00 30
7902 FORD ANALYST 7566 1981-12-03 3000.00 20
7934 MILLER CLERK 7782 1982-01-23 1300.00 10
14 rows selected
SQL>
我们来看个错误的写法:
SQL> select a.ename , a.comm from emp a where a.comm < (select comm from emp a where a.ename = 'ALLEN');
ENAME COMM
---------- ---------
TURNER 0.00
SQL>
只返回了一条 TURNER的数据,comm有许多空值 的并没有被显示出来,原因在于与空值比较后结果还是空值,需要先转换才行
使用coalesce函数将null值转换为一个可以用来作为标准值进行比较的真实值。
coalesce函数从值列表中返回第一个非NULL值。当遇到NULL值将其替换为0,这样就可以同ALLEN的提成进行比较了。
SQL> select a.ename , a.comm from emp a where coalesce( a.comm,0 ) < (select comm from emp a where a.ename = 'ALLEN');
ENAME COMM
---------- ---------
SMITH
JONES
BLAKE
CLARK
SCOTT
KING
TURNER 0.00
ADAMS
JAMES
FORD
MILLER
11 rows selected
SQL>
或者
select a.ename , a.comm from emp a where nvl( a.comm,0 ) < (select comm from emp a where a.ename = 'ALLEN');