Joins

Your data is spread across tables on purpose. A join puts it back together for one query.

Joins are where SQL stops being a list of commands and starts being a skill. The syntax takes ten minutes. Predicting how many rows you get back takes longer, and it is the part that decides whether your totals are correct.

A join works on a condition, almost always matching a foreign key to a primary key. For each pair of rows where the condition holds, the database produces one output row containing columns from both.

Read that again with the emphasis on "each pair". The row count of the result is a consequence of how many pairs match, not of how many rows either table has.

SELECT
    o.order_id,
    o.order_date,
    c.company_name,
    c.country
FROM orders AS o
INNER JOIN customers AS c ON c.customer_id = o.customer_id
WHERE o.order_date >= '2026-01-01'
ORDER BY o.order_date DESC;

• INNER JOIN keeps only rows where the condition is satisfied on both sides. An order with no matching customer would be dropped, and a customer with no orders never appears.
• The ON clause states how the tables relate. Forgetting it, or getting the columns wrong, is the single most expensive mistake in this lesson.
• The word INNER is optional — plain JOIN means the same thing. Writing it out makes the intent explicit for the next reader.
• Row count here: exactly one row per matching order, because each order has exactly one customer. That is the safe case.

Join a one-to-many relationship and the "one" side repeats. Order 1001 has three items, so joining orders to order_items produces three rows for order 1001 — and the order's own columns are copied into all three.

That is correct behaviour. There is no other sensible answer. The problem is what happens when you then add up one of those repeated columns.

order_items for order 1001
--------------------------------------
order_item_id  product_id  quantity
5001           318         2
5002           412         1
5003           507         4

SELECT o.order_id, o.shipping_fee, oi.quantity
FROM orders o JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.order_id = 1001;

order_id  shipping_fee  quantity
1001      5.00          2
1001      5.00          1
1001      5.00          4

SUM(shipping_fee) over this result = 15.00
The actual shipping fee for order 1001 = 5.00

• shipping_fee appears three times because the order row was repeated once per item. Summing it triples the figure.
• Nothing errored. The query is valid, the join is correct, and the number is wrong by 300%. This is why joins deserve more than ten minutes.

-- WRONG: shipping_fee is counted once per order item
SELECT SUM(o.shipping_fee) AS total_shipping
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.order_date >= '2026-01-01';

-- Correct option 1: do not join. The fee lives on orders, so count it there.
SELECT SUM(o.shipping_fee) AS total_shipping
FROM orders AS o
WHERE o.order_date >= '2026-01-01';

-- Correct option 2: aggregate the many side first, then join one row to one row
SELECT
    SUM(o.shipping_fee) AS total_shipping,
    SUM(items.line_total) AS total_goods
FROM orders AS o
JOIN (
    SELECT oi.order_id, SUM(oi.quantity * oi.unit_price) AS line_total
    FROM order_items AS oi
    GROUP BY oi.order_id
) AS items ON items.order_id = o.order_id
WHERE o.order_date >= '2026-01-01';

• Option 1 is the answer whenever you did not actually need the other table. Check what the join is for before adding it.
• Option 2 is the pattern for when you need totals from both levels. Collapsing order_items to one row per order first restores a one-to-one join, so neither figure is multiplied.
• Reaching for SELECT DISTINCT to remove the duplicates does not fix this. It would collapse the three identical rows to one, but it changes the quantity total too, and on real data the rows usually are not identical.

-- All customers, with an order count including the ones who have never ordered
SELECT
    c.company_name,
    COUNT(o.order_id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.company_name
ORDER BY order_count ASC;

-- Only the customers who have never ordered
SELECT c.company_name, c.country
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

• COUNT(o.order_id) counts non-null values, so a customer with no orders correctly shows 0. COUNT(*) would show 1, because that customer still produced one output row with NULLs in it. This distinction matters and is easy to miss.
• The second query uses the NULL that the LEFT JOIN creates as the filter. "No matching row on the right" becomes "the right table's key is NULL", which is a standard idiom worth recognising.
• Grouping is covered fully in the next lesson. Read GROUP BY here as "one output row per company name".

One older style is worth recognising because you will meet it in existing code: listing tables comma-separated and putting the join condition in WHERE, as in FROM orders o, customers c WHERE c.customer_id = o.customer_id. It produces the same result as an INNER JOIN.

Explicit JOIN syntax is preferred for new work. It separates "how these tables relate" from "which rows I want", and forgetting the ON clause is a syntax error rather than a silent cross join.