Get New CustomerID using Self Join in SQL Server

Question

I am trying to get all customerID who didn't place an order in the previous year. My logic to solve this is to do a self left john like below.

SELECT distinct t1.[CustomerID]
FROM [AdventureWorks2008].[Sales].[SalesOrderHeader] t1
    LEFT JOIN [AdventureWorks2008].[Sales].[SalesOrderHeader] t2
    on t1.CustomerID = t2.CustomerID
Where 
    t1.OrderDate >= '20040101'
    and t2.OrderDate > '20030101'
    and t2.OrderDate <= '20031231'
    and t2.customerID is null

But it returns an empty set. What am I missing here ?

Answer 1

Move WHERE conditions to the ON clause:

SELECT distinct t1.[CustomerID]
FROM [AdventureWorks2008].[Sales].[SalesOrderHeader] t1
LEFT JOIN [AdventureWorks2008].[Sales].[SalesOrderHeader] t2
   ON t1.CustomerID = t2.CustomerID AND
      t1.OrderDate >= '20040101'    AND
      t2.OrderDate BETWEEN '20030101' AND '20031231'
WHERE t2.customerID IS NULL

Your original query was a bit paradoxical because matching records from the previous year would never be NULL.