SQL Server Interview Question #69

How do you find rows that have no matching related record?

Joins, Subqueries, CTEs & Set Operators Mid-Level Intermediate

Detailed Explanation

A common pattern is LEFT JOIN followed by a NULL check on a non-nullable key from the right table. Another often clearer pattern is NOT EXISTS.

For example, to find categories with no products, check that no related product exists. NOT EXISTS is generally robust and avoids some NULL-related problems associated with NOT IN.

Code Example

SELECT c.Id, c.Name
FROM dbo.Categories AS c
WHERE NOT EXISTS
(
    SELECT 1
    FROM dbo.Products AS p
    WHERE p.CategoryId = c.Id
);