INNER vs LEFT at Each Hop

Concepts covered: Inner Joins, Outer Joins, Multiple JOINs

A chain of joins reads left to right. customers LEFT JOIN orders LEFT JOIN products means customers joined to orders, then that whole result joined to products, so the second hop's left side is everything the first hop produced. Each hop therefore has its own question: when a row arriving from the left finds no partner in this table, does it still belong in the answer? INNER JOIN says no and drops the row. LEFT JOIN says yes and keeps it with NULL in every column of this table. The requirement answers the question hop by hop, and the answers do not have to agree.

The middle row is the hop most answers never examine. Cara bought product 13 before it was delisted, which happens in real catalogs every week, and an INNER JOIN to products removes her order from a result that promised every order. Keeping or dropping it is a requirement question with a real answer, so ask it: 'Should orders for delisted products show with a blank category, or drop out?' Asking at this hop shows the interviewer you checked what a missing match means here, instead of applying a single join type to the whole chain.

The hop that cancels the LEFT before it

The most common chain bug is a LEFT JOIN at the first hop and a plain JOIN at the next. The first hop keeps Dev with NULL in every orders column, o.product_id included. The second hop then tests p.product_id = o.product_id on Dev's row, which becomes p.product_id = NULL for every product. A comparison with NULL evaluates to unknown, an INNER JOIN keeps only pairs whose condition is true, and Dev's row is gone. The LEFT at hop 1 did its job, and hop 2 reversed it.

SELECT co.name, co.order_id, p.category FROM customer_orders co
JOIN products p ON p.product_id = co.product_id

The animation starts where hop 1 left off: customer_orders is what the LEFT JOIN produced for Ana and Dev, with Dev's order columns all NULL. Hop 2 returns 1 row. Ana survives because order 1001 points at product 10, which exists. Dev drops: his NULL product_id matches nothing. Cara's order for product 13 drops at the same hop for another reason, because no products row has id 13. Across all 4 customers only Ana and Ben survive: the query contains a LEFT JOIN, reads as if it keeps every customer, and keeps half of them.

-- Both hops LEFT: the preserved rows stay preserved
SELECT c.name, o.order_id, p.category
FROM customers c
LEFT JOIN orders   o ON o.customer_id = c.customer_id
LEFT JOIN products p ON p.product_id  = o.product_id;

The result has 4 rows, 1 per customer. Its 2 NULL categories say different things: Cara's means her product is unknown to the catalog, Dev's means he has no order at all. The rule that falls out is that once a hop preserves rows with LEFT, every later hop whose ON clause reads columns from the preserved side must be LEFT as well, or it filters those rows back out. The exception is deliberate. If the requirement is 'priced orders with their customers', the anchor moves to orders and both hops become INNER. Losing Dev and Cara's order is then the intended result.

When INNER is the right choice

INNER JOIN is correct whenever the match defines who belongs. 'Customers who have placed an order', 'orders with a valid product', 'products that sold this month': in each, a row with no partner sits outside the question, and keeping it with NULLs puts rows in the answer the asker did not want. INNER also documents intent for the next reader of the query: this table is required. Interviewers give full credit for either join type when you chose it and can say why; they take points off for a join type nobody chose.

INNER at this hop

• A missing partner puts the row out of scope
• Rows can only drop or repeat, never arrive unmatched
• Every later hop sees only matched rows
• Cue words: 'who have', 'with a', 'that sold'

LEFT at this hop

• A missing partner means the attribute is absent or unknown
• Every left row survives at least once, NULLs filling the gap
• Later hops reading its columns must stay LEFT
• Cue words: 'every', 'including', 'even if none'

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.