一个关于SQL关联条件问题
问题如下,以下两段脚本的执行计划是否相同???
脚本一:
SELECT * FROM T1 join T2 on T1.v1=T2.v2 AND T1.v2='....'
脚本二:
SELECT * FROM T1 join T2 on T1.v1=T2.v2 where T1.v2='....'
在内连接时,两段执行脚本的执行计划相同,换句话说所得的数据集相同,但是换做RIGHT JOIN或LEFT JOIN时,可能会出现不同的执行计划,比如:
脚本一:
with T as( select 1 as v1,1 as v2 UNION SELECT 1 as v1,2 as v2 ) SELECT * FROM T t1 LEFT JOIN T t2 ON t2.v1=t1.v2
脚本二:
with T as( select 1 as v1,1 as v2 UNION SELECT 1 as v1,2 as v2 ) SELECT * FROM T t1 LEFT JOIN T t2 ON t2.v1=t1.v2 AND t1.v2=2
脚本三:
with T as( select 1 as v1,1 as v2 UNION SELECT 1 as v1,2 as v2 ) SELECT * FROM T t1 LEFT JOIN T t2 ON t2.v1=t1.v2 where t1.v2=2
on后面的条件只作为关联条件,不作为筛选条件,where后面才作为筛选条件。