Why does a LEFT JOIN stop preserving unmatched rows after I add a filter?

Because the filter runs after the join has produced its rows. For a customer without a matching order, the right-side columns are NULL; orders.status = 'open' is therefore not true, so the WHERE clause removes that row. In SQLite, a LEFT JOIN first adds a NULL-filled right-side row when there is no match under ON, then WHERE excludes rows whose condition is false or NULL. [S1]

The key question is whether you want to filter the final results, or filter which orders count as matches. To keep all customers while matching only open orders, put the status condition in ON.

Assume these invented tables:

customers
id | name
1  | Ada
2  | Ben
3  | Cy

orders
id  | customer_id | status
101 | 1           | open
102 | 1           | closed
103 | 2           | closed

This query filters after joining:

SELECT customers.name, orders.status
FROM customers
LEFT JOIN orders ON orders.customer_id = customers.id
WHERE orders.status = 'open';

Its result is:

name | status
Ada  | open

Ben's closed order does not satisfy the filter, and Cy has no matching order, so neither appears. Now put the filter in the join condition:

SELECT customers.name, orders.status
FROM customers
LEFT JOIN orders
  ON orders.customer_id = customers.id
 AND orders.status = 'open';

The result is:

name | status
Ada  | open
Ben  | NULL
Cy   | NULL

Here the status condition limits which orders match; it does not filter customers out. These rows follow from the example data and SQLite's described outer-join and WHERE processing. [S1]

Start with the output you need:

1. If you need every customer, including those with no qualifying order, put the order condition in ON.
2. If you only want customers whose joined order meets a condition, put it in WHERE.
3. Check what happens for a customer with no order and for one with only a nonqualifying order. Those two cases often reveal the difference.

For example, “show each customer and any open order” calls for an ON condition. “Show customers who have an open order” calls for a WHERE condition if you want only qualifying matches. The join type alone does not guarantee all left-side rows survive later filters: WHERE can remove them. [S1]

Use this small review:

1. Name the left-side rows that must always appear.
2. State what makes a right-side row eligible to match.
3. Put that eligibility condition in ON if unmatched left-side rows must remain.
4. Put a condition in WHERE only when rows failing it should be excluded from the final output.
5. Test one left-side row with no right-side match and one with only a nonqualifying match; compare the actual result with your intended output.

This guide describes SQLite's behavior. The example does not establish how every database handles every query detail, so check the documentation for your database if you are using something other than SQLite. [S1]