Lesson 4 of 6 in this module

8/33 in track

Multi-table joins

sql12 min

Overview

JOIN combines rows through a shared key. INNER keeps matches, LEFT keeps every left row, RIGHT keeps every right row, and FULL OUTER keeps rows from both sides.

On this page8 sections

The table we keep using

What do the 2 tables hold?

We reuse the same 8 orders as the last lesson, now with seven payment attempts.

orders_demo: 8 rows, 1 row per order, amounts in rupees.

order_iduser_idorder_statusorder_total
ORD-0001U01paid400
ORD-0002U02paid800
ORD-0003U01cancelled250
ORD-0004U03paid200
ORD-0005U02pending600
ORD-0006U03paid500
ORD-0007U04cancelled160
ORD-0008U01paid100

payments_demo: 7 rows. PAY-0009 points at ORD-0099, which is not in orders.

payment_idorder_idamountmethodstatus
PAY-0001ORD-0001400cardcaptured
PAY-0002ORD-0002800upicaptured
PAY-0004ORD-0004200walletcaptured
PAY-0005ORD-0005600cardfailed
PAY-0006ORD-0006500upicaptured
PAY-0008ORD-0008100cardcaptured
PAY-0009ORD-0099350upicaptured

Count the sides: 8 orders, 7 payments. 6 orders match a payment. ORD-0003 and ORD-0007 (cancelled) have none, and PAY-0009 has no order.

Why do orders and payments live in separate tables?

The orders table records what was bought. It cannot tell you whether the money reached your bank.

  1. orderswhat the customer bought
  2. order_idthe shared key
  3. paymentswhat the gateway did

The gateway sends its own data, which lands in a separate payments table. A join connects the two.

Can you build both tables yourself?

Yes. The real orders and payments tables are bigger, so we make small copies to trace by hand.

SQLTwo small tables for the whole lesson
CREATE OR REPLACE TABLE orders_demo (
  order_id VARCHAR, user_id VARCHAR, order_status VARCHAR, order_total DOUBLE
);
INSERT INTO orders_demo VALUES
  ('ORD-0001', 'U01', 'paid',      400), ('ORD-0002', 'U02', 'paid',      800),
  ('ORD-0003', 'U01', 'cancelled', 250), ('ORD-0004', 'U03', 'paid',      200),
  ('ORD-0005', 'U02', 'pending',   600), ('ORD-0006', 'U03', 'paid',      500),
  ('ORD-0007', 'U04', 'cancelled', 160), ('ORD-0008', 'U01', 'paid',      100);

CREATE OR REPLACE TABLE payments_demo (
  payment_id VARCHAR, order_id VARCHAR, amount DOUBLE, method VARCHAR, status VARCHAR
);
INSERT INTO payments_demo VALUES
  ('PAY-0001', 'ORD-0001', 400, 'card',   'captured'), ('PAY-0002', 'ORD-0002', 800, 'upi', 'captured'),
  ('PAY-0004', 'ORD-0004', 200, 'wallet', 'captured'), ('PAY-0005', 'ORD-0005', 600, 'card', 'failed'),
  ('PAY-0006', 'ORD-0006', 500, 'upi',    'captured'), ('PAY-0008', 'ORD-0008', 100, 'card', 'captured'),
  ('PAY-0009', 'ORD-0099', 350, 'upi',    'captured');

Words you will see in this lesson

Six words. The rest of the lesson uses only these.

WordWhat it means
JoinCombine rows from two tables
INNER JOINKeep only rows that match in both tables
LEFT JOINKeep every row from the left table
ONThe test that says which rows match
KeyThe shared column used to match rows
Fan-outA join that produces extra copies of rows

What we need from it

Which questions need a join?

Without a join you would export both tables and write loops to match order_id values by hand.

A join does the matching across millions of rows in a single query.

Business questionWhat the join does
Which orders were actually paid?Reconciles orders with payments
Which orders have no payment attempt?Isolates missing records
Which payments belong to no order?Finds orphans for manual audit
Who is each employee's manager?Relates rows inside one table

Trace it step by step

How does a join match rows?

