INNER JOIN and Join Keys

Real data lives in many tables

Real source data is never in one table. A raw e-commerce feed splits orders (who bought, when, total) from customers (name, region, email) from products (name, category, price). To answer "revenue by customer region" you must first stitch these back together on their shared keys. That stitching is a join, and the everyday workhorse is the INNER JOIN: it returns rows where a key in one table matches a key in the other, and drops everything with no match on either side.

Worked example: attach each order to its customer

SELECT
  o.order_id,
  o.total_cents,
  c.customer_name
FROM orders AS o
INNER JOIN customers AS c
  ON o.customer_id = c.customer_id;

Read it as: for each orders row, find the customers row whose customer_id equals this order's customer_id, and glue their columns side by side. An order with no matching customer, or a customer with no orders, does not appear. That's the "inner" part.

Press play to watch each order find its customer, and see exactly why order 103 and Alan drop out:

orders · INNER JOIN · customers ON customer_id = customer_id

Step 1 / 4

order_id	customer_id	total_cents
100	1	2500
101	2	5000
102	1	9900
103	9	1500

customer_id	customer_name
matched: 1	Ada Lovelace
2	Grace Hopper
3	Alan Turing

customer_id=1 matches 1 customers row.

Result (1 row)

orders.order_id	orders.customer_id	orders.total_cents	customers.customer_id	customers.customer_name
100	1	2500	1	Ada Lovelace

Anatomy of a join

FROM   orders     AS o          ← left table, aliased 'o'
INNER JOIN customers AS c       ← right table, aliased 'c'
  ON   o.customer_id = c.customer_id
       └─── join key: the column(s) that relate the two tables ───┘
SELECT o.order_id, c.customer_name
       └── qualify columns with the alias so 'customer_id' isn't ambiguous ──┘

Three habits that make joins readable and correct:

1. Alias every table (orders AS o). Short aliases keep the ON and SELECT legible.
2. Qualify every column (o.order_id, not order_id). The instant two tables share a column name, an unqualified reference is ambiguous and errors.
3. Name the join key deliberately. The ON clause is the contract: "these two rows describe the same thing."

Cardinality: the concept that separates a DE from a query monkey

Before you join, know the relationship between the tables:

• N:1 (many-to-one): each order points at exactly one customer, but a customer has many orders, so orders to customers is the canonical many-to-one join. (A true 1:1 is rarer: each row on one side matches at most one on the other, like users to user_profiles.)
• 1:N (one-to-many): one order has many order_items. Joining orders to order_items multiplies each order row by its number of line items.
• M:N (many-to-many): needs a bridge table (you'll model these in Level 3).

Why this matters: a 1:N join fans out rows, and a fan-out inflates a SUM. If you join orders to order_items and then SUM(orders.total_cents), you sum each order's total once per line item. A 3-item order counts its total three times. The revenue triples and looks plausible, which is how bad numbers ship. The fix is to know your grain: after a fan-out join, aggregate the line-level measure (SUM(quantity * unit_price_cents)), never the pre-aggregated header total.

Keep it readable / common pitfall

Forgetting the ON clause (or writing ,-separated tables with the join condition in WHERE) can produce a cross join: every row paired with every row, N×M rows. If your result set is suspiciously huge, you dropped or weakened a join key. Always join on the full key; a partial key silently fans out.

Recap: INNER JOIN … ON key returns only matching rows from both tables; alias and qualify everything, and always know the cardinality: a 1:N join fans out rows and will double-count any header-level SUM.