SQL Server Interview Question #68

What is the difference between conditions in ON and WHERE for an OUTER JOIN?

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

Detailed Explanation

For an outer join, placing a predicate in ON can produce a different result from placing it in WHERE.

The ON clause determines which rows match during the join while preserving required outer rows. A WHERE predicate is applied after the joined result is formed. Therefore, filtering a nullable right-side column in WHERE can eliminate unmatched rows and effectively turn a LEFT JOIN into an inner-like result for that condition.

This distinction is a frequent interview and production-query issue.

Code Example

-- Preserves all categories:
SELECT c.Name, p.Name
FROM dbo.Categories AS c
LEFT JOIN dbo.Products AS p
    ON p.CategoryId = c.Id
   AND p.IsActive = 1;

-- Removes rows where no active product matched:
SELECT c.Name, p.Name
FROM dbo.Categories AS c
LEFT JOIN dbo.Products AS p
    ON p.CategoryId = c.Id
WHERE p.IsActive = 1;