The ON condition is the matching rule. Here it is p.order_id = o.order_id.

8 order rows on the left. Six have an arrow to a payment row on the right. ORD-0003 and ORD-0007 are red and point to an empty dashed box labelled no payment row.
Green arrows show key matches on order_id. The two red orders (ORD-0003 and ORD-0007) have no matching row in payments.

For each order, the engine looks for payments with the same order_id and glues the columns into one wider row.

How do the 4 join types differ?

Same tables, same ON condition. The only difference is what happens to unmatched rows.

INNER JOIN vs OUTER JOINS
INNER JOINOUTER JOINSvsKeeps only rows with matches on bothsidesReturns 6 rows hereDiscards ORD-0003, ORD-0007, andPAY-0009Use when you only care about completepairsLEFT: keeps all left rows (8 rows)RIGHT: keeps all right rows (7 rows)FULL: keeps everything from both sides(9 rows)Unmatched attributes become NULL

Same tables, same ON condition. The only difference is what happens to unmatched rows.

INNER JOIN: what survives?

INNER JOIN keeps only pairs where both sides match. Predict the row count.

SQLMatched pairs only
SELECT o.order_id, o.order_status, p.status AS pay_status, p.amount
FROM orders_demo o
INNER JOIN payments_demo p ON p.order_id = o.order_id
ORDER BY o.order_id;
order_id  order_status  pay_status  amount
ORD-0001  paid          captured    400
ORD-0002  paid          captured    800
ORD-0004  paid          captured    200
ORD-0005  pending       failed      600
ORD-0006  paid          captured    500
ORD-0008  paid          captured    100

6 rows. ORD-0005 is here because a payment attempt exists, even though it failed. ORD-0003, ORD-0007 and PAY-0009 are gone.

LEFT JOIN: how do you keep every order?

LEFT JOIN keeps every left row. Where the right side has no match, its columns become NULL. Predict pay_status for ORD-0003.

SQLAll 8 orders survive
SELECT o.order_id, o.order_status, p.status AS pay_status, p.amount
FROM orders_demo o
LEFT JOIN payments_demo p ON p.order_id = o.order_id
ORDER BY o.order_id;
order_id  order_status  pay_status  amount
ORD-0001  paid          captured    400
ORD-0002  paid          captured    800
ORD-0003  cancelled     NULL        NULL
ORD-0004  paid          captured    200
ORD-0005  pending       failed      600
ORD-0006  paid          captured    500
ORD-0007  cancelled     NULL        NULL
ORD-0008  paid          captured    100

All 8 orders return. The two cancelled orders show NULL, so you can audit orders that never reached payment.

LEFT JOIN: every left row survives. Missing matches become NULL.

RIGHT JOIN: how do you keep every payment?

RIGHT JOIN is the mirror image. Predict order_status for PAY-0009.

SQLAll 7 payments survive
SELECT p.payment_id, p.order_id AS pay_order_id, o.order_status, p.amount
FROM orders_demo o
RIGHT JOIN payments_demo p ON p.order_id = o.order_id
ORDER BY p.payment_id;
payment_id  pay_order_id  order_status  amount
PAY-0001    ORD-0001      paid          400
PAY-0002    ORD-0002      paid          800
PAY-0004    ORD-0004      paid          200
PAY-0005    ORD-0005      pending       600
PAY-0006    ORD-0006      paid          500
PAY-0008    ORD-0008      paid          100
PAY-0009    ORD-0099      NULL          350

PAY-0009 shows NULL: money was captured but no order row exists. Many teams swap the tables and use a LEFT JOIN instead.

FULL OUTER JOIN: can you see both sides at once?

When you reconcile ledgers, you want matched pairs, orders without payments and payments without orders. Predict: 6 + 2 + 1 rows.

SQLReconcile orders and payments
SELECT
  COALESCE(o.order_id, p.order_id) AS reconciled_order_id,
  o.order_status,
  p.payment_id,
  p.status AS payment_status,
  p.amount
