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
idnamecity
1AvaDenver
2BenAustin
3ChloeDenver
4DanBoston
5EveAustin
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Live queryINNER JOIN: every order with its customer
Tables: customers, orders
customers
idnamecity
1AvaDenver
2BenAustin
3ChloeDenver
4DanBoston
5EveAustin
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
Result · 8 rows
nameproductamount
AvaDesk240
BenLamp35
AvaChair120
ChloeMonitor180
BenDesk240
DanLamp35
AvaLamp35
ChloeChair120

How the database reads it

  1. It takes a row from customers, say Ava (id 1).
  2. It looks for the rows in orders where customer_id is 1 and finds three.
  3. 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
idnamecity
1AvaDenver
2BenAustin
3ChloeDenver
4DanBoston
5EveAustin
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
Result · 9 rows
nameproductamount
AvaChair120
AvaDesk240
AvaLamp35
BenDesk240
BenLamp35
ChloeChair120
ChloeMonitor180
DanLamp35
EveNULLNULL

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
idnamecity
1AvaDenver
2BenAustin
3ChloeDenver
4DanBoston
5EveAustin
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
Result · 0 rows
No rows

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
idnamecity
1AvaDenver
2BenAustin
3ChloeDenver
4DanBoston
5EveAustin
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
Result · 5 rows
nameproductamount
AvaDesk240
AvaChair120
ChloeMonitor180
BenDesk240
ChloeChair120

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
idnamecity
1AvaDenver
2BenAustin
3ChloeDenver
4DanBoston
5EveAustin
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
Result · 1 row
COUNT(*)
8

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
idnamecity
1AvaDenver
2BenAustin
3ChloeDenver
4DanBoston
5EveAustin
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
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
idnamecity
101AvaDenver
102BenAustin
103AvaDenver
104ChloeDenver
105BenAustin
106DanBoston
107AvaDenver
108ChloeDenver

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
idnamecity
1AvaDenver
2BenAustin
3ChloeDenver
4DanBoston
5EveAustin
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
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
nameCOUNT(o.id)
Ava3
Ben2
Chloe2
Dan1
Eve0

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
idnamecity
1AvaDenver
2BenAustin
3ChloeDenver
4DanBoston
5EveAustin
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
Show the answer
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL
Result · 1 row
name
Eve

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
idnamecity
1AvaDenver
2BenAustin
3ChloeDenver
4DanBoston
5EveAustin
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
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
citySUM(o.amount)
Denver695
Austin275
Boston35

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.

Related

SQL JOIN practice exercisesSQL GROUP BY practiceSQL skills testSQL reference with queries you can runSQL practice online

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.