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_id | user_id | order_status | order_total |
|---|---|---|---|
| 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 |
payments_demo: 7 rows. PAY-0009 points at ORD-0099, which is not in orders.
| payment_id | order_id | amount | method | status |
|---|---|---|---|---|
| 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 |
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.
- orderswhat the customer bought
- order_idthe shared key
- 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.
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.
| Word | What it means |
|---|---|
| Join | Combine rows from two tables |
| INNER JOIN | Keep only rows that match in both tables |
| LEFT JOIN | Keep every row from the left table |
| ON | The test that says which rows match |
| Key | The shared column used to match rows |
| Fan-out | A 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 question | What 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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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_id | product_id |
|---|---|
| ORD-0001 | SKU-017 |
| ORD-0002 | SKU-004 |
| ORD-0002 | SKU-015 |
| ORD-0006 | SKU-001 |
| ORD-0006 | SKU-012 |
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.
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.
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.
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.
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.
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.
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.
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.
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.
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?
Question: which orders were paid, and which never reached the gateway?
Both tables share order_id, so ON p.order_id = o.order_id matches rows.
LEFT JOIN keeps all 8 orders. ORD-0003 and ORD-0007 get NULL payment columns.
A WHERE on the payment status would drop them, so the filter goes in ON.
NOT EXISTS lists the 2 orders with no payment at all.
Keep these.
- A join matches rows on a key with ON. The join type decides what happens to unmatched rows.
- LEFT JOIN keeps all left rows. Unmatched right columns become NULL.
- A WHERE filter on the right table turns a LEFT JOIN into an INNER JOIN.
- Joining to a one-to-many table repeats rows. Aggregate before you sum.
- 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.
| Table | Rows |
|---|---|
| orders | order 1 total 120, order 2 total 40 |
| items | order 1: A, B, C. order 2: A |
| payments | order 1: 60, 60. order 2: 40 |
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_idrevenue 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.
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_idrevenue 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.
- Customers who never orderedInterview-style drill: Anti-join customers with no rows in lb_orders.Studiobeginnersql8 min
- Employee department namesInterview-style drill: Inner join employees to departments.Studiobeginnersql8 min
- Employees earning more than their managerInterview-style drill: Find employees paid more than the person they report to.Studiobeginnersql8 min