대안 메서드 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은 서브쿼리의 모든 결과를 모은 뒤 바깥 쿼리에 전달합니다

서브쿼리에는 IN 대신 EXISTS 사용을 고려하십시오

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 처리 방식

연습해 봅시다!

SQL Server에서 쿼리 성능 향상하기