Join tables

Two tables

Real databases keep different kinds of data in different tables. This lesson uses two tables.

customers:

id	name	city
1	Ana	Lisbon
2	Ben	Porto
3	Cy	Lisbon
4	Dee	Faro

orders:

id	customer_id	item	amount
1	1	Tea	6.00
2	1	Cake	4.50
3	2	Coffee	3.20

The table orders has six rows. The rows above are the first three. The column customer_id holds the id of a customer. It links each order to one customer. A column like this is called a foreign key.

JOIN

A JOIN puts rows of two tables side by side. The ON clause says which rows belong together.

SELECT customers.name, orders.item
FROM customers
JOIN orders ON orders.customer_id = customers.id;

When two tables have a column with the same name, write the table name before it, with a dot: customers.id. This tells the database which column you mean. You can use short names for tables:

SELECT c.name, o.item
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id;

A plain JOIN is an inner join. It keeps only the rows that have a match in both tables. A customer with no orders is not in the result.

LEFT JOIN

A LEFT JOIN keeps all rows of the first (left) table. When a row has no match, the columns of the other table are NULL, which means "no value".

SELECT customers.name, orders.item
FROM customers
LEFT JOIN orders ON orders.customer_id = customers.id;

COUNT(orders.id) counts only the rows where orders.id is not NULL. So a customer with no orders gets 0. COUNT(*) would give 1, because the empty row still counts as a row.

Your turn

Write one query for each task, one after the other. Queries can have many lines. The semicolon marks the end.