Joins
Joining more than two tables
So far, our queries have joined two tables at a time.
A query can also join more than two tables.
For example, suppose we want to see an order together with the customer who placed it and the product that was ordered.
That information is spread across four tables:
customersstores the customerordersstores the orderorder_itemsstores the products included in the orderproductsstores information about each product
The tables are connected through these columns:
12345customers.id ← orders.customer_idorders.id ← order_items.order_idproducts.id ← order_items.product_idWe can follow these relationships to combine information from all four tables.
Joining four tables
Run:
123456789101112131415SELECT o.id AS order_id, c.full_name, p.name AS product_name, oi.quantity, oi.unit_priceFROM orders oINNER JOIN customers c ON o.customer_id = c.idINNER JOIN order_items oi ON o.id = oi.order_idINNER JOIN products p ON oi.product_id = p.idWHERE oi.id <= 4ORDER BY oi.id;You should see:
123456 order_id | full_name | product_name | quantity | unit_price----------+-------------+--------------+----------+----------- 2 | Customer 3 | Product 2 | 2 | 3.01 3 | Customer 4 | Product 3 | 3 | 3.02 4 | Customer 5 | Product 4 | 4 | 3.03 5 | Customer 6 | Product 5 | 1 | 3.04Each JOIN adds another related table to the query.
Let's look at the relationships one at a time.
This joins each order to the customer who placed it:
12INNER JOIN customers c ON o.customer_id = c.idThis joins each order to its order items:
12INNER JOIN order_items oi ON o.id = oi.order_idAnd this joins each order item to its product:
12INNER JOIN products p ON oi.product_id = p.idTogether, these relationships let us return columns that originally live in four different tables.
A simplified view looks like this:
Following one result row
Consider this row from the result:
12 | Customer 3 | Product 2 | 2 | 3.01It comes from matching rows across the four tables.
The orders row has an id of 2 and a customer_id of 3.
That customer_id matches customer 3, giving us Customer 3.
An order_items row has an order_id of 2, which connects it to the same order.
That order item has a product_id of 2, which matches product 2, giving us Product 2.
The query therefore combines values from all of those matching rows into one result row.
One row can become multiple result rows
Joining tables does not necessarily produce one result row for each row in the table you started with.
For example, one order can contain multiple order items.
If an order matches three rows in order_items, the joined result can contain three rows for that order: one for each matching order item.
Imagine an order like this:
1234ordersid42with three matching order items:
123456order_itemsorder_id product_id42 1042 2542 81After joining them, order 42 appears three times:
1234order_id product_id42 1042 2542 81This is an important property of joins.
A join returns rows based on matching combinations of rows, not simply one row for each record in the first table.
Each JOIN needs its own relationship
When joining several tables, each JOIN should describe how the new table relates to the tables already involved in the query.
In our query:
1234567FROM orders oINNER JOIN customers c ON o.customer_id = c.idINNER JOIN order_items oi ON o.id = oi.order_idINNER JOIN products p ON oi.product_id = p.idthe relationships form a chain:
1customers ← orders ← order_items → productsThis is why understanding the relationships between tables is important when writing joins.
The SQL syntax itself is straightforward once you know which columns connect the tables.