Following the Foreign Keys

Concepts covered: Inner Joins, Join Cardinality

A foreign key is a column whose values are the primary key of another table. orders.customer_id holds values from customers.customer_id, and orders.product_id holds values from products.product_id. Every ON clause in a reporting chain pairs a foreign key with the key it references, so the list of foreign keys is the first draft of the ON clauses, completed wherever the table being joined has a key of more than one column. The graph drew these links as edges; writing the SQL means reading them off the schema exactly, because an ON clause on the wrong pair of columns runs without an error and returns wrong rows.

Which way the key points

The foreign key always sits on the many side, so the column a hop joins from gives its direction: from orders.product_id to products is a many-to-one lookup, and from customers to orders.customer_id is one-to-many, the direction that can repeat rows.

SELECT c.region, p.category, SUM(o.amount) AS revenue
FROM orders o
JOIN customers c                      -- orders.customer_id references customers
  ON c.customer_id = o.customer_id    -- many-to-one: still 1 row per order
JOIN products p                       -- orders.product_id references products
  ON p.product_id = o.product_id      -- many-to-one, but 1003 finds no product
GROUP BY c.region, p.category;

SELECT c.region, SUM(o.amount) AS revenue
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
GROUP BY c.region

The animation plays the first hop on the 3 orders. Each order carries a customer_id, finds exactly 1 customers row, and picks up that customer's region, so 3 orders produce 3 joined rows: 1001 and 1003 for West, 1002 for East. The GROUP BY sums them per region, giving West 125 and East 120. The sum can be trusted because the hop was many-to-one: no order met 2 customers, so no amount was counted twice. In the full query above, the INNER hop to products would then drop order 1003 and its 85 from the category report.

Inferring keys the schema never declares

Interview schemas usually arrive as column lists in a shared doc, and warehouse tables often carry no enforced constraints, so the foreign keys have to be inferred. A column called customer_id in orders points, by its name, at the table named customers. The referenced column is the one that is unique in its own table. And both columns have to describe the same thing: orders.created_by and customers.customer_id can both be integers and still be unrelated, because created_by holds employee ids. When one of these signals is missing, ask: 'Is customer_id unique in customers, or does that table keep history rows?'

-- Is customer_id unique in the table I'm about to join?
SELECT customer_id, COUNT(*) AS rows_per_key
FROM customer_addresses
GROUP BY customer_id
HAVING COUNT(*) > 1;
-- customer_id = 1, rows_per_key = 2  (Ana: shipping and billing)

Match the whole key, never part of it

customer_addresses has no single-column key. Its key is the pair (customer_id, address_type): 1 row per customer per address type. An ON clause that matches only customer_id matches part of the key, and every address row for that customer comes along. Matching the full key, with the address type the question asks about, returns at most 1 row per customer, which is what the request's 'the city we ship to' means:

LEFT JOIN customer_addresses a
  ON  a.customer_id  = c.customer_id
  AND a.address_type = 'shipping'   -- completes the key: at most 1 match

USING (customer_id) produces the same matches as the explicit ON clause and saves a few characters, but it hides which table each side comes from and only works when both columns share a name. In an interview, write the explicit ON with both aliases, because that is the line your narration points at.

About this lesson

This section is part of the Multi-Table Joins: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.