Joins — combining tables without double counting

Why joins exist

Relational databases split information across tables to avoid repeating it: the store's city is stored once in stores, not on every order. To answer "revenue by city" you must join orders to stores on store_id.

A join pairs rows from two tables whose key values match, according to a condition in ON.

INNER JOIN — only matching rows

SELECT o.order_id, o.order_date, s.store_name, s.city
FROM orders AS o
INNER JOIN stores AS s ON s.store_id = o.store_id
ORDER BY o.order_id
LIMIT 5;

o and s are table aliases. Prefixing every column with its table (o.order_id) makes queries readable and avoids "ambiguous column" errors when both tables have a column with the same name. JOIN alone means INNER JOIN.

An inner join drops rows that have no partner. Every order has a valid store here, so nothing is lost — but that is something to verify, not assume.

LEFT JOIN — keep everything on the left

LEFT JOIN keeps all rows of the left table; where there is no match, the right table's columns are NULL. It answers questions like "all stores, with their 2025 plan if they have one":

SELECT s.store_name, SUM(t.target_revenue) AS plan_2025
FROM stores AS s
LEFT JOIN sales_targets AS t
       ON t.store_id = s.store_id
      AND t.year_month LIKE '2025%'
GROUP BY s.store_name
ORDER BY plan_2025;

Baku Airport closed in June 2024, so it has no 2025 plan — it still appears, with NULL. An inner join would have hidden it.

Finding what is missing: the anti-join

LEFT JOIN + IS NULL finds rows without a match — customers who never ordered, products never sold:

SELECT COUNT(*) AS customers_without_orders
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

RIGHT JOIN and FULL OUTER JOIN

• RIGHT JOIN is a LEFT JOIN with the tables swapped. Most analysts rewrite it as a LEFT JOIN for readability.
• FULL OUTER JOIN keeps unmatched rows from both sides — ideal for reconciliation ("which store-months have actuals but no plan, or a plan but no actuals?").

WITH actual AS (
  SELECT store_id, substr(order_date, 1, 7) AS year_month, COUNT(*) AS orders
  FROM orders WHERE order_date >= '2025-01-01' GROUP BY 1, 2
)
SELECT COALESCE(a.store_id, t.store_id)     AS store_id,
       COALESCE(a.year_month, t.year_month) AS year_month,
       a.orders, t.target_revenue
FROM actual AS a
FULL OUTER JOIN (SELECT * FROM sales_targets WHERE year_month >= '2025-01') AS t
  ON t.store_id = a.store_id AND t.year_month = a.year_month
WHERE a.store_id IS NULL OR t.store_id IS NULL;

No rows: every 2025 store-month has both a plan and actual orders. An empty result is a useful result in reconciliation. (The WITH clause is a CTE — explained in the next lesson.)

Self joins

A self join joins a table to itself, for comparing rows of the same table. Example: pairs of stores in the same city.

SELECT a.store_name AS store_a, b.store_name AS store_b, a.city
FROM stores AS a
JOIN stores AS b
  ON a.city = b.city
 AND a.store_id < b.store_id;

a.store_id < b.store_id avoids pairing a store with itself and listing each pair twice. Self joins are also used for hierarchies (employee → manager).

Joining several tables

Revenue by category and channel for completed 2025 orders needs three tables:

SELECT p.category,
       o.channel,
       ROUND(SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct)), 0) AS net_revenue
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN products    AS p  ON p.product_id = oi.product_id
WHERE o.status = 'completed'
  AND o.order_date >= '2025-01-01' AND o.order_date < '2026-01-01'
GROUP BY p.category, o.channel
ORDER BY p.category, o.channel;

Each join adds columns; WHERE filters on the combined rows; GROUP BY summarises.

The fan-out problem

When you join a table to another with a finer grain, rows of the coarser table are repeated. orders has one row per order; order_items has one row per line. Summing an order-level column after the join counts it once per line:

SELECT
  (SELECT COUNT(*) FROM orders WHERE campaign_id IS NOT NULL)     AS campaign_orders_correct,
  (SELECT COUNT(*) FROM orders o JOIN order_items oi ON oi.order_id = o.order_id
     WHERE o.campaign_id IS NOT NULL)                              AS campaign_orders_after_join;

The second number is inflated by the average number of lines per order. Totals like this look plausible, which is why fan-out survives into dashboards.

Practice

Summary

• INNER keeps matches only; LEFT keeps all left rows; FULL keeps everything; RIGHT is a mirrored LEFT.
• Put conditions on the right table of a LEFT JOIN in ON, not WHERE.
• LEFT JOIN … IS NULL finds missing relationships; self joins compare rows within a table.
• Joining to a finer-grained table repeats rows — aggregate first and reconcile totals.