Metode alternatif 1

Meningkatkan Kinerja Kueri di 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 akan berhenti menelusuri subkueri saat kondisinya TRUE

IN mengumpulkan semua hasil dari subkueri sebelum diteruskan ke kueri luar

Pertimbangkan memakai EXISTS alih-alih IN dengan subkueri

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 dan NULL

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

UN_Region	CountryName	Capital

Menangani NOT IN dengan 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, dan NOT IN

Kelebihan

Hasil dapat memuat kolom apa pun dari kueri luar, dalam urutan apa pun

Kekurangan

Cara NOT IN menangani nilai NULL dalam subkueri

Ayo berlatih!

Meningkatkan Kinerja Kueri di SQL Server