Menu
CoddyTech

Find Missing Matches

Part of the Connected Data section of Coddy's PostgreSQL journey. Lesson 4 of 25.

A left join can find records without a related row. After joining, test a right-side key with IS NULL. Test a key known to be present on real matches, not an optional attribute.

Input table customers:

idnamecity
1AriHaifa
2BoHaifa
3CleoEilat
4DeeEilat

Input table orders:

idcustomer_idamountordered_onstatusmeta
111202025-01-02paid{"channel":"web","items":2,"shipping":{"city":"Haifa"}}
121302025-01-04paid{"channel":"app","items":3,"shipping":{"city":"Haifa"}}
132302025-02-01pending{"channel":"web","items":1}
143502025-02-03paid{"channel":"app","items":5,"shipping":{"city":"Eilat"}}
152102025-02-03paid{"channel":"web","items":2}
SELECT c.id, c.name FROM customers AS c LEFT JOIN orders AS o ON o.customer_id = c.id AND o.status = 'pending' WHERE o.id IS NULL ORDER BY c.id;

Ari, Cleo and Dee have no pending order. A customer with paid orders can still satisfy this query because the ON condition looks only for pending matches.

LEFT JOIN followed by a null test on a right-side key finds missing matches.

challenge icon

Challenge

Medium

Return id and name for customers who have never placed any order. Order by id.

The input tables are shown in the test panel. Each run starts from fresh data. Return the named columns in the stated order; do not hard-code the displayed rows.

Try it yourself

-- Write your SQL here.
quiz iconTest yourself

This lesson includes a short quiz. Start the lesson to answer it and track your progress.

All lessons in Connected Data

Practice on your own: PostgreSQL playground