Menu
CoddyTech

Keep Rows with LEFT JOIN

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

LEFT JOIN keeps every row from its left table. When no right-side row matches, right-side columns become NULL. This lets a report include customers who have not ordered yet.

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, o.id AS order_id FROM customers AS c LEFT JOIN orders AS o ON o.customer_id = c.id ORDER BY c.id, o.id;

Dee remains with a null order_id because customer 4 has no order. Ari still produces two joined rows. A left join preserves unmatched left rows but does not prevent multiple matches.

Put the population you must retain on the left side of LEFT JOIN.

challenge icon

Challenge

Medium

Return customer_id, name and order_amount for all customers, including those without orders. Order by customer id, then order 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