Core · DatabasesLesson 4 of 6Free to read

Joining tables · SQL and Databases

How do you combine two tables?

Why you need it

An order row only stores customer_id = 2, not the name Bo. That is on purpose: the name lives in one place, so changing it is one update. To see names next to orders you join the tables back together. Joins are the single most used SQL skill after SELECT.

The intuition

JOIN … ON matches rows from two tables where the condition is true, usually this table's id = that table's foreign key.

JOIN (inner join) keeps only rows that found a partner.
LEFT JOIN keeps every row from the left table; where no partner exists, the right side's columns are NULL.

Eli has never ordered. An inner join drops Eli; a left join keeps Eli with NULLs, which is exactly how you find "customers with no orders".

Definition

Foreign key

A column that holds the id of a row in another table, such as orders.customer_id → customers.id.

Table alias

`FROM orders o JOIN customers c` lets you write `o.qty` and `c.name` instead of the full table names.

Examples

Who bought what

Run it in the lesson

Three tables, two joins. Each ON says how the rows match.

SELECT c.name, p.name AS product, o.qty
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN products p ON p.id = o.product_id
WHERE o.day = '2025-03-05';

Output

name | product | qty
Bo | Notebook | 2
Dana | Lamp | 1

Step by step

L2Start from the orders, called o.
L3Each order finds its customer.
L4…and its product.
L5Two orders on that day.Bo | Notebook | 2

Customers with no orders

Run it in the lesson

LEFT JOIN keeps Eli; the order columns are NULL for Eli, and that is what WHERE looks for.

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Output

name
Eli

Step by step

L3Every customer stays, partner or not.
L4Only Eli has no matching order.Eli

Common mistakes

Forgetting ON

A join without a matching condition pairs every row with every row: 7 orders × 5 customers = 35 rows of nonsense.

Ambiguous column names

Both customers and products have name. Write c.name and p.name, or the database can't tell which one you mean.

Quick quiz

1. What does this print?

SELECT COUNT(*) FROM customers c JOIN orders o ON o.customer_id = c.id;

A
```
COUNT(*)
7
```
B
```
COUNT(*)
5
```
C
```
COUNT(*)
8
```

Show the answer

Answer: A

Each of the 7 orders finds exactly one customer. Eli has no order and disappears in an inner join.

2. What does this print?

SELECT COUNT(*) FROM customers c LEFT JOIN orders o ON o.customer_id = c.id;

A
```
COUNT(*)
7
```
B
```
COUNT(*)
5
```
C
```
COUNT(*)
8
```

Show the answer

Answer: C

The 7 matched rows, plus one row for Eli with NULLs.

Practice

3 exercises in three steps: predict what a program prints, change a program to meet a new requirement, then write one from scratch. Your code runs in the browser and is checked the moment you submit.

PredictSpending per customer
Type exactly what this query returns.
ModifyShow names, not ids
The query shows customer_id and qty. Join customers so it shows the customer's name and qty instead, for orders with qty of 5 or more.
WriteNever a big order
Return the name of every product that has no order with a qty of 2 or more.

Practise this lesson

Open the interactive lesson to edit and run these examples and solve the exercises with instant feedback. Free to start, no sign-up.

Open the interactive lesson Course map

Interactive lessons open in order. If you have not done the earlier ones yet, you start at the course map.

Terms in this lesson

Term	Chinese	Definition
Join	连接	Combining rows of two tables that match on a condition.
Left join	左连接	A join that keeps every row of the left table, with NULLs where there is no match.
Foreign key	外键	A column that points to a row in another table by its id.