FROM orders_demo o
FULL OUTER JOIN payments_demo p ON p.order_id = o.order_id
ORDER BY reconciled_order_id;
reconciled_order_id  order_status  payment_id  payment_status  amount
ORD-0001             paid          PAY-0001    captured        400
ORD-0002             paid          PAY-0002    captured        800
ORD-0003             cancelled     NULL        NULL            NULL
ORD-0004             paid          PAY-0004    captured        200
ORD-0005             pending       PAY-0005    failed          600
ORD-0006             paid          PAY-0006    captured        500
ORD-0007             cancelled     NULL        NULL            NULL
ORD-0008             paid          PAY-0008    captured        100
ORD-0099             NULL          PAY-0009    captured        350

9 rows. COALESCE picks whichever order_id exists, so ORD-0099 still gets a name.

Does it matter if the filter sits in ON or WHERE?

Yes, and this is a classic bug. First, the filter in ON: it only limits which payments can match.

SQLFilter in ON: all 8 orders survive
SELECT o.order_id, o.order_status, p.status AS pay_status, p.amount
FROM orders_demo o
LEFT JOIN payments_demo p
  ON p.order_id = o.order_id
 AND p.status = 'captured'
ORDER BY o.order_id;
order_id  order_status  pay_status  amount
ORD-0001  paid          captured    400
ORD-0002  paid          captured    800
ORD-0003  cancelled     NULL        NULL
ORD-0004  paid          captured    200
ORD-0005  pending       NULL        NULL
ORD-0006  paid          captured    500
ORD-0007  cancelled     NULL        NULL
ORD-0008  paid          captured    100

8 rows. ORD-0005 kept its place, but its failed payment no longer matched, so its payment columns are NULL.

What happens when the same filter moves to WHERE?

Now WHERE runs after the join. Predict how many rows remain.

SQLFilter in WHERE
SELECT o.order_id, o.order_status, p.status AS pay_status, p.amount
FROM orders_demo o
LEFT JOIN payments_demo p ON p.order_id = o.order_id
WHERE p.status = 'captured'
ORDER BY o.order_id;
order_id  order_status  pay_status  amount
ORD-0001  paid          captured    400
ORD-0002  paid          captured    800
ORD-0004  paid          captured    200
ORD-0006  paid          captured    500
ORD-0008  paid          captured    100

8 rows shrank to five. NULL = 'captured' is UNKNOWN, so WHERE dropped the unmatched rows and the failed one. The LEFT JOIN quietly became an INNER JOIN.

A WHERE filter on the right table turns a LEFT JOIN into an INNER JOIN.

How do you ask 'has a payment' without duplicating rows?

EXISTS tests for a match and returns no columns from the other table. That is a semi-join.

SQLOrders with a captured payment
SELECT o.order_id, o.order_total
FROM orders_demo o
WHERE EXISTS (
  SELECT 1 FROM payments_demo p
  WHERE p.order_id = o.order_id
    AND p.status = 'captured'
)
ORDER BY o.order_id;
order_id  order_total
ORD-0001  400
ORD-0002  800
ORD-0004  200
ORD-0006  500
ORD-0008  100

5 orders, still 1 row per order.

How do you find orders with no payment at all?

NOT EXISTS is the anti-join. Predict which orders it finds.

SQLOrders with zero payment attempts
SELECT o.order_id, o.order_status
FROM orders_demo o
WHERE NOT EXISTS (
  SELECT 1 FROM payments_demo p
  WHERE p.order_id = o.order_id
)
ORDER BY o.order_id;
order_id  order_status
ORD-0003  cancelled
ORD-0007  cancelled

The two cancelled orders never reached the gateway.

Watch outWhy NOT IN breaks when the subquery contains NULL

Beginners often write WHERE order_id NOT IN (SELECT order_id FROM payments). If even a single row in payments has order_id = NULL, SQL evaluates NOT IN as: order_id <> 'ORD-0001' AND order_id <> NULL. In three-valued logic, any comparison with NULL evaluates to UNKNOWN. True AND UNKNOWN evaluates to UNKNOWN, which the WHERE filter rejects. Your query returns 0 rows. Prefer NOT EXISTS for anti-joins. It does not have this problem.

Same table, next cut

What is fan-out?

