You add photos to a product list, then move the “public photo” condition into WHERE. Products without a public photo disappear, even though the query still says LEFT JOIN. The placement of the condition changed which product rows survive. Keep the same three products throughout this example to see exactly where they go.
Which rows does LEFT JOIN retain?
With the public-photo condition in ON, products B and C remain as left-side rows without a matching public photo. In WHERE, the result is filtered after the join, so only A remains.
Put product on the left and photo on the right. A product can remain in a LEFT JOIN result even without a matching photo; the right-side columns become NULL. That value is neither zero nor an empty string. In this joined result, it means no photo row matched the condition.
The sample data has a public photo for a cup, a draft photo for a lamp, and no photo for a desk:
CREATE TABLE product (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE photo (
id INTEGER PRIMARY KEY,
product_id INTEGER NOT NULL,
status TEXT NOT NULL
);
INSERT INTO product VALUES (1, 'cup'), (2, 'lamp'), (3, 'desk');
INSERT INTO photo VALUES (101, 1, 'public'), (102, 2, 'draft');The join pairs product id with photo product_id. Foreign-key enforcement is omitted solely to keep the example focused; a real schema needs referential-integrity and allowed-status rules.
Put the public-photo condition in ON to retain all products
If every product should appear but only public photos may be attached, put the status condition in ON. It decides which photo counts as a match:
SELECT p.name, ph.id AS photo_id
FROM product AS p
LEFT JOIN photo AS ph
ON ph.product_id = p.id AND ph.status = 'public'
ORDER BY p.id;| name | photo_id |
|---|---|
| cup | 101 |
| lamp | NULL |
| desk | NULL |
The lamp has photo 102, but that photo is a draft. The desk has no photo at all. Neither has a matching public photo, so both show NULL here while their product rows survive. You cannot infer from this result alone that the lamp has no photos of any status.
What changes when the condition moves to WHERE?
Now join photos by product ID first, then keep only result rows whose photo status is public:
SELECT p.name, ph.id AS photo_id
FROM product AS p
LEFT JOIN photo AS ph ON ph.product_id = p.id
WHERE ph.status = 'public'
ORDER BY p.id;Only cup | 101 remains. The lamp's draft row fails the predicate; the desk's NULL status does not make ph.status = 'public' true. LEFT JOIN has not syntactically turned into an inner join, but this filter leaves only products with a public matching photo.
Would WHERE ph.status = 'public' OR ph.id IS NULL repair it? That retains cup and desk but still drops lamp. The lamp already matched draft photo 102, so ph.id is not NULL.
How should you verify lists and counts?
Check that all three products exist, inspect each photo's status, and compare product IDs before and after the join. If you count public photos, consider COUNT(ph.id) rather than COUNT(*): a preserved product with no matching photo still has one result row, while COUNT(ph.id) excludes its NULL right-side ID. Multiple photos can also multiply rows, so check the intended unit of counting.
If the list must contain every product, place the public-status match in ON. If the feature intentionally searches only products with a public photo, the WHERE filter can be appropriate. On larger tables, inspect indexes and the query plan without confusing physical execution order with this logical result explanation.
Key takeaways: ON chooses the photo; WHERE filters the result
To retain left-side products while attaching only public photos, put the status condition in ON. Moving it to WHERE removes both draft-only and photo-free products from the result. Test the public, draft, and absent-photo cases before changing the condition in a production query.
