LEFT JOIN: Location, Location, Location – #TSQL2sDay

Thank you to Sebastian Meine ( @sqlity ) for hosting December’s  T-SQL Tuesday #37. He is writing a series about JOINs this month and asked for others in the community to participate with a post about JOINs.

We are frequently given requirements to return ALL of the data from Table A combined with data from Table B whether the data exists in Table B or not. And, then the requirement changes and asks to see just the rows that either match a certain criteria in Table B don’t exist at all in Table B. Most of us will likely recognize that a LEFT JOIN will give us what we want, but who immediately recognizes that there are a couple of ways to do it?

Let’s start by creating a couple of tables that we can join later, and add some data that will be appropriate for our example:

CREATE TABLE #Customers (CustomerID int PRIMARY KEY IDENTITY, CustomerName varchar(75));
CREATE TABLE #Orders (OrderID int PRIMARY KEY IDENTITY, CustomerID int, ProductName varchar(50));
INSERT INTO  #Customers (CustomerName) VALUES ('Wile E Coyote'), ('Elmer Fudd');
INSERT INTO  #Orders (CustomerID, ProductName)
             VALUES (1, 'Dynamite'), (1, 'Anvil'), (1, 'JetPack'), (1, 'Giant Rock');

Scenario: Return a list of all CustomerNames, and a column indicating whether they have purchased a specific ProductName. The most common way to see this done is:

DECLARE   @ProductName varchar(75) = 'Dynamite';
SELECT    CustomerName
        , Purchased = CASE WHEN ProductName = @ProductName THEN 'Yes' ELSE 'No' END
FROM      #Customers C
LEFT JOIN #Orders O ON C.CustomerID = O.CustomerID
WHERE     ProductName = @ProductName
          OR ProductName IS NULL;

The SQL above often results in a bug because the NULL check gets forgotten. Notice that if we move the ProductName check from the WHERE to the JOIN condition, we get the same result and no longer need the NULL check.

DECLARE   @ProductName varchar(75) = 'Dynamite';
SELECT    CustomerName
        , Purchased = CASE WHEN ProductName = @ProductName THEN 'Yes' ELSE 'No' END
FROM      #Customers C
LEFT JOIN #Orders O ON C.CustomerID = O.CustomerID
AND       ProductName = @ProductName;