An order can have many line items. Joining orders to items repeats each order once per item. Here are the items.

order_items_demo: 5 rows. ORD-0001 has 1 item, ORD-0002 and ORD-0006 have 2.

order_idproduct_id
ORD-0001SKU-017
ORD-0002SKU-004
ORD-0002SKU-015
ORD-0006SKU-001
ORD-0006SKU-012
SQLLine items for three of the orders
CREATE OR REPLACE TABLE order_items_demo (order_id VARCHAR, product_id VARCHAR);
INSERT INTO order_items_demo VALUES
  ('ORD-0001', 'SKU-017'), ('ORD-0002', 'SKU-004'), ('ORD-0002', 'SKU-015'),
  ('ORD-0006', 'SKU-001'), ('ORD-0006', 'SKU-012');

Predict the sum of order_total after the join. The true total is 3010.

SQLSum after joining to items
SELECT SUM(order_total) AS gmv_after_join
FROM orders_demo o
LEFT JOIN order_items_demo i ON i.order_id = o.order_id;
gmv_after_join
4310

The join made 10 rows from 8 orders, because ORD-0002 and ORD-0006 each appear twice. Revenue is overstated by 1300.

Joining to a one-to-many table repeats the parent rows and inflates totals.

How do you join 3 tables?

Chain the joins. Orders connect to payments and to items.

SQLOrders, payments and items in one query
SELECT
  o.order_id,
  o.order_status,
  COUNT(i.product_id) AS item_count,
  p.status AS payment_status
FROM orders_demo o
LEFT JOIN payments_demo p ON p.order_id = o.order_id
LEFT JOIN order_items_demo i ON i.order_id = o.order_id
GROUP BY o.order_id, o.order_status, p.status
ORDER BY o.order_id;
order_id  order_status  item_count  payment_status
ORD-0001  paid          1           captured
ORD-0002  paid          2           captured
ORD-0003  cancelled     0           NULL
ORD-0004  paid          0           captured
ORD-0005  pending       0           failed
ORD-0006  paid          2           captured
ORD-0007  cancelled     0           NULL
ORD-0008  paid          0           captured

1 row per order again, because GROUP BY collapsed the repeated item rows. Each order has at most one payment, so nothing is double counted here.

What is a self-join?

A table can join itself. Each employee row names a manager_id that points at another employee in the same table.

SQLMap each person to their manager
CREATE OR REPLACE TABLE employees_demo (
  employee_id INTEGER, name VARCHAR, department VARCHAR, manager_id INTEGER
);
INSERT INTO employees_demo VALUES
  (1, 'Aarav', 'Engineering', NULL), (2, 'Diya', 'Engineering', 1),
  (3, 'Kabir', 'Engineering', 1),    (4, 'Neha', 'Analytics', NULL),
  (5, 'Rohan', 'Analytics', 4);

SELECT
  e.name AS employee_name,
  e.department,
  COALESCE(m.name, 'Top Leader') AS manager_name
FROM employees_demo e
LEFT JOIN employees_demo m ON e.manager_id = m.employee_id
ORDER BY e.employee_id;
employee_name  department   manager_name
Aarav          Engineering  Top Leader
Diya           Engineering  Aarav
Kabir          Engineering  Aarav
Neha           Analytics    Top Leader
Rohan          Analytics    Neha

Same table, two aliases: e is the employee, m is the manager. The LEFT JOIN keeps the top leaders, whose manager_id is NULL.

What does CROSS JOIN do?

CROSS JOIN pairs every row of one table with every row of the other, with no ON. 3 sizes times 2 colours is 6 rows.

SQLEvery size with every colour
CREATE OR REPLACE TABLE sizes_demo (size VARCHAR);
INSERT INTO sizes_demo VALUES ('S'), ('M'), ('L');
CREATE OR REPLACE TABLE colors_demo (color VARCHAR);
INSERT INTO colors_demo VALUES ('Red'), ('Blue');

SELECT s.size, c.color
FROM sizes_demo s
CROSS JOIN colors_demo c
ORDER BY s.size, c.color;
size  color
L     Blue
L     Red
M     Blue
M     Red
S     Blue
S     Red

