常见的SQL等价改写

常见的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的逻辑，多次查询同⼀个表与查询⼀次就能获得结果集，那个效率⾼很明显赛。

本⼈兼职卖核桃啊（家⾥的核桃）
有兴趣可以微：159********。