Drawing the Join Graph First
Concepts covered: Multiple JOINs, Join Cardinality
The request names its output columns, and they live in tables that rarely sit next to each other. The name comes from customers, the category from products, the city from customer_addresses, and the only table that touches products is orders. A join graph puts that on the whiteboard before any SQL exists: every table is a node, every foreign key is an edge, and the query is a walk along those edges. Without the drawing, the join order follows the order the prompt listed the tables in, and a missing hop surfaces as an error or, worse, as a join on 2 columns that only look related.
The graph for the running question
- customers (source, 1 row per customer, key customer_id)
- orders (storage, 1 row per order, holds customer_id and product_id)
- products (storage, 1 row per product, key product_id)
- customer_addresses (storage, 1 row per customer per address_type)
- customers -> orders
- orders -> products
- customers -> customer_addresses
| Table | Rows used throughout this lesson |
|---|---|
| customers | Ana (id 1, West), Ben (id 2, East), Cara (id 3, West), Dev (id 4, East) |
| orders | Order 1001: Ana, product 10, $40. Order 1002: Ben, product 11, $120. Order 1003: Cara, product 13, $85 |
| products | Product 10 Mug (Kitchen), product 11 Lamp (Home), product 12 Kettle (Kitchen) |
| customer_addresses | Ana: shipping Portland, billing Seattle. Ben: shipping Boston. Cara: shipping Denver |
Products sits 2 hops from customers, which a list of table names hides, so no single ON clause connects them, and the route runs through orders.product_id. customer_addresses hangs off customers rather than orders, so its ON clause compares against c.customer_id, the column that is filled on every row of the result, including Dev's. And 2 edges leave customers toward tables that hold several rows per customer, which marks the 2 places the row count can grow.
Choose the anchor before the first JOIN
The anchor is the table whose rows must all reach the result, and it opens the FROM clause. The request names it, usually in a phrase about who has to appear: 'every customer', 'all products, even unsold ones', 'each order'. Here the anchor is customers, because the request keeps people who never bought anything. Say it out loud. An interviewer who hears 'customers is my anchor, since customers with no orders stay in' already expects the first hop to be a LEFT JOIN, and has watched you read the requirement before the schema.
| The request says | Anchor | Why |
|---|---|---|
| every customer, including ones who never ordered | customers | Customers with zero orders must survive every hop |
| revenue per order with its product category | orders | The grain is 1 order; customers and products only add columns |
| each product and how many times it sold | products | Unsold products need a row showing 0 |
| customers who placed an order last week | either side | The match defines the population, so INNER from either end gives the same rows |
Mark the many end of every edge
Each edge gets a 1 at one end and a many at the other, read from the keys. customers to orders is 1 to many: 1 customer, any number of orders. orders to products is many to 1: many orders can name the same product, and each order names exactly 1. customers to customer_addresses is 1 to many as well, since a customer can keep separate shipping and billing addresses. Walking an edge from its 1 end toward its many end is the hop that can repeat rows. Walking from the many end toward the 1 end never adds rows, though under INNER it can lose them.
The drawing also exposes the shape that causes the most damage: 2 one-to-many edges leaving the same node. If Ana had 2 orders and 2 addresses, joining both branches would give her 4 rows, every order paired with every address, because nothing in either ON clause relates an order to an address. The 2 edges fanning out of customers on the whiteboard are the cue to ask which address the question means before the address join is written.
If the graph has no route to a table the request needs, say that too. 'I don't see a link from returns to products; is there a product_id on return lines, or does it go through orders?' is a question interviewers plant on purpose, and asking it scores better than inventing a join on a column that happens to share a name.
- Typing the FROM clause first
- Join order follows the order the prompt listed the tables
- Join types default to habit, usually INNER
- Repeated rows appear in the output if anyone looks
- Rewrites start when the interviewer points at a row
- Drawing the graph first
- Join order walks the edges outward from the anchor
- Each hop's join type comes from the requirement
- Repeated rows are predicted from the many ends
- The interviewer sees the plan and can redirect early
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.