SQL Left Joins and Filter Placement

A left join preserves unmatched left rows by supplying null right-side values. A later WHERE predicate can remove those rows.

Before you start

You should understand tables, keys, joins and SELECT queries. Write down a tiny dataset before reasoning about a query. SQL examples use PostgreSQL-style syntax where relevant; planner behavior, locking and available features must be checked for the database and version used in an application.

The practical goal is to reason through this situation: Filtering the right table’s status in WHERE can eliminate parents without matching children. Read the walkthrough first, then try the interview exercise before opening its answer. The important part is explaining the decision and its consequences, rather than remembering a definition alone.

Step-by-step walkthrough

Step 1: Preserve the left side

A left join produces a null-extended row when nothing matches.

Step 2: Place eligibility in ON

Filter active orders during matching if customers without them must remain.

Step 3: Review later predicates

A WHERE condition on right-side values can remove unmatched customers.

Worked scenario

Filtering the right table’s status in WHERE can eliminate parents without matching children.

SELECT c.id, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'active';

sql

A customer with only inactive orders remains with a null order_id. Moving the status comparison into WHERE removes that customer’s unmatched row.

Common mistake

Treating ON and WHERE as interchangeable changes outer-join semantics.

Verify the behavior

Test no orders, only inactive orders and several active orders for one customer.

Interview exercise

Preserve customers without active orders.

Answer and reasoning

Put the eligible-order condition in ON when unmatched customers must remain in the result.

Continue learning

Compare the scenario with the SQL and PostgreSQL interview questions and test your understanding with the SQL and PostgreSQL MCQs. For terminology and implementation details, consult the reference material.