Talking Through Each Join Hop
Concepts covered: Multiple JOINs, Join Cardinality, Outer Joins
A multi-table join written in silence forces the interviewer to reverse-engineer it, and most stop trying around the third ON clause. Narration lets them grade each decision as you make it and gives them a place to step in before a wrong hop eats the remaining minutes. The format that works is the same 4 beats for every hop, said while you write that line.
- The table this hop adds, and the key it joins on
- Which way the key points: many-to-one holds the count, one-to-many can repeat rows
- INNER or LEFT, and the requirement that decided it
- The row count after the hop, worked out from the count before it
Open with the grain and the anchor before you write the FROM line: 'The answer has a row for every order and a row for each customer with no orders. Customers is my anchor.' That sentence tells the interviewer you know what a correct result looks like before any SQL exists, and every later beat refers back to it.
The running query, narrated
| Line you write | What you say |
|---|---|
| FROM customers c | Customers is the anchor, since every customer appears. Four rows to start. |
| LEFT JOIN orders o ON o.customer_id = c.customer_id | Orders references customers by customer_id, so walking from customers is one-to-many: a customer with 3 orders becomes 3 rows. LEFT, because customers with no orders stay. Here each has 1 order or none, so still 4. |
| LEFT JOIN products p ON p.product_id = o.product_id | Orders points at products, many-to-one, so this hop can't add rows. LEFT, because order 1003 references a delisted product and Dev's row has a NULL product_id; INNER would drop both. Still 4. |
| LEFT JOIN customer_addresses a ON a.customer_id = c.customer_id AND a.address_type = 'shipping' | Addresses are keyed by customer and type, so I match both and get at most 1 row. LEFT, because Dev has no address. Still 4: the 3 orders and Dev. |
The narration leaves the syntax out. Nobody needs to hear 'and now I LEFT JOIN products on product_id'; the interviewer can read that off the screen. What they cannot read is the reason, and the reason is the part being scored. A candidate who gives the reason before the interviewer asks for it has answered the follow-up in advance. Each spoken line runs roughly 15 seconds, so a 3-hop chain costs under a minute of the round.
- Narrating the syntax
- 'Now I join products on product_id'
- 'Then a LEFT JOIN to addresses'
- Reasons come out only when the interviewer asks
- The row count is discovered by running the query
- Narrating the decisions
- 'Orders points at products, so this hop can't add rows'
- 'LEFT, because Dev has no address and still belongs'
- Every hop arrives with its reason attached
- The row count is stated before the query runs
The follow-ups, and answers that score
| Interviewer asks | Answer that scores |
|---|---|
| What happens to a customer with no orders? | Dev passes every hop with NULLs in the orders, products and address columns, because all 3 hops are LEFT. He gets 1 row. |
| Why not INNER on products? | Dev's NULL product_id would match nothing and drop him, and Cara's order for product 13 would vanish. The request asked for both. |
| How many rows on the real tables? | The number of orders that belong to a customer, plus the number of customers with none, as long as the address hop matches the full key. I'd confirm with COUNT(*) against COUNT(DISTINCT order_id). |
| What if an order has a NULL customer_id? | It never appears, because the chain starts at customers and a NULL key matches nothing. If those orders matter, the anchor is wrong, and I'd ask before changing it. |
| A customer has 2 shipping addresses. Now what? | Then customer_id and address_type together are not a key, the hop repeats that customer's orders, and I need a rule from you for which address wins. |
The last follow-up separates a memorized query from an understood one. A candidate who knows why the address hop is safe can name the assumption it rests on, and asks for the missing rule instead of patching the result. The patch interviewers watch for is SELECT DISTINCT over the repeated rows: it hides the repeat without deciding which address is correct, and when the 2 addresses differ in city it does not even remove the duplicate order.
- Do
- Draw the join graph and name the anchor table before writing FROM
- Decide INNER or LEFT per hop, from the wording of the requirement
- Keep every hop after a LEFT as LEFT unless dropping those rows is intended
- Match the full key of the table each hop joins to
- Predict the row count after each hop and check it with COUNT(*)
- Avoid
- Don't join on columns that only share a name, like 2 tables' id
- Don't put an INNER JOIN after a LEFT on columns from the preserved side
- Don't patch repeated rows with SELECT DISTINCT before finding the hop
- Don't narrate syntax; narrate the key and join type of each hop, then its row count
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.