常见的SQL等价改写

表结构

SQL> desc scott.emp
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
EMPNO NUMBER(4) Y
ENAME VARCHAR2(10) Y
JOB VARCHAR2(9) Y
MGR NUMBER(4) Y
HIREDATE DATE Y
SAL NUMBER(7,2) Y
COMM NUMBER(7,2) Y
DEPTNO NUMBER(2) Y

SQL> desc scott.dept
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14) Y
LOC VARCHAR2(13) Y

半连接

select * from scott.emp a

where deptno in(select deptno from scott.dept b where  a.deptno = b.deptno)

等价

select * from scott.emp a

where exists(select 1 from scott.dept b where  a.deptno = b.deptno)

等价

select * from scott.emp a，(select deptno from scott.dept ) b

where a.deptno = b.deptno

 

反连接

select * from scott.emp a

where deptno not in (select deptno from scott.dept b where  a.deptno = b.deptno)

等价

select * from scott.emp a

where not exists(select 1 from scott.dept b where  a.deptno = b.deptno)

等价

select * from scott.emp a left join scott.dept b

on a.deptno = b.deptno

where b.deptno is null

 

 

标量子查询

select ename, (select dname from scott.dept b where a.deptno = b.deptno)
from scott.emp a
where empno = 7499

等价

select ename, dname
from scott.emp a left join scott.dept

on a.deptno = b.deptno

where empno = 7499

 

表反复查询

select ename from scott.emp

where empno in (select max(empno) from scott.emp group by deptno)

等价

select ename  from (

select ename ,

empno,

max(empno)over(partition by deptno) max_empno

 from scott.emp

) where empno=max_empno

 

 

select * from (
select count(*) from scott.emp where deptno=10),
(
select count(*) from scott.emp where deptno=20)

等价

select
sum(case when deptno=10 then 1 end),
sum(case when deptno=20 then 1 end)
from scott.emp

 

with可以改写sql中反复出现的查询体

 

 

在一些特定场景中（子查询加层次查询，rownum等），sql的某种写法，会导致执行计划有问题，走的是cost较高的执行计划。

所以需要sql改写，来让sql优化器能找出正确的执行计划。

 

还有就是sql的逻辑，多次查询同一个表与查询一次就能获得结果集，那个效率高很明显赛。