SQL语句分析：ON与WHERE的比较_简单嵌套查询与非嵌套查询的比较
某天的工作是修复某个项目的bug，接着就发现，其sql极其混乱，有非常多的left join和in操作，还有嵌套查询（只有一个表的嵌套查询）。不知道看到过哪里的资料说，嵌套查询速度慢，于是我把全部嵌套查询都改成join的形式，嵌套查询里面的where条件，我都写到join...on后面去了。突然一个想法冒出来：筛选条件跟在join...on后面 和 跟在整个sql语句最后面的where后面有什么区别呢？还有嵌套查询真的慢么？于是便有下面的测试产生，数据库环境为MS SQL 2005。
一，inner join
先看看非嵌套查询：
非嵌套inner join
inner join t2 on t1.id = t2.id
inner join t3 on t1.id = t3.id
where t1.a=1 and t2.b=1 and t3.c=1
b.select * from t1
inner join t2 on t1.id = t2.id and t2.b=1
inner join t3 on t1.id = t3.id
where t1.a=1 and t3.c=1
c.select * from t1
inner join t2 on t1.id = t2.id and t2.b=1
inner join t3 on t1.id = t3.id and t3.c=1
where t1.a=1
在上面三个非嵌套查询，让“and t2.b=1”和“and t3.c=1”分别在join...on和where之间游走，用Management Studio选中“包含实际的执行计划”并执行这三条语句，都得出下面这个执行计划。
三个“聚集索引扫描”的谓词从上到下分别是：
1.t3.c=1
2.t1.a=1 (seek谓词：t1.id=t3.id)
3.t2.b=1 (seek谓词：t2.id=t3.id)
故可以认为：在MS SQL2005中，条件跟在inner join...on后面 和 跟在where后面是等价的。
接着看嵌套查询：
嵌套inner join
inner join (select * from t2 where t2.b=1)a on t1.id=a.id
inner join t3 on t1.id = t3.id
where t1.a=1 and t3.c=1
e.select * from t1
inner join (select * from t2 where t2.b=1)a on t1.id=a.id
inner join (select * from t3 where t3.c=1)b on t1.id=b.id
where t1.a=1
f.elect * from t1
inner join (select t3.id,t2.b,t3.c from t3 inner join t2 on t2.id = t3.id where t2.b=1 and t3.c=1)a on t1.id=a.id
where t1.a=1
第一句sql语句把t2的查询变成子查询，第二句sql语句把t2,t3分别变成子查询，第三句把t2和t3的查询合成一个子查询，再看看实际的执行计划：
跟上面非嵌套查询的执行计划一模一样。
故可以认为：简单（注意是简单的，复杂的情况得另外考虑）嵌套查询和其相对应的非嵌套查询形式，执行效率是一样的（网上一些文章指出这是MS SQL优化器针对这些嵌套查询进行了优化）。
--------------------------------------------------------------------------------------------
接着，在上面两个执行计划的图中又发现一个小问题，为什么明明是select t1 inner join t2 inner join t3，执行计划却把t1和t3先inner join(t1.id = t3.id)再跟t2 inner join（t2.id = t3.id）起来？
经过三个表，四个表，五个表进行连接测试，发现这些顺序都是不确定的。很可能这些顺序是根据SQL优化器内的算法所决定的，由于没有源代码，所以无从考究。
(感谢Keep Walking的补充:
数量级，索引，统计的不同都可以导致顺序变化”。
----------------------------------------
PS1：
1.经测试，在inner join on后面t1.id = t2.id与t2.id = t1.id等价
如果发现这文章有错误，欢迎指出。
----------------------------------------------------
PS2：经过songmc指出，发现了一些问题。补充对left join的测试。
二，left join
附上songmc的代码：
Code
INSERT INTO @a SELECT 'aa'
UNION ALL SELECT 'bb'
UNION ALL SELECT 'cc'
UNION ALL SELECT 'dd'
DECLARE @b TABLE(id INT IDENTITY,NAME VARCHAR(20))
INSERT INTO @b SELECT 'ee'
UNION ALL SELECT 'ff'
UNION ALL SELECT 'gg'
UNION ALL SELECT 'hh'
DECLARE @c TABLE (id INT IDENTITY,NAME VARCHAR(20))
INSERT INTO @c SELECT 'ii'
UNION ALL SELECT 'jj'
UNION ALL SELECT 'kk'
UNION ALL SELECT 'll'
--a.
SELECT
*
FROM @a a
LEFT JOIN @b b ON a.id = b.id
LEFT JOIN @c c ON b.id = c.id
WHERE a.NAME != 'aa'
--b.
SELECT
*
FROM @a a
LEFT JOIN @b b ON a.id = b.id
LEFT JOIN @c c ON b.id = c.id AND
a.NAME != 'aa'
在这里，把a.NAME != 'aa' 放在where后面以及放在c的on后面，查询出来的结果：
发现sql语句b比sql语句a的结果多了一行记录，这里c.id和c.name为NULL，到底为什么呢？再做一个实验：
SELECT
*
FROM @a a
LEFT JOIN @b b ON a.id = b.id AND
a.[NAME] != 'aa'
LEFT JOIN @c c ON b.id = c.id
这次把条件放到b后面了，看b和c的查询结果的对比图：
发现不仅是c.id和c.name，连b.id和b.name为NULL，这是为什么呢？
首先要理解left jion就是去掉不符合条件的，保留左表的行。
分析sql语句b中的 “LEFT JOIN @c c ON b.id = c.id AND a.NAME != 'aa'”，表c和x（x为表a和表b连结后的中间结果）连结后的中间结果x1中，去掉a.NAME不等于'aa'的行，并保留左表，因此c.id和c.name为NULL。
分析sql语句c中的 “LEFT JOIN @b b ON b.id = c.id AND a.NAME != 'aa'”，表b和表a连结后的中间结果x2中，去掉a.NAME不等于'aa'的行，并保留左表，因此b.id和b.name为NULL。又因为“LEFT JOIN @c c ON b.id = c.id”，b.id为NULL，保留左表，所以c.id和c.name为NULL。
inner join和left join不一样，inner join左边或右边的结果为空，该行记录就不显示了。而left join会以左边的表为保留表，就算右边的结果为空，该行仍然显示。
于是得出的结论是：条件放在on与放在where后面的作用是不一样的。on对中间结果进行筛选，再由where对最终结果进行筛选。
PS3:下面是Left Join on+where的执行过程（附上songmc给我的文档的精华部分,根据《Inside Microsoft® SQL Server™ 2005 T-SQL Querying》进行基于自己理解的修改）
sql：
FROM a
LEFT JOIN b ON a.id = b.id AND b.Name != 'ff'
WHERE a.NAME != 'aa'
步骤1：FROM后面的两个表a，b进行笛卡尔积，生成虚拟表VT1。
步骤2：应用ON筛选器到VT1，只有条件（当前的条件为a.id = b.id AND b.Name != 'ff'）为真的行，插入到VT2。
步骤3：添加外部行(OUTER (join))
这一步只对OUTER JOIN起作用，如果是LEFT JOIN会以左边的表为保留表，如果是RIGHT JOIN会以右边的表为保留表。所谓外部行是指，保留表中的行。即使第二步的ON过滤掉了一些行，在这一步，会根据保留表添加第二步过滤掉的行，并生成VT3。
步骤4.应用WHERE筛选器到VT3，只有条件(当前是Name != ‘aa’)为真的行，插入到VT4。
由于篇幅有限（其实是不想这篇文章太长了），就不把例子贴上了。
