left join 子查询 索引失效 left join和子查询效率对比

通常情况下，3种查询方式的执行时间：

EXISTS <= IN <= JOIN
NOT EXISTS <= NOT IN <= LEFT JOIN

只有当表中字段允许NULL时，NOT IN的方式最慢：

NOT EXISTS <= LEFT JOIN <= NOT IN

综上：
IN的好处是逻辑直观简单（通常是独立子查询）；缺点是只能判断单字段，并且当NOT IN时效率较低，而且NULL会导致不想要的结果。
EXISTS的好处是效率高，可以判断单字段和组合字段，并不受NULL的影响；缺点是逻辑稍微复杂（通常是相关子查询）。
JOIN用在这种场合，往往是吃力不讨好。JOIN的用途是联接两个表，而不是判断一个表的记录是否在另一个表。

编程建议：
（以下三条建议中EXISTS和IN同时代指肯定式逻辑和加NOT后的否定式逻辑）
如果查询条件是单字段主键（有索引且不允许NULL），则EXISTS和IN的性能基本一样，IN的查询通常写法简单、逻辑直观。
如果查询条件涉及多个字段，则最好选择EXISTS，千万不要用字段拼接再IN的方式（索引会失效）。
如果条件不确定，选用EXISTS是最保险的办法，性能最好，不受三值逻辑影响（EXISTS只会返回True/False不会返回Unknown），但代码逻辑稍稍复杂，思路要理清楚，而且相关字段最好采用“表（别）名.字段名”的形式。

附一：IN/NOT IN容易出现的两个问题
参看如下代码：

[code=sql] 
SELECT 
EmployeeID = n, 
EmployeeName = 'E' + RIGHT('000' + CAST(n AS varchar(10)),3) 
INTO #Employees 
FROM dbo.Nums WHERE n <= 10; SELECT EmployeeID 
INTO #Badboys 
FROM (SELECT TOP(4) EmployeeID = n FROM dbo.Nums WHERE n <= 10 ORDER BY NEWID()) tmp 
UNION 
SELECT NULL; --问题1： 
SELECT * FROM #Employees WHERE EmployeeID IN (SELECT EmployeeID FROM #Badboys); 
SELECT * FROM #Employees WHERE EmployeeID NOT IN (SELECT EmployeeID FROM #Badboys); 
--问题2： 
SELECT * FROM #Employees WHERE EmployeeName IN (SELECT EmployeeName FROM #Badboys); 
SELECT * FROM #Employees WHERE EmployeeName NOT IN (SELECT EmployeeName FROM #Badboys); 
[/code]

其中：
问题1是三值逻辑的问题，说明了在NOT IN遇到NULL时要特别小心。这也是为什么建议“如果可能，尽量让所有字段都声明为NOT NULL”的原因之一。
问题2是SQL Server子查询处理时命名空间解析的漏洞，说明了在多表查询中采用“表（别）名.字段名”的形式的好处，否则就要对字段名的拼写非常小心。

附二：EXISTS、IN与JOIN性能分析测试代码：

[code=sql] 
--表中字段不允许NULL --TestCase1: 无重复数据，无索引 
CREATE TABLE T1(n int NOT NULL); 
CREATE TABLE T2(n int NOT NULL); 
INSERT INTO T1 
SELECT n FROM dbo.Nums WHERE n <= 100; 
INSERT INTO T2 
SELECT n FROM dbo.Nums WHERE n <= 100 AND n % 2 = 0; --TestCase2: 无重复数据，有索引 
CREATE UNIQUE CLUSTERED INDEX IX_T1 ON T1(n); 
CREATE UNIQUE CLUSTERED INDEX IX_T2 ON T2(n); --TestCase3: 有重复数据，无索引 
DROP TABLE T1; 
DROP TABLE T2; 
CREATE TABLE T1(n int NOT NULL); 
CREATE TABLE T2(n int NOT NULL); 
INSERT INTO T1 
SELECT n FROM dbo.Nums WHERE n <= 100; 
INSERT INTO T2 
SELECT n FROM dbo.Nums WHERE n <= 100 AND n % 2 = 0 
UNION ALL 
SELECT n FROM dbo.Nums WHERE n <= 100 AND n % 3 = 0; --TestCase4: 有重复数据，有索引 
CREATE CLUSTERED INDEX IX_T1 ON T1(n); 
CREATE CLUSTERED INDEX IX_T2 ON T2(n); --表中字段允许NULL 
--TestCase5: 无重复数据，无索引 
DROP TABLE T1; 
DROP TABLE T2; 
CREATE TABLE T1(n int NULL); 
CREATE TABLE T2(n int NULL); 
INSERT INTO T1 
SELECT n FROM dbo.Nums WHERE n <= 100; 
INSERT INTO T2 
SELECT n FROM dbo.Nums WHERE n <= 100 AND n % 2 = 0; --TestCase6: 无重复数据，有索引 
CREATE UNIQUE CLUSTERED INDEX IX_T1 ON T1(n); 
CREATE UNIQUE CLUSTERED INDEX IX_T2 ON T2(n); --TestCase7: 有重复数据，无索引 
DROP TABLE T1; 
DROP TABLE T2; 
CREATE TABLE T1(n int NULL); 
CREATE TABLE T2(n int NULL); 
INSERT INTO T1 
SELECT n FROM dbo.Nums WHERE n <= 100; 
INSERT INTO T2 
SELECT n FROM dbo.Nums WHERE n <= 100 AND n % 2 = 0 
UNION ALL 
SELECT n FROM dbo.Nums WHERE n <= 100 AND n % 3 = 0; --TestCase8: 有重复数据，有索引 
CREATE CLUSTERED INDEX IX_T1 ON T1(n); 
CREATE CLUSTERED INDEX IX_T2 ON T2(n); --Foreach TestCase above，分别执行以下两组语句并观察执行计划： 
--肯定式逻辑 
SELECT T1.* 
FROM T1 
WHERE EXISTS (SELECT * FROM T2 WHERE T2.n = T1.n); SELECT T1.* 
FROM T1 
WHERE T1.n IN (SELECT T2.n FROM T2); SELECT DISTINCT T1.*  --不加DISTINCT可能会引起重复 
FROM T1 
INNER JOIN T2 
ON T1.n = T2.n; --否定式逻辑 
SELECT T1.* 
FROM T1 
WHERE NOT EXISTS (SELECT * FROM T2 WHERE T2.n = T1.n); SELECT T1.* 
FROM T1 
WHERE T1.n NOT IN (SELECT T2.n FROM T2); SELECT T1.* 
FROM T1 
LEFT JOIN T2 
ON T1.n = T2.n 
WHERE T2.n IS NULL; --End Foreach 
--清场 
DROP TABLE T1; 
DROP TABLE T2;