记录一下常用sql语句的多种写法，顺便复习一下sql语句！
最近处理的问题涉及SQL Tuning的东西比较多。不少语句不是加几个索引这么简单，而是语句是在太复杂了，有些作者都不知道是谁，逻辑非常难理解。碰到这种情况着实令人头疼。但是根据经验，很多语句的书写方式是可以用其他方式代替，通过尝试修改语句的写法，往往取得不错的效果。
当然，改变语句的写法只是语句优化的一种手段之一，在这个基础上，在结合其他的优化手段（采用PLSQL块提花单个语句、采用游标、提示等）才能取得最好的优化效果。以下的替换中，大多数情况下，右边的写法会优于左边的写法，并且根据等价原理进行互换，可以在复杂语句中组合成多种写法：
1.1.1 OR -> IN
SELECT B
FROM T
WHERE C = 1
OR C = 2;
=>
SELECT B
FROM T
WHERE C IN (1,2);
1.1.2 IN -> EXISTS
SELECT B
FROM T1
WHERE C IN (SELECT C FROM T2 WHERE A=’aaa’);
=>
SELECT B
FROM T1
WHERE EXISTS (SELECT 1 FROM T2 WHERE A=’aaa’ and T1.C = T2.C);
1.1.3 IN -> JOIN
SELECT B FROM T1
IXDBA.NET社区论坛
WHERE B IN (SELECT B FROM T2);
=>
SELECT DISTINCT T1.B FROM T1, T2
WHERE T1.B=T2.B
1.1.4 INTERSECT -> JOIN
SELECT B FROM TT1
INTERSECT
SELECT B FROM TT2;
=>
SELECT DISTINCT T1.B FROM T1, T2
WHERE T1.B=T2.B
1.1.5 DISTINCT -> EXISTS
SELECT DISTINCT T1.B
FROM T1, T2
WHERE T2.A=’aaa’
and T1.C = T2.C;
WHERE EXISTS (SELECT 1 FROM T2 WHERE A=’aaa’ and T1.C = T2.C);
=>
SELECT B
FROM T1
WHERE EXISTS (SELECT 1 FROM T2 WHERE A=’aaa’ and T1.C = T2.C);
1.1.6 EXISTS -> JOIN
SELECT B
FROM T1
WHERE EXISTS (SELECT 1 FROM T2 WHERE A=’aaa’ and T1.C = T2.C);
=>
SELECT T1.B
FROM T1, T2
WHERE T2.A=’aaa’
and T1.C = T2.C;
WHERE EXISTS (SELECT 1 FROM T2 WHERE A=’aaa’ and T1.C = T2.C);
1.1.7 NOT IN -> NOT EXISTS
SELECT B
FROM T1
WHERE C NOT IN (SELECT C FROM T2 WHERE A=’ccc’);
=>
SELECT B
FROM T1
WHERE NOT EXISTS (SELECT 1 FROM T2 WHERE A=’ccc’ AND T1.B = T2.B);
1.1.8 NOT IN/NO EXISTS -> OUTER JOIN + IS NULL
SELECT B
FROM T1
WHERE C NOT IN (SELECT C FROM T2 WHERE A=’ccc’);
=>
SELECT B
FROM T1 Ta,
(SELECT C FROM T2 WHERE A=’ccc’) Tb
WHERE Ta.C = Tb.C(+)
AND Tb.C IS NULL;
1.1.9 NOT IN/NO EXISTS -> NOT IN + /*+ HASH_AJ */
SELECT B
FROM T1
WHERE C NOT IN (SELECT C FROM T2 WHERE A=’ccc’);
=>
SELECT /*+ HASH_AJ */B
FROM T1
WHERE C NOT IN (SELECT C FROM T2 WHERE A=’ccc’);
优化器缺省是用嵌套循环来处理anti-joins的，但是如果使用了MERGE_AJ、HASH_AJ、NL_AJ提示，NOT IN
