Alternative methods 2

Improving Query Performance in SQL Server

Dean Smith

Founder, Atamai Analytics

INNER JOIN

SELECT c.CustomerID
      ,c.CompanyName
      ,o.OrderID
      ,o.OrderDate
      ,o.ShippedDate
      ,o.Freight
FROM Customers c
INNER JOIN Orders o
    ON c.CustomerID = o.CustomerID

CustomerID	CompanyName	OrderID	...
VINET	Vins et alcools Chevalier	10248	...
HANAR	Hanari Carnes	10250	...
VICTE	Victuailles en stock	10251	...
SUPRD	Suprêmes délices	10252	...
...	...	...	...

LEFT OUTER JOIN

Inclusive LEFT OUTER JOIN

Venn Diagram Inclusive Left Outer Join

Exclusive LEFT OUTER JOIN

Venn Diagram Exclusive Left Outer Join

Exclusive LEFT OUTER JOIN

SELECT c.CustomerID
      ,c.CompanyName
      ,o.OrderID
      ,o.OrderDate
      ,o.ShippedDate
      ,o.Freight
FROM Customers c
LEFT OUTER JOIN Orders o
    ON c.CustomerID = o.CustomerID
WHERE o.CustomerID IS NULL

CustomerID	CompanyName	OrderID	...
FISSA	FISSA Fabrica Inter. Salchichas S.A.	NULL	...
PARIS	Paris spécialités	NULL	...

Review: INTERSECT and EXCEPT

INTERSECT: checks for presence

EXCEPT: checks for absence

Advantages

Great for data interrogation
Remove duplicates from the returned results

Disadvantage

The number and order of columns in the SELECT statement must be the same between queries

Review: EXISTS and NOT EXISTS

EXISTS: checks for presence

NOT EXISTS: checks for absence

Advantages

The sub-query will stop searching as soon as it evaluates to TRUE
Results can contain any column from the outer query, and in any order

Disadvantage

Results can only contain columns from the outer query

Review: IN and NOT IN

IN: checks for presence

NOT IN: checks for absence

Advantage

Results can contain any column from the outer query, and in any order

Disadvantages

Results can only contain columns from the outer query
No results returned because of the way NOT IN handles nulls in the sub-query

Review: INNER JOIN and exclusive L.O.J

INNER JOIN: checks for presence

exclusive LEFT OUTER JOIN: checks for absence

Advantage

Results can contain any column, from all joined queries, in any order

Disadvantage

Requirement to add the IS NULL WHERE filter condition with the exclusive LEFT OUTER JOIN

Let's practice!

Improving Query Performance in SQL Server