JOIN: two tables in one query

In short: JOIN … ON combines a row of one table with the row of another table that its foreign key points to, for example an order with its customer, and keeps only the rows that have a match.

Problem

Orders with names

The courier company wants a list: order number, date and customer name. The number and date are in orders, the name is in customers.

In the last lesson we did it with two queries and carried the number between them by hand. For 60 orders that is 60 lookups. We need one query that finds the customer of every order by itself.

The idea

JOIN … ON

JOIN puts next to every row of one table the matching row of the other. ON says which row matches which:

SQLdatabase: shop

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

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

▶ RunRestore the queryFresh database

For every order the database looks for the customer whose customers.id equals orders.customer_id, and glues them into one row. That is why the columns are written with the table name in front: orders.id, customers.name.

60 rows come out, as many as there are orders: every order has exactly one customer. This is called a JOIN (or INNER JOIN): only the pairs that are found remain. Teodora and Yavor are not in the list because they have no orders. We will come back to them in the next lesson.

On small tables you can see how it happens: four orders and three customers, and Teodora has no orders.

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

Step by step, in words

1. 4 orders and 3 customers. The customer_id of an order is the id of its customer.
2. Order 1 has customer_id 2. The database looks for the customer with id 2: Georgi Petrov. The pair becomes a row.
3. Order 2: customer_id 1, that is Maria Ivanova.
4. Order 3 is again for customer 1: Maria Ivanova is in the result a second time.
5. Order 4 is again for customer 2: Georgi Petrov is in the result a second time.
6. Teodora Genova (id 3) is not the customer_id of any order. There is no pair, so she is not in the result.
7. 4 rows, as many as there are orders. JOIN keeps only the pairs that are found.

The same query on these small tables. Run it and compare with the last step:

SQLempty database

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

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

▶ RunRestore the queryFresh database

The idea

Short names

orders.ordered_at and customers.name get long. A table can be given a short name for the query: you write it after the table name in FROM and JOIN.

SQLdatabase: shop

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

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

▶ RunRestore the queryFresh database

Same query, same result. o is orders, c is customers. The short name holds only within this query. You can also write FROM orders AS o.

Try it

The new orders

Show the number of the new orders (status is new) with the customer's name and email, so a confirmation can be sent.

SQLdatabase: shop

SELECT id, customer_id FROM orders WHERE status = 'new';

SELECT id, customer_id FROM orders WHERE status = 'new';

Check▶ Run

See it

“ambiguous column name”

Here is a query without table names. Run it:

SQLdatabase: shop

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

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

▶ RunRestore the queryFresh database

Both orders and customers have an id column. After a JOIN the columns of both are together, and the database does not know which id you mean. “Ambiguous” means it can be read two ways. The name column exists only in customers, so it does not complain about that one.

The fix is to say which table the column is from:

SQLdatabase: shop

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

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

▶ RunRestore the queryFresh database

A good habit is to put the short name in front of every column in a query with JOIN, even when it is not required. Whoever reads it sees at once where each column comes from.

The idea

JOIN, WHERE and ORDER BY

After a JOIN, everything you know works as before, and conditions can use columns from both tables. The orders of the customers from Varna, by date:

SQLdatabase: shop

SELECT o.id, o.ordered_at, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.city = 'Varna'
ORDER BY o.ordered_at;

SELECT o.id, o.ordered_at, c.name FROM orders o JOIN customers c ON c.id = o.customer_id WHERE c.city = 'Varna' ORDER BY o.ordered_at;

▶ RunRestore the queryFresh database

The city is in customers, the date in orders. The order of the parts is: SELECT … FROM … JOIN … ON … WHERE … ORDER BY.

Try it

What is in order 60

Show the product name, quantity and price for every line of order 60.

SQLdatabase: shop

SELECT product_id, quantity, unit_price FROM order_items WHERE order_id = 60;

SELECT product_id, quantity, unit_price FROM order_items WHERE order_id = 60;

Check▶ Run

Exercise

Two tasks

1. The shop is checking why orders get cancelled. Show the number of the cancelled orders (cancelled) with the customer's name and city.

The cancelled orders with the customer and their city:

SQLdatabase: shop

Check▶ Run

2. The quality team wants the reviews rated 3 or lower together with the product name, so they know what to check.

The low ratings: the product, the rating and the review text:

SQLdatabase: shop

Check▶ Run

More precisely, if you are curious

You will also meet the older form: the tables in FROM separated by a comma, and the condition for the pairs in WHERE.

SQLdatabase: shop

SELECT o.id, c.name
FROM orders o, customers c
WHERE c.id = o.customer_id;

SELECT o.id, c.name FROM orders o, customers c WHERE c.id = o.customer_id;

▶ RunRestore the queryFresh database

The result is the same. The JOIN … ON form keeps the joining condition apart from the filtering conditions. That is why the course writes JOIN.

Test

Check yourself

1. What does ON c.id = o.customer_id say?

Which row of customers goes with which row of ordersWhich columns to showHow to sort the rows

2. Why does SELECT id FROM orders JOIN customers … give “ambiguous column name: id”?

Because id is a reserved wordBecause both tables have an id column and the database does not know which one you wantBecause id cannot be shown after a JOIN

3. In FROM orders o JOIN customers c, what is o?

A new copy of the tableA short name for orders in this queryA column

4. Where does WHERE go in a query with JOIN?

After JOIN … ON and before ORDER BYBefore JOINAt the end, after ORDER BY

Check

Start the lesson over

Related lessons

• Builds on: WHERE: filtering rows · NULL: no value · ORDER BY, LIMIT and DISTINCT · Primary key and foreign key
• Continues in: LEFT JOIN: rows without a match too · Many tables, and a table joined to itself · UNION, INTERSECT, EXCEPT and CROSS JOIN · Subqueries
• Tasks on this topic: Joining tables