参考地址:http://blog.csdn.net/jwisdom/article/details/1803577
IN用法
SELECT *
FROM t1
WHERE t1.a IN ( SELECT t2.b FROM t2 )
可以理解为:
SELECT *
FROM t1,
(
SELECT DISTINCT t2.b FROM t2
) t2
WHERE t1.a = t2.b;
结论:
t2不能是个大表,因为要对t2进行全表“唯一排序”
t1能是个大表,因为t1.a = t2.b会走关联索引
EXISTS 用法
SELECT *
FROM t1
WHERE EXISTS (
SELECT NULL
FROM t2
WHERE Y = X
)
可以理解为:
FOR a IN ( SELECT * FROM t1 )
LOOP
IF ( EXISTS (
SELECT NULL
FROM t2
WHERE t2.b = t1.a )
THEN
OUTPUT THE RECORD!
END IF
END LOOP
表t1进行全表扫描,表t1不能是个大表
t2可以很大,通过关联t2.b = t1.a可以走t2.b的索引。
对比得出的结论:
IN适合(表t1记录>表t2记录);
EXISTS适合(表t1记录<表t2记录)。