替代方法 1

改进 SQL Server 中的查询性能

Dean Smith

Founder, Atamai Analytics

EXISTS

SELECT CustomerID,
       CompanyName,
       ContactName 
FROM Customers c
WHERE EXISTS
        (SELECT 1 
         FROM Orders o
         WHERE c.CustomerID = o.CustomerID);

CustomerID	CompanyName	ContactName
ALFKI	Alfreds Futterkiste	Maria Anders
LAUGB	Laughing Bacchus Wine Cellars	Yoshi Tannamuri
QUICK	QUICK-Stop	Horst Kloss
...	...	...

IN

SELECT CustomerID,
       CompanyName,
       ContactName 
FROM Customers
WHERE CustomerID IN
        (SELECT CustomerID 
         FROM Orders);

CustomerID	CompanyName	ContactName
ALFKI	Alfreds Futterkiste	Maria Anders
LAUGB	Laughing Bacchus Wine Cellars	Yoshi Tannamuri
QUICK	QUICK-Stop	Horst Kloss
...	...	...

EXISTS vs. IN

EXISTS 在条件为 TRUE 时会停止搜索子查询

IN 会先收集子查询的全部结果，再传给外层查询

有子查询时，考虑用 EXISTS 替代 IN

NOT EXISTS

SELECT CustomerID,
       CompanyName,
       ContactName 
FROM Customers c
WHERE NOT EXISTS 
        (SELECT 1 
         FROM Orders o
         WHERE c.CustomerID = o.CustomerID);

CustomerID	CompanyName	ContactName
FISSA	FISSA Fabrica Inter. Salchichas S.A.	Diego Roel
PARIS	Paris spécialités	Marie Bertrand

NOT IN

SELECT CustomerID,
       CompanyName,
       ContactName 
FROM Customers
WHERE CustomerID NOT IN
        (SELECT CustomerID 
         FROM Orders);

CustomeID	CompanyName	ContactName
FISSA	FISSA Fabrica Inter. Salchichas S.A.	Diego Roel
PARIS	Paris spécialités	Marie Bertrand

NOT IN 与 NULL

SELECT UNStatisticalRegion AS UN_Region
      ,CountryName
      ,Capital
FROM Nations
WHERE Capital NOT IN 
        (SELECT NearestPop 
         FROM Earthquakes);

UN_Region	CountryName	Capital

处理 NOT IN 的 NULL

SELECT UNStatisticalRegion AS UN_Region
      ,CountryName
      ,Capital
FROM Nations
WHERE Capital NOT IN 
        (SELECT NearestPop 
         FROM Earthquakes
         WHERE NearestPop IS NOT NULL);

UN_Region	CountryName	Capital
South Asia	India	New Delhi
East Asia and Pacific	Indonesia	Jakarta
East Asia and Pacific	East Timor	Dili
Sahara Africa	Comoros	Moroni
...	...	...

EXISTS、NOT EXISTS、IN 与 NOT IN

优点

结果可包含外层查询的任意列，顺序不限

缺点

NOT IN 在子查询含 NULL 时的处理方式

Passons à la pratique !

改进 SQL Server 中的查询性能