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:
id | name | city |
|---|---|---|
1 | Ari | Haifa |
2 | Bo | Haifa |
3 | Cleo | Eilat |
4 | Dee | Eilat |
Input table orders:
id | customer_id | amount | ordered_on | status | meta |
|---|---|---|---|---|---|
11 | 1 | 20 | 2025-01-02 | paid | {"channel":"web","items":2,"shipping":{"city":"Haifa"}} |
12 | 1 | 30 | 2025-01-04 | paid | {"channel":"app","items":3,"shipping":{"city":"Haifa"}} |
13 | 2 | 30 | 2025-02-01 | pending | {"channel":"web","items":1} |
14 | 3 | 50 | 2025-02-03 | paid | {"channel":"app","items":5,"shipping":{"city":"Eilat"}} |
15 | 2 | 10 | 2025-02-03 | paid | {"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
MediumReturn 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.
This lesson includes a short quiz. Start the lesson to answer it and track your progress.
All lessons in Connected Data
1Connect Tables
Match Rows with JOINKeep Rows with LEFT JOINCount After a JoinFind Missing MatchesCustomer CoveragePractice on your own: PostgreSQL playground