T-SQL reference › LEFT JOIN … IS NULL

Find rows with no match in SQL Server: LEFT JOIN … IS NULL

An inner JOIN only keeps rows that match on both sides. To find the ones that don’t match, keep everything from one side and look for the gaps.

Customers who have never ordered

SELECT c.customer_id, c.first_name, c.last_name, c.email
FROM Customers c
LEFT JOIN Orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

LEFT JOIN keeps every customer. Where there’s no order, every column from Orders comes back NULL, so filtering on a column that is never NULL in a real order (its key) leaves only the customers with none.

Three ways to get it wrong

Using a plain JOIN returns nothing. Writing = NULL instead of IS NULL returns nothing. Putting an extra condition on Orders in the WHERE clause (say, o.status = 'Shipped') quietly turns the LEFT JOIN back into an inner join. Put conditions on the right-hand table in the ON clause instead.

The same with NOT EXISTS

SELECT p.product_id, p.product_name
FROM Products p
WHERE NOT EXISTS (SELECT 1 FROM OrderLines ol WHERE ol.product_id = p.product_id);

NOT EXISTS reads closer to the question and never duplicates rows. Both forms are fine in SQL Server. Avoid NOT IN (subquery) when the subquery can return NULL: one NULL makes the whole thing return nothing.

Try it on Harbour & Co

Becky Ashworth (Support) needs this now: “Who signed up but never ordered? I want to send them a nudge.” Write the query, run it, send the results. On the paid plan.

Take the ticket →