JOINs.
Combine tables. Answer real questions.
Real data lives across multiple tables — users in one, orders in another, products in a third. JOINs combine them into a single result. This is where SQL stops being "read a table" and becomes "answer questions across your entire database." It's also where beginners get confused — so every example below shows the actual rows returned. Click Output to see exactly which rows match, which don't, and which get NULL values.
A JOIN combines rows from two tables based on a shared column. INNER JOIN keeps only matches. LEFT JOIN keeps all left rows, NULLs on right where no match. RIGHT JOIN mirrors LEFT. FULL OUTER JOIN keeps everything from both sides. The ON clause specifies the match condition — usually orders.user_id = users.id. In 90% of real queries, you'll use INNER or LEFT.
Before this lesson: complete Lesson 2. You need SELECT, WHERE, ORDER BY.
🎯 Why JOINs exist
Real data is split across tables. JOINs are how you put it back together.
If you stored user data and order data in one table, every order row would need to repeat the user's name, email, and address. That's wasteful (dozens of rows with duplicate user info) and dangerous (if the user changes their email, you'd have to update every order). This is why databases normalize data — split it across related tables.
-- Two related tables. orders.user_id is a -- FOREIGN KEY pointing to users.id. users ───── id | name | email ───┼─────────────┼───────────────── 1 | Alice Chen | alice@example.com 2 | Bob Smith | bob@example.com 3 | Priya Patel | priya@example.com 4 | Marcus Lee | marcus@example.com 5 | Sofia García| sofia@example.com orders ────── id | user_id | amount | created_at ───┼─────────┼────────┼─────────── 1 | 1 | 150.00 | 2026-01-15 2 | 1 | 200.00 | 2026-01-20 3 | 2 | 89.99 | 2026-02-01 4 | 4 | 300.00 | 2026-03-10 5 | 4 | 45.50 | 2026-03-15 -- Notice: Priya (id=3) and Sofia (id=5) -- have NO orders. This is why LEFT JOIN exists.
users table
| id | name | |
|---|---|---|
| 1 | Alice Chen | alice@example.com |
| 2 | Bob Smith | bob@example.com |
| 3 | Priya Patel | priya@example.com |
| 4 | Marcus Lee | marcus@example.com |
| 5 | Sofia García | sofia@example.com |
orders table
| id | user_id | amount | created_at |
|---|---|---|---|
| 1 | 1 | 150.00 | 2026-01-15 |
| 2 | 1 | 200.00 | 2026-01-20 |
| 3 | 2 | 89.99 | 2026-02-01 |
| 4 | 4 | 300.00 | 2026-03-10 |
| 5 | 4 | 45.50 | 2026-03-15 |
Notice the gap: users 3 and 5 have no orders. This is the entire reason different JOIN types exist. INNER JOIN will silently drop them. LEFT JOIN will keep them with NULL order data. Which one you want depends on the question: "show me users who ordered" (INNER) vs "show me all users with their orders" (LEFT). Every JOIN decision comes down to: what do you do with the rows that don't match?
🔗 INNER JOIN — matches only
Only rows where both tables have a match. The default JOIN type.
-- Every order with its user's name. -- Users without orders do NOT appear. SELECT u.name, o.amount, o.created_at FROM users u INNER JOIN orders o ON o.user_id = u.id ORDER BY o.created_at;
| name | amount | created_at |
|---|---|---|
| Alice Chen | 150.00 | 2026-01-15 |
| Alice Chen | 200.00 | 2026-01-20 |
| Bob Smith | 89.99 | 2026-02-01 |
| Marcus Lee | 300.00 | 2026-03-10 |
| Marcus Lee | 45.50 | 2026-03-15 |
What it does
Returns only rows where the ON condition is true. Users 3 (Priya) and 5 (Sofia) have no orders, so they're excluded. The result has 5 rows — one per order.
Table aliases
users u and orders o give each table a short alias. Use u.name and o.amount instead of the full table names — much cleaner, especially with 3+ joins.
The ON clause
Specifies how to match rows. ON o.user_id = u.id means "match orders rows where user_id equals a users id." The condition is what makes the JOIN meaningful.
Duplicate-looking rows are correct
Alice appears twice because she has 2 orders. Marcus appears twice because he has 2 orders. JOINs multiply rows — one result row per matching pair.
INNER JOIN silently drops unmatched rows. This is usually what you want — "show me users who ordered" naturally excludes users who didn't. But if your query is supposed to return all users and you accidentally use INNER JOIN, the users with no orders just disappear from the result. No error, no warning. That's why understanding the difference between INNER and LEFT is the single most important JOIN skill.
⬅️ LEFT JOIN — all left, matches from right
Keep every row from the left table. NULLs fill in where the right table has no match.
-- ALL users, plus their orders (if any). -- Users without orders still appear, with NULLs. SELECT u.name, o.amount, o.created_at FROM users u LEFT JOIN orders o ON o.user_id = u.id ORDER BY u.name;
| name | amount | created_at |
|---|---|---|
| Alice Chen | 150.00 | 2026-01-15 |
| Alice Chen | 200.00 | 2026-01-20 |
| Bob Smith | 89.99 | 2026-02-01 |
| Marcus Lee | 300.00 | 2026-03-10 |
| Marcus Lee | 45.50 | 2026-03-15 |
| Priya Patel | NULL | NULL |
| Sofia García | NULL | NULL |
LEFT JOIN is how you answer "which users have never ordered?" — the reverse of "which users have ordered":
-- Users who have NEVER placed an order SELECT u.name, u.email FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE o.id IS NULL;
| name | |
|---|---|
| Priya Patel | priya@example.com |
| Sofia García | sofia@example.com |
What it does
Returns every row from the left table. For each, matches rows from the right table (multiplying if multiple match). If no match, fills NULLs on the right side.
The "no match" pattern
LEFT JOIN ... WHERE right.id IS NULL is the standard way to find rows with no matching rows elsewhere. "Users who never ordered," "products never sold," "customers with no support tickets."
Row count is >= left table
A LEFT JOIN returns at least as many rows as the left table, usually more (if the right table has multiple matches per left row). Never fewer.
Which side is "left"?
The table named first in the FROM clause is the left side. The table named after JOIN is the right side. FROM users LEFT JOIN orders — users is left, orders is right.
The LEFT JOIN + IS NULL pattern is one of the most useful queries in SQL. It answers "which X has no Y?" — a question that comes up constantly. Users with no orders, products with no sales, articles with no comments, accounts with no activity. Every time you need a "find what's missing" query, this pattern is the answer. Memorize it — you'll use it weekly.
➡️ RIGHT JOIN — all right, matches from left
Mirror of LEFT JOIN. Rarely used — usually rewritten as LEFT.
-- Same result as: FROM orders LEFT JOIN users -- Returns all ORDERS, with user info if matched SELECT u.name, o.amount, o.created_at FROM users u RIGHT JOIN orders o ON o.user_id = u.id ORDER BY o.created_at;
| name | amount | created_at |
|---|---|---|
| Alice Chen | 150.00 | 2026-01-15 |
| Alice Chen | 200.00 | 2026-01-20 |
| Bob Smith | 89.99 | 2026-02-01 |
| Marcus Lee | 300.00 | 2026-03-10 |
| Marcus Lee | 45.50 | 2026-03-15 |
RIGHT JOIN is rarely used. Rewrite it as LEFT JOIN. Every RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order. FROM users RIGHT JOIN orders = FROM orders LEFT JOIN users. The second version is preferred because LEFT JOIN is more common — readers recognize it faster. Consistency across your codebase matters more than which side of the JOIN you use.
🔀 FULL OUTER JOIN — everything
All rows from both sides. Rarely needed. When you think you need it, check twice.
-- All users AND all orders — matched where possible, -- NULLs where not. Includes users without orders -- AND orders without users (orphans). SELECT u.name, o.amount, o.created_at FROM users u FULL OUTER JOIN orders o ON o.user_id = u.id ORDER BY u.name NULLS LAST, o.created_at; -- Note: FULL OUTER JOIN is not supported in MySQL. -- Use a UNION of LEFT JOIN and RIGHT JOIN instead.
| name | amount | created_at |
|---|---|---|
| Alice Chen | 150.00 | 2026-01-15 |
| Alice Chen | 200.00 | 2026-01-20 |
| Bob Smith | 89.99 | 2026-02-01 |
| Marcus Lee | 300.00 | 2026-03-10 |
| Marcus Lee | 45.50 | 2026-03-15 |
| Priya Patel | NULL | NULL |
| Sofia García | NULL | NULL |
What it does
Returns all rows from both tables. Matched rows have both sides filled. Unmatched left rows get NULLs on the right. Unmatched right rows get NULLs on the left.
Rarely needed
Usually when you think you need FULL OUTER JOIN, you actually want two queries: "users with orders" and "orders without users." Or you're diagnosing data integrity issues.
Not universal
PostgreSQL, Oracle, SQL Server support FULL OUTER JOIN. MySQL does not — you have to UNION a LEFT JOIN and a RIGHT JOIN to simulate it.
Performance
FULL OUTER JOIN is expensive — it can't use indexes as efficiently as INNER or LEFT. If you're using it on large tables, check the query plan and consider whether two queries would be faster.
✖️ CROSS JOIN — every combination
Cartesian product. Rarely intentional, often accidental.
-- Every color × every size — 3 × 3 = 9 rows SELECT colors.name AS color, sizes.name AS size FROM colorsCROSS JOIN sizes; -- ⚠️ ACCIDENTAL CROSS JOIN — the classic bug -- Forgetting the ON clause in an INNER JOIN -- produces the same result: SELECT * FROM users JOIN orders; -- no ON clause! -- Returns users × orders = 5 × 5 = 25 rows.
colors × sizes (3 × 3 = 9 rows)
| color | size |
|---|---|
| Red | Small |
| Red | Medium |
| Red | Large |
| Blue | Small |
| Blue | Medium |
| Blue | Large |
| Green | Small |
| Green | Medium |
| Green | Large |
What it does
Every row from A paired with every row from B. If A has n rows and B has m rows, result has n × m rows. No matching condition — just every combination.
Legitimate use
Generating combinations: product variants (size × color × material), time slots (days × hours), test matrices. Rare but occasionally useful.
The accidental bug
Forgetting ON in an INNER JOIN produces a CROSS JOIN. On large tables, this explodes — 10,000 × 10,000 = 100 million rows. Query hangs, then crashes. Always include the ON clause.
Data explosion
A CROSS JOIN on 1,000-row tables returns 1,000,000 rows. On 10,000-row tables, 100,000,000 rows. Always check table sizes before writing one.
The missing-ON bug is the most dangerous SQL mistake. Write FROM a JOIN b with no ON clause and every row of a pairs with every row of b. On production tables with 100K rows each, that's 10 billion rows — the query hangs the database and can take down your app. Always include ON. If you're lucky, you get an error. If you're unlucky, you get the query running.
🏆 Challenge
Three tasks. Each exercises a different JOIN pattern.
Task 1 — Predict the output
Given the users and orders tables from earlier (5 users, 5 orders, Priya and Sofia have no orders):
1. How many rows does SELECT * FROM users INNER JOIN orders ON users.id = orders.user_id return?
2. How many rows does SELECT * FROM users LEFT JOIN orders ON users.id = orders.user_id return?
3. How many rows does SELECT * FROM users RIGHT JOIN orders ON users.id = orders.user_id return?
4. How many rows does SELECT * FROM users FULL OUTER JOIN orders ON users.id = orders.user_id return?
5. How many rows does SELECT * FROM users CROSS JOIN orders return?
Explain why each number is what it is.
Task 2 — Write queries
Using the users and orders tables, write queries to answer:
1. All users with their total order count (users with 0 orders should show 0, not be excluded)
2. Users who have placed more than 2 orders
3. All orders with the user's name, including orphan orders (orders whose user_id doesn't match any user)
4. Users who have never placed an order
5. The 3 users with the highest total spending, including users with 0 orders
Which query needs LEFT JOIN? Which needs INNER JOIN? Why?
Task 3 — Design a schema
You're building a movie review site. Design the tables and JOINs for these questions:
1. Show all movies with their average rating
2. Show users who have reviewed more than 5 movies
3. Show movies that have never been reviewed
4. Show reviews with the reviewer's name and the movie's title
5. Show the top 10 highest-rated movies with at least 3 reviews
Design the tables (movies, users, reviews), identify the foreign keys, and write the query for each question. Which JOIN type does each query need?
⚠️ Common mistakes
The JOIN bugs that catch every beginner.
FROM users JOIN orders with no ON produces a CROSS JOIN — every user × every order. On production tables, this hangs the database. Always include ON.
You want "all users and their orders" but use INNER JOIN — users with 0 orders vanish. Fix: LEFT JOIN.
To find users with no orders, use LEFT JOIN ... WHERE orders.id IS NULL. Using WHERE orders.id = NULL returns 0 rows.
Users with 3 orders return 3 rows in a JOIN. If you want one row per user, use GROUP BY or DISTINCT.
SELECT name FROM users JOIN orders — which name? Both tables might have a name column. Fix: SELECT users.name or use table aliases.
ON orders.id = users.id joins order IDs to user IDs — meaningless. Fix: ON orders.user_id = users.id — the foreign key to the primary key.
FROM users LEFT JOIN orders WHERE orders.user_id = users.id — the WHERE clause turns the LEFT JOIN into an INNER JOIN (rows with NULL fail the WHERE). Fix: put the condition in ON.
A 4-table JOIN multiplies row counts. 100 × 50 × 200 × 30 = 300 million rows. If your query hangs, check for missing ON clauses or unexpected row explosions.
➡️ What's next
You can combine tables. Next: aggregate and summarize.
❓ Frequently asked questions
What is a SQL JOIN?
A JOIN combines rows from two or more tables based on a related column. Data is normalized across tables — users in one, orders in another — and JOINs let you query them together. The most common JOIN is on a foreign key: orders.user_id matches users.id.
What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN returns only rows where both tables have a match. LEFT JOIN returns all rows from the left table, plus matching rows from the right — rows with no match get NULL values on the right side. Use INNER when you only care about matches. Use LEFT when you want all left-table rows regardless.
What is the difference between LEFT JOIN and RIGHT JOIN?
They're mirror images. LEFT JOIN keeps all rows from the left table, matching from the right. RIGHT JOIN keeps all rows from the right table, matching from the left. In practice, RIGHT JOIN is rarely used — you can rewrite any RIGHT JOIN as a LEFT JOIN by swapping the table order.
What is a FULL OUTER JOIN?
A FULL OUTER JOIN returns all rows from both tables — matched rows have both sides filled, unmatched rows on either side get NULL for the other. Rarely used in practice because it's expensive and the results are usually noise.
What is a CROSS JOIN?
A CROSS JOIN returns every combination of rows from both tables — the Cartesian product. If table A has 10 rows and table B has 5 rows, CROSS JOIN returns 50 rows. Accidentally omitting the ON clause in an INNER JOIN produces the same result, which is a common bug.
What is a self JOIN?
A self JOIN joins a table to itself. Used for hierarchical data (employees.manager_id references employees.id) or finding duplicates. You must alias the two instances so SQL can distinguish them.
What is the difference between JOIN and multiple tables in FROM?
Both work but JOIN is the modern standard. FROM users, orders WHERE users.id = orders.user_id (comma syntax) is the old way. JOIN users u ON u.id = o.user_id (explicit JOIN) is preferred because it separates join conditions from WHERE filters.
Why does my JOIN return duplicate rows?
Because the right table has multiple matching rows for each left row. If a user has 3 orders, joining users to orders returns 3 rows for that user. This is normal — JOINs multiply rows. Fix: use DISTINCT (if you only need unique users), or aggregate (COUNT, SUM) to get one row per user.