SQL practice › SQL reference › JOIN
SQL JOIN: INNER JOIN and LEFT JOIN, with queries you can run
A JOIN puts rows from two tables side by side when they match on a condition, usually an id that one table stores to point at the other. INNER JOIN (or just JOIN) returns only the rows that found a match. LEFT JOIN returns every row of the first table, matched or not, with NULL in the second table's columns when there was nothing to match.
Joins rarely fail with an error. They fail by returning too few rows, or too many, and the query looks fine. This page shows the three ways that happens.
Skip to the 4 exercises ↓
Syntax
SELECT c.name, o.product
FROM customers c
JOIN orders o ON o.customer_id = c.id
FROM customers c- The first (left) table.
c is an alias: a short name for the table inside this query. JOIN orders o- The table to bring in.
JOIN alone means INNER JOIN. Write LEFT JOIN to keep the customers that have no order. ON o.customer_id = c.id- The match rule: which column of one table points at which column of the other. Each order stores the id of its customer.
The two tables
Five customers and eight orders. Each order has a customer_id, the id of the customer who placed it. Eve has never ordered.
customers
| id | name | city |
| 1 | Ava | Denver |
| 2 | Ben | Austin |
| 3 | Chloe | Denver |
| 4 | Dan | Boston |
| 5 | Eve | Austin |
orders
| id | customer_id | product | amount | order_date |
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
Live queryINNER JOIN: every order with its customer
Tables: customers, orders
customers
| id | name | city |
| 1 | Ava | Denver |
| 2 | Ben | Austin |
| 3 | Chloe | Denver |
| 4 | Dan | Boston |
| 5 | Eve | Austin |
orders
| id | customer_id | product | amount | order_date |
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
Result · 8 rows
| name | product | amount |
| Ava | Desk | 240 |
| Ben | Lamp | 35 |
| Ava | Chair | 120 |
| Chloe | Monitor | 180 |
| Ben | Desk | 240 |
| Dan | Lamp | 35 |
| Ava | Lamp | 35 |
| Chloe | Chair | 120 |
How the database reads it
- It takes a row from
customers, say Ava (id 1).
- It looks for the rows in
orders where customer_id is 1 and finds three.
- It returns one row per match: Ava next to each of her three orders. A customer with one order gives one row. A customer with none gives no row at all.
So the number of rows in the result is the number of matches, not the number of customers and not always the number of orders.
LEFT JOIN: keep the rows that found nothing
LEFT JOIN does the same thing, then adds back every row of the left table that matched nothing, with NULL in the columns that would have come from the right table. "Left" is simply the table written first, after FROM.
Live queryLEFT JOIN: every customer, with or without orders
Tables: customers, orders
customers
| id | name | city |
| 1 | Ava | Denver |
| 2 | Ben | Austin |
| 3 | Chloe | Denver |
| 4 | Dan | Boston |
| 5 | Eve | Austin |
orders
| id | customer_id | product | amount | order_date |
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
Result · 9 rows
| name | product | amount |
| Ava | Chair | 120 |
| Ava | Desk | 240 |
| Ava | Lamp | 35 |
| Ben | Desk | 240 |
| Ben | Lamp | 35 |
| Chloe | Chair | 120 |
| Chloe | Monitor | 180 |
| Dan | Lamp | 35 |
| Eve | NULL | NULL |
Three ways a join goes wrong without an error
Each one runs below. Fix the query in the box and run it again.
1. Joining on the wrong column. Both tables have a column called id, and joining them to each other is the classic slip. Customer ids are 1 to 5 and order ids start at 101, so nothing matches and the result is empty. With other data it would be worse: some ids would match by accident, and you'd get rows that look real.
Live queryWrong key: no error, no rows
Tables: customers, orders
customers
| id | name | city |
| 1 | Ava | Denver |
| 2 | Ben | Austin |
| 3 | Chloe | Denver |
| 4 | Dan | Boston |
| 5 | Eve | Austin |
orders
| id | customer_id | product | amount | order_date |
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
2. A WHERE that undoes the LEFT JOIN. You want every customer, with their orders over 100 if they have any. A WHERE on the orders table runs after the join, when Eve's row has NULL in amount. NULL > 100 isn't true, so her row is thrown away, and so is Dan's, whose only order was 35. The LEFT JOIN now behaves like an INNER JOIN.
Live queryWHERE on the right table: Dan and Eve disappear
Tables: customers, orders
customers
| id | name | city |
| 1 | Ava | Denver |
| 2 | Ben | Austin |
| 3 | Chloe | Denver |
| 4 | Dan | Boston |
| 5 | Eve | Austin |
orders
| id | customer_id | product | amount | order_date |
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
Result · 5 rows
| name | product | amount |
| Ava | Desk | 240 |
| Ava | Chair | 120 |
| Chloe | Monitor | 180 |
| Ben | Desk | 240 |
| Chloe | Chair | 120 |
3. Counting after a join. A join repeats a customer once per order. Count rows after it and you count orders, not customers.
Live queryHow many customers have ordered? Not 8
Tables: customers, orders
customers
| id | name | city |
| 1 | Ava | Denver |
| 2 | Ben | Austin |
| 3 | Chloe | Denver |
| 4 | Dan | Boston |
| 5 | Eve | Austin |
orders
| id | customer_id | product | amount | order_date |
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
Practice: 4 exercises
Write the query in the box and press Run. Your result is compared with the right one, on these tables and again on changed data, so a query with the values typed in won't pass. Column names don't matter; the columns and rows do.
Exercise 1Orders with the customer's name
List every order: its id, the name of the customer who placed it and that customer's city.
Tables: customers, orders
customers
| id | name | city |
| 1 | Ava | Denver |
| 2 | Ben | Austin |
| 3 | Chloe | Denver |
| 4 | Dan | Boston |
| 5 | Eve | Austin |
orders
| id | customer_id | product | amount | order_date |
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
Show the answer
SELECT o.id, c.name, c.city
FROM orders o
JOIN customers c ON c.id = o.customer_id
Result · 8 rows
| id | name | city |
| 101 | Ava | Denver |
| 102 | Ben | Austin |
| 103 | Ava | Denver |
| 104 | Chloe | Denver |
| 105 | Ben | Austin |
| 106 | Dan | Boston |
| 107 | Ava | Denver |
| 108 | Chloe | Denver |
Each order stores its customer's id in customer_id, so that's the column to match with customers.id. Starting from orders or from customers gives the same rows with an inner join.
Exercise 2Orders per customer, including zero
For every customer, return their name and how many orders they have placed. A customer with no orders must be in the result, with 0.
Tables: customers, orders
customers
| id | name | city |
| 1 | Ava | Denver |
| 2 | Ben | Austin |
| 3 | Chloe | Denver |
| 4 | Dan | Boston |
| 5 | Eve | Austin |
orders
| id | customer_id | product | amount | order_date |
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
Show the answer
SELECT c.name, COUNT(o.id)
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
Result · 5 rows
| name | COUNT(o.id) |
| Ava | 3 |
| Ben | 2 |
| Chloe | 2 |
| Dan | 1 |
| Eve | 0 |
LEFT JOIN keeps Eve, with NULL in the order columns. COUNT(o.id) counts only the rows where an order exists, so she gets 0. COUNT(*) would give her 1.
Exercise 3Customers who never ordered
Return the name of each customer who has never placed an order.
Tables: customers, orders
customers
| id | name | city |
| 1 | Ava | Denver |
| 2 | Ben | Austin |
| 3 | Chloe | Denver |
| 4 | Dan | Boston |
| 5 | Eve | Austin |
orders
| id | customer_id | product | amount | order_date |
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
Show the answer
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL
After the LEFT JOIN, a customer with no orders has NULL in every order column. Keeping only the rows where o.id IS NULL leaves exactly those customers. NOT EXISTS with a subquery gets the same result.
Exercise 4Sales per city
Return each city and the total amount ordered by its customers, the city with the biggest total first. Cities with no orders don't need to appear.
Tables: customers, orders
customers
| id | name | city |
| 1 | Ava | Denver |
| 2 | Ben | Austin |
| 3 | Chloe | Denver |
| 4 | Dan | Boston |
| 5 | Eve | Austin |
orders
| id | customer_id | product | amount | order_date |
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
Show the answer
SELECT c.city, SUM(o.amount)
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.city
ORDER BY SUM(o.amount) DESC
Result · 3 rows
| city | SUM(o.amount) |
| Denver | 695 |
| Austin | 275 |
| Boston | 35 |
The join gives each order its customer's city; GROUP BY c.city then folds the orders into one row per city and SUM adds their amounts. The city isn't stored in orders, so there is no way to get this without the join.
Questions people ask
Is JOIN the same as INNER JOIN?
Yes. INNER is the default and the word is optional, so JOIN and INNER JOIN are the same thing.
What's the difference between LEFT JOIN and LEFT OUTER JOIN?
None. OUTER is optional too: LEFT JOIN is short for LEFT OUTER JOIN.
What do RIGHT JOIN and FULL OUTER JOIN do?
RIGHT JOIN is a LEFT JOIN seen from the other side: it keeps every row of the second table. Most people swap the two tables and write LEFT JOIN instead, so that the table they want complete always comes first. FULL OUTER JOIN keeps the unmatched rows of both tables, with NULL on whichever side is missing.
Does it matter which table comes first?
For an inner join, no: the rows are the same either way. For a LEFT JOIN it decides everything, because the first table is the one that is kept whole.
Should a condition go in ON or in WHERE?
With an inner join the result is the same. With a LEFT JOIN, a condition on the second table belongs in ON if you want to keep the unmatched rows, because ON is applied while matching and WHERE after it, when those rows hold NULL. The second live query above shows the difference.
Keep going
Lesson 3 is seven more JOIN drills on a plant shop's customers and orders: writing the ON, the wrong-key trap, LEFT JOIN with counts, finding what's missing, a condition in ON versus WHERE, and counting after a join. It's free, like lessons 1, 2 and 4 and the level test.
Written and tested by Nico, who builds SheetDrill (about). Every query on this page was run on SQLite, and every exercise checked against right and wrong answers, before the page was published. Other databases differ in places; where they do, the page says so. Checked against: PostgreSQL documentation: Table Expressions (joined tables). Last checked November 2026.