Menu
CoddyTech

Match Rows with JOIN

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

An order stores a customer id instead of repeating customer details. JOIN ... ON matches rows using a relationship. Short table aliases such as c and o make qualified column names easier to read.

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

Order 11 matches customer 1, Ari. Ari appears again for order 12 because one customer can have several orders. A join returns matching pairs, not one row per customer.

Join related keys with ON and qualify ambiguous column names with a table alias.

challenge icon

Challenge

Medium

Return order_id and city for every order with a matching customer. Order by 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