Multi-table joins · SQL & Analytical Warehousing

Overview

JOIN aligns two tables on a shared key. INNER keeps matches; LEFT keeps all left rows.

On this page7 sections

1The table we keep using
2What we need from it
3Trace it step by step
4Same table, next cut
5Common beginner questions
6What comes next
7Practice

The table we keep using

Source data: orders

Three orders. O101 and O103 belong to the same user.

order_id	user_id	order_status	order_total
O101	U10	paid	150.00
O102	U11	paid	75.00
O103	U10	cancelled	200.00

Source data: order_items

Three line items. Order O101 has two items. Order O102 has none in this sample.

item_id	order_id	product_id	quantity	unit_price
I1	O101	P7	2	50.00
I2	O101	P9	1	50.00
I3	O103	P4	4	50.00

Real-world data is spread across multiple tables. Orders live in one table, payments in another, and customer details in a third. A JOIN combines rows from two tables when they share a matching key, letting you answer questions that span multiple tables.

Think of it like matching receipts: if order O0001 appears in both the orders table and the payments table, a JOIN connects those two pieces of information into one combined row. The column they share (order_id) is called the join key.

There are several types of joins. INNER JOIN keeps only rows that have a match in both tables. LEFT JOIN keeps every row from the left table, filling in NULL where no match exists on the right. RIGHT JOIN does the opposite. FULL OUTER JOIN keeps everything from both sides. CROSS JOIN pairs every row with every other row (rarely useful).

What we need from it

A product manager asks: 'Show me all orders with their payment status.' The order details are in the orders table, but the payment status is in the payments table. Without a JOIN, you cannot answer this question in a single query. Joins are how data engineers combine related facts into complete pictures.

The join type you choose determines which rows survive. Using INNER JOIN drops any order without a payment (potentially hiding problems). Using LEFT JOIN preserves all orders and shows NULL for unpaid ones (revealing the gaps). Choosing the wrong join type is one of the most common sources of incorrect analytics.

Equally important is understanding grain. If one order has three line items, joining orders to order_items produces three rows per order. Your order_total value appears three times, and summing it gives triple the real revenue. This is called fan-out, and it is the top join-related bug.

Trace it step by step

How a JOIN matches rows

The join key (order_id) connects one table to another. One order can match multiple items.

Same table, next cut

Example 1: INNER JOIN

INNER JOIN returns only rows where both tables have a matching key. Orders without items are dropped. Items without orders are dropped.

Run the example below in this tab. Read the input, follow the code, then check the output matches what you expect.

SQLMatch orders to their line items

SELECT
  o.order_id,
  o.order_status,
  i.product_id,
  i.quantity
FROM orders AS o
INNER JOIN order_items AS i
  ON o.order_id = i.order_id;

Result: O102 disappears because it has no matching items. O101 appears twice because it has two items.

order_id	order_status	product_id	quantity
O101	paid	P7	2
O101	paid	P9	1
O103	cancelled	P4	4

Notice that O102 is missing from the result. INNER JOIN only keeps matched pairs. Also notice O101 appears twice because it matched two item rows. This is fan-out in action.

Example 2: LEFT JOIN

LEFT JOIN keeps every row from the left table (orders), even if there is no match on the right (items). Unmatched right-side columns become NULL.

SQLKeep all orders, show payment status when available

SELECT
  o.order_id,
  o.order_status,
  p.status AS payment_status
FROM orders AS o
LEFT JOIN payments AS p
  ON o.order_id = p.order_id;

Result: O103 has no payment record, so payment_status shows NULL. It is still included.

order_id	order_status	payment_status
O101	paid	captured
O102	paid	captured
O103	cancelled	NULL

Example 3: Finding unmatched rows (anti-join)

A useful pattern: LEFT JOIN then filter for NULL on the right side. This finds rows in the left table that have no match in the right table.

SQLFind orders without any payment

SELECT
  o.order_id,
  o.order_status
FROM orders AS o
LEFT JOIN payments AS p
  ON o.order_id = p.order_id
WHERE p.payment_id IS NULL;

Result: only orders with no matching payment row appear.

order_id	order_status
O103	cancelled

Fan-out multiplies your data

Joining orders (one row per order) to order_items (multiple rows per order) gives you one row per item. If you SUM(order_total) after this join, you count the same total multiple times. Always check your row count after a join.

WHERE on a LEFT JOIN can turn it into INNER

If you LEFT JOIN payments and then write WHERE p.status = 'captured', rows where p.status is NULL (unmatched orders) are removed. This accidentally converts your LEFT JOIN into an INNER JOIN. Move the filter into the ON clause instead.

Always use table aliases

When two tables share column names (like order_id), you must qualify them: o.order_id, p.order_id. Short aliases like o and p keep queries readable.

Choose the join type based on which population you need to preserve.

Join Type	Keeps from left	Keeps from right	Use case
INNER JOIN	Only matched	Only matched	Show orders with their payments
LEFT JOIN	All rows	Only matched (NULL if none)	Find orders missing payments
RIGHT JOIN	Only matched (NULL if none)	All rows	Rarely used; reverse the tables and use LEFT
FULL OUTER JOIN	All rows	All rows	Reconcile two complete datasets
CROSS JOIN	All rows	All rows (every combination)	Generate all possible pairs (use carefully)

INNER vs LEFT: which rows survive?

INNER keeps the overlap. LEFT keeps the entire left circle.

Fixing fan-out: pre-aggregate before joining

If your target grain is one row per order, but the items table has multiple rows per order, aggregate the items first. Then join the summary.

SQLAggregate items first, then join at the order grain

WITH item_totals AS (
  SELECT
    order_id,
    SUM(quantity) AS total_units,
    SUM(quantity * unit_price) AS item_value
  FROM order_items
  GROUP BY order_id
)
SELECT
  o.order_id,
  o.order_total,
  t.total_units,
  t.item_value
FROM orders AS o
LEFT JOIN item_totals AS t
  ON o.order_id = t.order_id;

Result: one row per order. O102 has NULL because it had no items.

order_id	order_total	total_units	item_value
O101	150.00	3	150.00
O102	75.00	NULL	NULL
O103	200.00	4	200.00

Copy-paste without reading the output

Run Sample first. If the numbers or row count look wrong, stop and re-read the previous section before changing code.

Common beginner questions

When should I use LEFT JOIN vs INNER JOIN?

Use LEFT JOIN when you want to keep all rows from the main table even if some do not have a match. Use INNER JOIN when you only want rows that exist in both tables. If in doubt, start with LEFT JOIN so you can see what is missing.

Why do I get more rows after a JOIN?

This is fan-out. It happens when one row on the left matches multiple rows on the right. Check the relationship: if it is one-to-many, expect more rows. Pre-aggregate the many-side if you need one row per key.

Can I join more than two tables?

Yes. Chain additional JOINs: FROM orders o JOIN payments p ON ... JOIN customers c ON .... Each join adds columns from another table.

What comes next

Joins let you combine tables, but complex queries often need multiple steps. The next lesson introduces CTEs (Common Table Expressions): named intermediate results that make multi-step queries readable and debuggable.

Practice

Run Sample to left-join orders to payments. Then complete the exercise: INNER JOIN order_items to orders on order_id. Return order_id, product_id, quantity, and order_status. Limit to 20 rows.

Practicals · load into the editor

After you read the theory, run these in the pane on the right. They execute in this tab, no cluster.

Practice this

Same ideas as interview drills. These challenges open in the studio with a dataset and tests already set up.

Rate:

Was this useful?