Lesson 6 • Intermediate Track
JOIN Operations
By the end of this lesson you'll be able to combine data that's split across two tables — pairing customers with their orders, keeping unmatched rows when you need them, and understanding exactly where those NULLs come from. JOINs are the skill that turns isolated tables into real answers.
What You'll Learn
- ✓Why data is split across tables and needs joining
- ✓Match rows with INNER JOIN and an ON condition
- ✓Keep every left-side row with LEFT JOIN
- ✓Mirror it with RIGHT JOIN and keep everything with FULL OUTER JOIN
- ✓Use table aliases (c, o) to keep queries readable
- ✓Understand why unmatched rows produce NULLs
SELECT, WHERE, and reading a result set. The in-browser editor lets you write and edit SQL; to run it, copy your query into a free playground like sqliteonline.com or db-fiddle.com. Every example below shows the expected result so you can check yourself.Our Two Sample Tables
JOINs always involve two or more tables, so this lesson uses two. Every query below runs against these exact rows — keep them in mind as you read.
customers — who they are
Result:
| id | name | city |
|---|---|---|
| 1 | Ada Lovelace | London |
| 2 | Grace Hopper | New York |
| 3 | Alan Turing | Manchester |
| 4 | Linus Torvalds | Portland |
| 5 | Marie Curie | Paris |
orders — what they bought (note customer_id links back to customers.id)
Result:
| id | customer_id | product | amount |
|---|---|---|---|
| 101 | 1 | Keyboard | 75 |
| 102 | 2 | Monitor | 199.99 |
| 103 | 1 | Mouse | 24.5 |
| 104 | 3 | Desk | 120 |
| 105 | 99 | Webcam | 45 |
Two details that make the examples interesting: Marie Curie (id 5) has no orders at all, and order #105 points at customer_id 99, who isn't in the customers table. Watch how each JOIN type treats these two "loose ends".
1. Why You Need a JOIN
Databases deliberately split data across tables to avoid repeating themselves. A customer's name lives once in customers; each order just stores that customer's id instead of copying the whole name. That keeps data tidy — but it means the orders table alone can't tell you who placed an order.
A JOIN stitches the tables back together for the duration of one query, matching rows by a shared value (here, the customer id). Nothing is changed on disk — the combined table exists only in the result.
🧾 Real-world analogy
Imagine two stacks of paper on your desk: a list of customers (each with an account number) and a pile of receipts (each stamped with the account number, but no name). To bill people you walk down the receipts and, for each one, find the matching customer by account number. A JOIN is that matching done automatically — the ON condition is the rule "match where the account numbers are equal".
The problem JOINs solve
orders stores a customer_id, not a name.
-- The data you want lives in TWO tables.
-- The orders table only stores customer_id — not the name.
SELECT * FROM orders;
-- To show "Ada Lovelace bought a Keyboard for 75.00"
-- you must connect orders to customers on the shared id.
-- That connection is a JOIN.Result — orders on its own — no names!:
| id | customer_id | product | amount |
|---|---|---|---|
| 101 | 1 | Keyboard | 75 |
| 102 | 2 | Monitor | 199.99 |
| … | … | … | … |
2. INNER JOIN — Only the Matches
An INNER JOIN returns only the rows that have a match in both tables. Each orders row is paired with the customers row whose id equals the order's customer_id. Rows without a partner on either side are dropped.
Two pieces of new syntax do the work. Table aliases (FROM customers c, JOIN orders o) give each table a short nickname so you can write c.name and o.product instead of the full table name. The ON condition states the matching rule — usually one table's primary key = another table's foreign key.
INNER JOIN
Pair each order with its customer; drop unmatched rows.
-- INNER JOIN: keep only rows that MATCH in both tables
SELECT
c.name, -- from customers (aliased c)
o.product, -- from orders (aliased o)
o.amount
FROM customers c
INNER JOIN orders o
ON c.id = o.customer_id; -- the matching rule
-- c and o are table aliases — short nicknames so you
-- don't retype "customers"/"orders" on every column.
-- ON says HOW the two tables line up: a customer row
-- pairs with an order row when their ids are equal.Result — 4 matched rows — Marie Curie and orphan order #105 are both excluded:
| name | product | amount |
|---|---|---|
| Ada Lovelace | Keyboard | 75 |
| Grace Hopper | Monitor | 199.99 |
| Ada Lovelace | Mouse | 24.5 |
| Alan Turing | Desk | 120 |
Your Turn: complete the INNER JOIN
Fill in the blanks to show each buyer's city next to the product they ordered. The expected result is in the comments so you can check yourself.
🎯 Your Turn: INNER JOIN
Pick the join type and the ON column.
-- 🎯 YOUR TURN — fill in the blanks, then press "Try it Yourself"
-- Goal: show each customer's city next to the product they bought.
SELECT c.name, c.city, o.product
FROM customers c
___ JOIN orders o -- 👉 the join type that keeps only matches
ON c.id = ___; -- 👉 the orders column that points back to a customer
-- ✅ Expected result: 5 matched rows (only customers WITH orders),
-- e.g. Ada Lovelace | London | Keyboard , Grace Hopper | New York | Monitor , ...3. LEFT JOIN — Keep Every Left-Side Row
A LEFT JOIN keeps every row from the left table (the one in FROM), whether or not it finds a match on the right. Where there's no match, the right-side columns come back as NULL — SQL's marker for "no value / unknown".
This is exactly what you want when "missing" is meaningful. Marie Curie has never ordered, so an INNER JOIN would hide her — but a LEFT JOIN keeps her, with NULL where her order details would be.
customers LEFT JOIN orders keeps all customers; flip the tables and it keeps all orders.LEFT JOIN
Keep all customers, even the one with no orders.
-- LEFT JOIN: keep ALL rows from the LEFT table (customers),
-- and attach order data where it exists. No match -> NULLs.
SELECT
c.name,
o.product,
o.amount
FROM customers c
LEFT JOIN orders o
ON c.id = o.customer_id;
-- Marie Curie has placed no orders, so her row still appears
-- but product and amount come back as NULL (empty / unknown).Result — 6 rows — note Marie Curie's NULLs:
| name | product | amount |
|---|---|---|
| Ada Lovelace | Keyboard | 75 |
| Ada Lovelace | Mouse | 24.5 |
| Grace Hopper | Monitor | 199.99 |
| Alan Turing | Desk | 120 |
| Linus Torvalds | NULL | NULL |
| Marie Curie | NULL | NULL |
A classic follow-up is to isolate just the gaps — the customers with no orders — by keeping only the rows where the matched order is NULL:
LEFT JOIN + IS NULL — find the gaps
Customers who have never ordered anything.
-- A classic use of LEFT JOIN: find the UNMATCHED rows.
-- "Which customers have never ordered anything?"
SELECT c.name, c.city
FROM customers c
LEFT JOIN orders o
ON c.id = o.customer_id
WHERE o.id IS NULL; -- keep only rows where no order matched
-- IS NULL is the filter that isolates the gaps a LEFT JOIN reveals.Result — 2 rows — only customers with no matching order:
| name | city |
|---|---|
| Linus Torvalds | Portland |
| Marie Curie | Paris |
Your Turn: complete the LEFT JOIN
Fill in the blanks so the result lists every customer's order amount — including the customer who has never ordered (their amount should be NULL).
🎯 Your Turn: LEFT JOIN
Choose the join type that keeps all customers.
-- 🎯 YOUR TURN — fill in the blanks.
-- Goal: list EVERY customer and their order amount —
-- including the customer who has never ordered (amount = NULL).
SELECT c.name, o.amount
FROM customers c
___ JOIN orders o -- 👉 the join type that keeps ALL customers
ON c.id = o.customer_id;
-- ✅ Expected result: 6 rows — all 5 customers (one appears twice
-- because they have 2 orders) PLUS Marie Curie with amount = NULL.4. RIGHT JOIN — The Mirror Image
A RIGHT JOIN is a LEFT JOIN seen in a mirror: it keeps every row from the right table and fills the left side with NULL when there's no match. With customers RIGHT JOIN orders, every order survives — including the orphan order #105 whose customer_id 99 matches nobody, so its name is NULL.
LEFT JOIN and simply put the "keep everything" table first. Any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the two tables.RIGHT JOIN
Keep all orders, even the one with no matching customer.
-- RIGHT JOIN: the mirror image — keep ALL rows from the RIGHT
-- table (orders) and attach customer data where it exists.
SELECT
c.name,
o.product,
o.amount
FROM customers c
RIGHT JOIN orders o
ON c.id = o.customer_id;
-- Order #105 was placed by customer_id 99, who isn't in our
-- customers table, so its name comes back NULL — an "orphan" order.Result — 5 rows — note the orphan order's NULL name:
| name | product | amount |
|---|---|---|
| Ada Lovelace | Keyboard | 75 |
| Grace Hopper | Monitor | 199.99 |
| Ada Lovelace | Mouse | 24.5 |
| Alan Turing | Desk | 120 |
| NULL | Webcam | 45 |
5. FULL OUTER JOIN — Keep Everything
A FULL OUTER JOIN combines both behaviours: it keeps every row from both tables. Matched rows line up normally; unmatched rows on either side appear with NULLs filling the missing columns. It's the join you reach for when you want a complete picture and need to spot loose ends on either side.
With our data you get all five customers and all five orders: order-less Marie Curie shows up with NULL product/amount, and orphan order #105 shows up with a NULL name.
FULL OUTER JOIN directly — you emulate it by UNION-ing a LEFT JOIN with a RIGHT JOIN. PostgreSQL and SQL Server support it natively.FULL OUTER JOIN
Every customer AND every order, matched where possible.
-- FULL OUTER JOIN: keep EVERYTHING from both tables.
-- Matched rows line up; unmatched rows on either side show NULL.
SELECT
c.name,
o.product,
o.amount
FROM customers c
FULL OUTER JOIN orders o
ON c.id = o.customer_id;
-- You get every customer (even order-less Marie Curie) AND
-- every order (even the orphan #105 with no customer).Result — 7 rows — NULLs on BOTH sides:
| name | product | amount |
|---|---|---|
| Ada Lovelace | Keyboard | 75 |
| Ada Lovelace | Mouse | 24.5 |
| Grace Hopper | Monitor | 199.99 |
| Alan Turing | Desk | 120 |
| Linus Torvalds | NULL | NULL |
| Marie Curie | NULL | NULL |
| NULL | Webcam | 45 |
Common Errors (and the fix)
- Forgetting
ON→ a cross join "explosion":FROM customers c JOIN orders owith noONpairs every customer with every order. Our 5×5 tables would balloon to 25 rows; on real tables that's millions. Always state the matching rule:ON c.id = o.customer_id. - "ambiguous column name: id": both tables have an
id, so SQL doesn't know which you mean. Prefix it with the table or alias —c.idoro.id, never a bareid. - Using
INNERwhen you meantLEFT: anINNER JOINsilently drops customers with no orders. If a customer "disappeared" from your report, you probably wanted aLEFT JOINto keep unmatched rows. - Filtering on a NULL with
=:WHERE o.id = NULLnever matches anything. To find unmatched rows after aLEFT JOIN, useWHERE o.id IS NULL. - "no such column: customers.name": once a table has an alias (
customers c), you must use the alias — writec.name, notcustomers.name.
📘 Quick Reference
| Join | Keeps | NULLs appear for… |
|---|---|---|
| INNER JOIN | Only rows matched in both | Never (unmatched rows dropped) |
| LEFT JOIN | All left rows + matches | Unmatched left rows (right side NULL) |
| RIGHT JOIN | All right rows + matches | Unmatched right rows (left side NULL) |
| FULL OUTER JOIN | All rows from both tables | Unmatched rows on either side |
Shape: SELECT c.name, o.product FROM customers c <JOIN> orders o ON c.id = o.customer_id;
Frequently Asked Questions
Q: What's the difference between JOIN and INNER JOIN?
Nothing — JOIN on its own means INNER JOIN in every major database. Writing INNER explicitly just makes your intent clearer.
Q: Which table should go "left"?
The one whose rows you want to keep no matter what. customers LEFT JOIN orders keeps all customers; the order is a deliberate choice, not a rule.
Q: Can I join more than two tables?
Yes — chain them: ... JOIN orders o ON ... JOIN order_items oi ON .... Each JOIN adds one more link, each with its own ON condition. You'll meet this in later lessons.
Q: Why do I get duplicate-looking rows?
If a customer has two orders, they appear twice — once per matching order (see Ada Lovelace above). That's correct: the result has one row per matched pair, not one per customer.
Mini-Challenge: Big-Ticket Orders
Put it all together — a brief, a blank canvas, and the expected result in the comments. Write it, then copy it into a playground to confirm. (Hint: join customers to orders, then add a WHERE.)
🎯 Mini-Challenge
Buyer name + product + amount, only for orders over 50.
-- 🎯 MINI-CHALLENGE
-- Using only what this lesson covered (a JOIN + ON, a WHERE filter,
-- and choosing columns):
-- 1. Join customers to orders so each order shows the buyer's name.
-- 2. Keep only orders where amount is greater than 50.
-- 3. Return three columns: name, product, amount.
--
-- ✅ Expected result: 3 rows
-- Ada Lovelace | Keyboard | 75.00
-- Grace Hopper | Monitor | 199.99
-- Alan Turing | Desk | 120.00
-- your query here🎉 Lesson Complete
- ✅ Data is split across tables; a
JOINrecombines it on a shared value - ✅
INNER JOINkeeps only rows matched in both tables - ✅
LEFT/RIGHT JOINkeep all rows from one side;FULL OUTERkeeps both - ✅ Unmatched rows fill their missing columns with
NULL - ✅ The
ONcondition is the matching rule; table aliases (c,o) keep it readable - ✅ Next: Aggregate Functions — count, sum, and average across the rows a JOIN produces
Sign up for free to track which lessons you've completed and get learning reminders.