That is a good use: a full catalogue of combinations.

Watch outThe row explosion risk of accidental CROSS JOINs

If you omit the ON clause on a large table or create an unconstrained join, you trigger a cartesian explosion. Joining a 100,000-row customer table to a 100,000-row orders table without a key produces 10,000,000,000 rows. A result that size can run for a very long time, fill memory or disk, or run up a large bill, depending on the engine.

What happens when both sides repeat the key?

ORD-0001 has two shipping tags and two promo codes. Join the two child tables on order_id. Predict the row count.

SQLJoining two child tables on order_id
CREATE OR REPLACE TABLE order_tags_demo (order_id VARCHAR, tag VARCHAR);
INSERT INTO order_tags_demo VALUES ('ORD-0001', 'express'), ('ORD-0001', 'fragile');
CREATE OR REPLACE TABLE order_promos_demo (order_id VARCHAR, promo_code VARCHAR);
INSERT INTO order_promos_demo VALUES ('ORD-0001', 'FESTIVE10'), ('ORD-0001', 'FREESHIP');

SELECT t.order_id, t.tag, pr.promo_code
FROM order_tags_demo t
JOIN order_promos_demo pr ON t.order_id = pr.order_id
ORDER BY t.order_id, t.tag, pr.promo_code;
order_id  tag      promo_code
ORD-0001  express  FESTIVE10
ORD-0001  express  FREESHIP
ORD-0001  fragile  FESTIVE10
ORD-0001  fragile  FREESHIP

2 times 2 is 4 rows. With 5 tags and 4 promos it would be 20, and any metric on the result is multiplied. Aggregate each child table first, then join.

What is USING, and why avoid NATURAL JOIN?

When both tables name the key the same, USING (order_id) replaces ON. It keeps one order_id column in the output.

SQLJOIN USING on a shared key
SELECT order_id, o.order_status, p.amount
FROM orders_demo o
JOIN payments_demo p USING (order_id)
ORDER BY order_id;
order_id  order_status  amount
ORD-0001  paid          400
ORD-0002  paid          800
ORD-0004  paid          200
ORD-0005  pending       600
ORD-0006  paid          500
ORD-0008  paid          100

NATURAL JOIN joins on every shared column name automatically. That is brittle. Watch what a new shared updated_at column does.

SQLA silent second join column
SELECT *
FROM (VALUES ('ORD-0001', TIMESTAMP '2026-08-01 10:00:00')) a(order_id, updated_at)
NATURAL JOIN
     (VALUES ('ORD-0001', TIMESTAMP '2026-08-01 10:00:05')) b(order_id, updated_at);
order_id  updated_at
(0 rows)

Same order_id, but the timestamps differ by 5 seconds, so nothing matches and no error appears. Always write ON or USING.

Does it work on the real orders and payments?

Yes. Same pattern, real tables. Count how many orders find a payment.

SQLLEFT JOIN on 180 orders
SELECT COUNT(*)             AS orders,
       COUNT(p.payment_id)  AS with_payment
FROM orders o
LEFT JOIN payments p ON p.order_id = o.order_id;
orders  with_payment
180     150

All 180 orders survive. 150 have a payment. The 30 without are the cancelled orders.

How does one join question run, start to finish?

Orders and payments, end to end
  1. Question: which orders were paid, and which never reached the gateway?

  2. Both tables share order_id, so ON p.order_id = o.order_id matches rows.

  3. LEFT JOIN keeps all 8 orders. ORD-0003 and ORD-0007 get NULL payment columns.

  4. A WHERE on the payment status would drop them, so the filter goes in ON.

  5. NOT EXISTS lists the 2 orders with no payment at all.

Keep these.

  1. A join matches rows on a key with ON. The join type decides what happens to unmatched rows.
  2. LEFT JOIN keeps all left rows. Unmatched right columns become NULL.
  3. A WHERE filter on the right table turns a LEFT JOIN into an INNER JOIN.
  4. Joining to a one-to-many table repeats rows. Aggregate before you sum.
  5. Prefer NOT EXISTS over NOT IN, and ON or USING over NATURAL JOIN.

