MySQL / Relational foundations and precise SQL / Lesson 3

Joins and relationship cardinality

Combine related facts while preserving the intended number of result rows.

About 4 min readContent reviewed 2026-10-23

On this page

Lesson goals

Distinguish inner, left, and cross joins using the shop fixture.
Locate duplication and preserve customers with no orders.

Choose the result grain

Use the original fixture without modifications. Before joining, state what one output row represents: a customer, an order, or an order line. An inner join emits one row per matching pair, so it can increase row count without any error. Customer 1 matches two orders; joining that result to lines creates three rows for that customer. This multiplication is relational behavior, not a malfunction.

Match keys, not labels

Write explicit JOIN ... ON clauses and qualify overlapping column names with aliases. Joining orders.customer_id to customers.id follows the declared relationship. Matching orders.id to customers.id would be syntactically legal but semantically wrong. Equality also follows type and collation rules, which is another reason to avoid loosely formatted text as a relationship key.

sql One customer label per order

SELECT o.id AS order_id, c.name, o.total
FROM orders AS o JOIN customers AS c ON c.id = o.customer_id
ORDER BY o.id;

Expected output

order_id | name | total
101 | Amina | 25.00
102 | Amina | 30.00
103 | Leo | 10.00

The result contains 101/Amina/25.00, 102/Amina/30.00, and 103/Leo/10.00. Amina appears twice because the output grain is orders. Customer uniqueness ensures the join adds one matching label per order; a nonunique lookup table would invalidate that assumption.

Preserve unmatched customers

A left join keeps every left-side row. Where no right-side row satisfies ON, its columns are NULL. Filtering a right-side column in WHERE can remove those preserved rows, effectively demanding a match. Put a condition in ON when it defines which right-side rows may match while keeping all left-side customers. These placements answer different questions and are not interchangeable.

sql All customers, paid matches only

SELECT c.id, c.name, o.id AS paid_order
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id AND o.status = 'paid'
ORDER BY c.id, o.id;

Expected output

id | name | paid_order
1 | Amina | 101
2 | Leo | 103
3 | Noor | NULL

Amina matches 101, Leo matches 103, and Noor remains with a NULL paid_order. Moving o.status = 'paid' into WHERE would remove Noor, because comparison against the generated NULL is unknown. The original new order 102 is never a paid match.

Ask for existence when needed

To list customers without any order, use NOT EXISTS with a correlated condition on customer_id. It expresses absence without building unnecessary projected order rows. A left join followed by WHERE o.id IS NULL is also valid because order id cannot be NULL in a stored row. Testing a nullable descriptive column instead would confuse an absent row with a matched row containing missing information.

Protect totals from multiplication

Joining order totals to order_items repeats the order total for every line. Summing those repeated values gives 90.00 instead of the correct 65.00 across all orders. Aggregate lines by order before comparing to order totals. DISTINCT is not a general repair: two different orders can legitimately have the same total. Verify row counts and identify the key at every intermediate stage.

Common errors and fixes

ERROR 1052 (23000): Column is ambiguous.

Why it happens: Both joined tables contain id, and the query leaves it unqualified.
How to fix it: Use c.id or o.id according to the requested entity.
Prevent it next time: Qualify relationship columns consistently, including ORDER BY.

Every customer appears with every order.

Why it happens: A missing join condition produced a Cartesian product: three times three rows.
How to fix it: Add ON o.customer_id = c.id and recount matches.
Prevent it next time: Predict cardinality before accepting visually plausible results.

Customers without paid orders disappear.

Why it happens: A right-side status filter in WHERE rejected outer-join NULL rows.
How to fix it: Move the paid-match condition into ON when all customers must remain.
Prevent it next time: Write whether each filter restricts matches or the final audience.

Check your understanding

1. List customers with no orders and predict the result.

Show worked solution

SELECT c.id, c.name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) ORDER BY c.id returns only 3/Noor. The subquery asks whether any matching row exists; SELECT 1 does not count or return the orders.

2. Why does joining orders to lines produce four rows rather than three?