Métodos alternativos 1

Melhorando o Desempenho de Consultas no 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 para de buscar na subconsulta quando a condição for TRUE

IN coleta todos os resultados da subconsulta antes de passar para a consulta externa

Considere usar EXISTS em vez de IN com subconsulta

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 e NULLs

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

UN_Region	CountryName	Capital

Tratando NULLs com NOT IN

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 e NOT IN

Vantagens

Os resultados podem conter qualquer coluna da consulta externa, em qualquer ordem

Desvantagens

Como NOT IN lida com valores NULL na subconsulta

Vamos praticar!

Melhorando o Desempenho de Consultas no SQL Server