## UNION ALL与空字符串
UNION ALL通常用于合并多个数据集,Oracle中常常把空字符串当作NULL处理：
```
SQL> SELECT SYSDATE FROM dual WHERE ‘’ IS NULL;
SYSDATE
-------------------
2018-01-16 14:41:14
但空字符串与NULL并不等价：
SELECT 1 AS id FROM dual UNION ALL SELECT ‘’ FROM dual;
ORA-01790: 表达式必须具有与对应表达式相同的数据类型
空字符串本身是carchar2类型，这与NULL可以是任何类型不同，它们不等价。
```
## UNION 与 OR
当在条件中有OR时，经常会改写为UNION：
```
SELECT empno, ename FROM emp WHERE empno = 7788 OR ename = ‘SCOTT’;
EMPNO ENAME
----- ------
7788 SCOTT
1 row selected
改写为：
SELECT empno, ename FROM emp WHERE empno = 7788
UNION ALL
SELECT empno, ename FROM emp WHERE ename = ‘SCOTT’;
EMPNO ENAME
--------- ----------
7788 SCOTT
7788 SCOTT
2 rows selected
结果是错的，因为原语句中用的条件是or，是两个结果集的合集而并非并集，所以一般改写时改为UNION来去掉重复的数据：
SELECT empno, ename FROM emp WHERE empno = 7788
UNION
SELECT empno, ename FROM emp WHERE ename = ‘SCOTT’;
EMPNO ENAME
--------- ----------
7788 SCOTT
1 row selected
这样两个语句分别可以用empno及ename上的索引。
```
## UNION与去重
UNION与UNION ALL的区别就是一个去重，一个不去重，这种去重方式一般都不会有问题，但也有少数例外。例如：
```
SELECT deptno FROM emp WHERE mgr = 7698 OR job = ‘SALESMAN’ ORDER BY 1;
DEPTNO
----------
30
30
30
30
30
5 rows selected
改用 UNION 后：
SELECT deptno FROM emp WHERE mgr = 7698
UNION
SELECT deptno FROM emp WHERE job = ‘SALESMAN’;
DEPTNO
----------
30
1 row selected
只剩下了一行数据，结果显然不对。
```
从以上实验可以看出：
1.不仅两个数据集间重复的数据会被去重，而单个数据集里重复的数据也会被去重。
2.有重复数据的数据集用UNION后得到的数据与预期会不一致。
像这种数据可以在去重前加入一个可以唯一标识各行的列即可：
```
SELECT empno,deptno FROM emp WHERE mgr = 7788
UNION
SELECT empno,deptno FROM emp WHERE job = ‘SALESMAN’;
EMPNO DEPTNO
---------- ----------
7499 30
7521 30
7654 30
7844 30
7876 20
5 rows selected
再嵌套一层就是想要的结果了：
SELECT deptno
FROM
(
SELECT empno,deptno FROM emp WHERE mgr = 7698
UNION
SELECT empno,deptno FROM emp WHERE job = ‘SALESMAN’
)
ORDER BY 1;
DEPTNO
----------
30
30
30
30
30
5 rows selected
```
## 组合相关的行
相对于查询单表中的数据来说，平时更常见的需求是要在多个表中返回数据。比如，显示部门10的员工编码、姓名及所在部门名称和工作地址：
```
显示部门 10 的员工编码、姓名及所在部门名称和工作地址：
SQL> SELECT e.empno, e.ename, d.dname, d.loc
FROM emp e
INNER JOIN dept d ON (e.deptno = d.deptno)
WHERE e.deptno = 10;
EMPNO ENAME DNAME LOC
----- ---------- -------------- -------------
7782 CLARK ACCOUNTING NEW YORK
7839 KING ACCOUNTING NEW YORK
7934 MILLER ACCOUNTING NEW YORK
3 row selected
另有写法如下：
SQL> SELECT e.empno, e.ename, d.dname, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.deptno = 10;
```
其中，JOIN 的写法是 SQL-92 的标准，当有多个表关联时，JOIN 方式的写法能更清楚地看清各表之间的关系，因此，个人建议大家写查询语句时优先使用 JOIN 的写法。
## IN、EXISTS 和 INNER JOIN
下面先创建一个表 emp2：
```
DROP TABLE emp2 PURGE;
CREATE TABLE emp2 AS
SELECT ename, job, sal, comm FROM emp WHERE job = ‘CLERK’;
```
要求返回与表 emp2(empno, job, sal)中数据相匹配的 emp(empno, ename, job, sal,deptno)信息。
有 IN、EXISTS、INNER JOIN 三种写法：
```
IN写法：
SQL>EXPLAIN PLAN FOR SELECT empno, ename, job, sal, deptno
FROM emp
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 | 320 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 4 | 320 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 742 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP2 | 4 | 108 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access(“ENAME”=“ENAME” AND “JOB”=“JOB” AND “SAL”=“SAL”)
EXISTS写法：
SQL> EXPLAIN PLAN FOR SELECT empno, ename, job, sal, deptno
FROM emp a
WHERE EXISTS (SELECT NULL
FROM emp2 b
WHERE b.ename = a.ename
AND b.job = a.job
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 | 320 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 4 | 320 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 742 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP2 | 4 | 108 | 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”)
因为子查询的 JOIN 列(emp2.ename,emp2.job,emp2.sal)没有重复行，所以这个查询可
以直接改为 INNER JOIN：
SQL> EXPLAIN PLAN FOR SELECT a.empno, a.ename, a.job, a.sal, a.deptno
FROM emp a
INNER JOIN emp2 b ON (b.ename = a.ename AND b.job = a.job AND b.sal =
a.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 | 320 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 4 | 320 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP2 | 4 | 108 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 742 | 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”)
```
或许与大家想象的不一样，以上三个 PLAN 中 JOIN 写法利用了 HASH JOIN（哈希连接），其他两种运用的都是 HASH JOIN SEMI（哈希半连接），说明在这个语句中的 IN 与 EXISTS效率是一样的。所以，在不知哪种写法高效时应查看 PLAN，而不是去记固定的结论。
## INNER JOIN、LEFT JOIN、RIGHT JOIN 和 FULL JOIN 解析
```
LEFT JOIN：
SELECT l.str AS left_str, r.str AS right_str
FROM l
INNER JOIN r ON l.val = r.val
ORDER BY 1, 2;
LEFT_S RIGHT_S
------ -------
left_3 right_3
left_4 right_4
返回两表相匹配的数据
```
```
LEFT JOIN：
SELECT l.str AS left_str, r.str AS right_str
FROM l
LEFT JOIN r ON l.val = r.val
ORDER BY 1, 2;
LEFT_S RIGHT_S
------ -------
left_1
left_2
left_3 right_3
left_4 right_4
4 rows selected.
左表为主表，返回所有数据，右表只返回与左表相匹配的数据。
```
```
RIGHT JOIN：
SELECT l.str AS left_str, r.str AS right_str
FROM l
RIGHT JOIN r ON l.val = r.val
ORDER BY 1, 2;
LEFT_S RIGHT_S
------ -------
left_3 right_3
left_4 right_4
right_5
right_6
4 rows selected.
```
## 自关联
两次查询同一张表，分别取不同的别名，这样就可以当作两个表，将这两个表和JOIN连接起来。
![[1700014893972.png]]
对于这样的表如何根据这个信息返回主管的姓名？
```
SELECT 员工.empno AS 职工编码,
员工.ename AS 职工姓名,
员工.job AS 工作,
员工.mgr AS 员工表_主管编码,
主管.empno AS 主管表_主管编码,
主管.ename AS 主管姓名
FROM emp 员工
LEFT JOIN emp 主管 ON (员工.mgr = 主管.empno)
ORDER BY 1;
```
可以理解为在两个不同的数据集中取数据。
## NOT IN\NOT EXISTS和LEFT JOIN
对于有一些部门中只有部门的名字，一个员工都没有：
```
select count(*) from emp where deptno = 40;
COUNT(*)
----------
0
1 row selected
```
可以通过关联查询将这些信息查出来，三种方法：NOT IN\NOT EXISTS\LEFT JOIN
**NOT IN**：
```
EXPLAIN PLAN FOR
SELECT *
FROM dept
WHERE deptno NOT IN (SELECT emp.deptno FROM emp WHERE emp.deptno IS NOT NULL);
SELECT * FROM TABLE(dbms_xplan.display());
```
NOT EXTSIS：
```
SELECT *
FROM dept
WHERE NOT EXISTS (SELECT NULL FROM emp WHERE emp.deptno = dept.deptno);
```
根据前面介绍过的左联知识，LEFT JOIN取出的是左表中所有的数据，其中与右表不匹配的就是表示左表NOT IN右表。所以在本节中LEFT JOIN加上IS NULL，就是LEFT JOIN的写法。
```
SELECT dept.*
FROM dept
LEFT JOIN emp ON emp.deptno = dept.deptno
WHERE emp.deptno IS NULL;
```
如果想改写，就要对比改写前后的PLAN，根据PLAN来判断并测试哪种方法的效率高，而不能凭借某些结论来碰运气。
# 改写调优案例分享
## 为什么不建议使用标量子查询
```
SELECT empno,
ename,
sal,
deptno,
(SELECT d.dname FROM dept d WHERE d.deptno = e.deptno) as dname
FROM emp e;
Plan hash value: 2981343222
------------------------------------------------------------------------
| Id | Operation | Name | Rows| Bytes| Cost(%CPU)|
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 644 | 3 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 22 | 1 (0)|
|* 2 | INDEX UNIQUE SCAN | PK_DEPT| 1 | | 0 (0)|
| 3 | TABLE ACCESS FULL | EMP | 14 | 644 | 3 (0)|
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(“D”.“DEPTNO”=:B1)
```
通过执行计划可以看到，标量子查询实际上执行的是：
``SELECT d.dname FROM dept d WHERE d.deptno = :B1
只是针对emp的每一行，：B1取不同的值，这时执行计划只有这一种，所以如果不能改写语句，就要在对应的列上建立索引（如上所示的PK_DEPT）来提高查询速度。