Common beginner questions

Which table should be on the left of a LEFT JOIN?

Place the primary table representing your target population on the left. If you are reporting on customer orders, put orders on the left and attach payments, shipments, or discounts on the right with LEFT JOIN.

Why do column names appear twice when I write SELECT * on a join?

Because SELECT * returns all columns from all joined tables. Both orders and payments contain an order_id column, causing a duplicated column in the output. Always specify exact column names with table prefixes, like o.order_id, p.payment_id, or use USING (order_id).

What happens when join keys contain NULL on both sides?

In SQL, NULL represents an unknown value. The expression NULL = NULL evaluates to UNKNOWN, not TRUE. Therefore, rows with NULL in their join keys will never match each other.

How do you explain fan-out during a technical interview?

Explain that fan-out occurs when joining 2 tables on a non-unique foreign key in a one-to-many or many-to-many relationship. The query engine matches each left record to every matching right record, multiplying rows. Aggregating parent-grain measures on the post-join relation leads to erroneous inflated metrics.

What comes next

When queries combine multi-table joins, aggregations, and complex business filters, stacking everything into one monolithic SELECT block makes SQL difficult to read and debug.

In the next lesson on Subqueries and CTEs, you will learn how Common Table Expressions break complex multi-step pipelines into clean, named intermediate stages.

Practice

In the workspace exercise, you will join orders and payments to inspect payment outcomes. Run the query with a LEFT JOIN to see all orders survive, return the required order and payment columns, and confirm that unmatched orders display NULL for payment attributes.

Going further: two child tables and the doubled total

The classic join disaster appears when you join one parent to two child tables at once. Order 1 has 3 items and 2 payments. Joining both multiplies the rows: 3 times 2 is 6 rows for one order. Predict whether the total revenue from the joined rows will be 160.

True revenue is 160.

TableRows
ordersorder 1 total 120, order 2 total 40
itemsorder 1: A, B, C. order 2: A
paymentsorder 1: 60, 60. order 2: 40
SQLThe wrong way
SELECT SUM(o.total) AS revenue
FROM orders o
JOIN items i    ON i.order_id = o.order_id
JOIN payments p ON p.order_id = o.order_id
revenue
760

The result is 760, almost 5 times the true 160. Order 1's total of 120 appeared 6 times (3 items x 2 payments), which is 720, plus order 2's 40 once. SQL did exactly what the join asked: 1 row for every pairing.

The fix is to give each child the same grain as the parent before joining: 1 row per order.

SQLAggregate each child first
WITH i AS (SELECT order_id, COUNT(*) AS items FROM items GROUP BY order_id),
     p AS (SELECT order_id, SUM(paid) AS paid FROM payments GROUP BY order_id)
SELECT SUM(o.total) AS revenue, SUM(i.items) AS items, SUM(p.paid) AS paid
FROM orders o
JOIN i ON i.order_id = o.order_id
JOIN p ON p.order_id = o.order_id
revenue  items  paid
160      4      160

Now revenue is 160, items is 4 and paid is 160. A quick check you can always run: the row count after a join should equal the parent's row count when every child side is 1 row per key.

Try a new scenario

You join customers to orders and want the number of customers who ordered. COUNT(*) gives 5, but there are 3 customers who ordered. What went wrong, and what do you count?

Work it out, then open the answer

Each customer appears once per order, so counting rows counts orders. Count COUNT(DISTINCT customer_id) instead, or aggregate orders to 1 row per customer first. Counting distinct hides fan-out, so it is fine for a count, but a SUM of a customer-level column would still be inflated.

Interview: what is the difference between INNER and LEFT JOIN?

INNER keeps only rows that match on both sides. LEFT keeps every row from the left table and fills the right side with NULL when there is no match. Use LEFT when missing matches are part of the answer, such as customers with no orders.

Close the lesson and explain it

Without scrolling up, explain how joining two child tables inflates a total, and how to prevent it.

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.

Finished this lesson?

Mark it as read when you could explain it to someone else.

Practice this idea

Each page shows the idea moving, then lists problems from easiest. Open one now, or come back when you finish the lesson.

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?