Joining Tables with INNER JOIN

Data is split across tables to avoid repeating it. A customer’s name is stored once in customers, not on every order. A join puts the pieces back together.

The idea

orders.customer_id holds the id of the customer who placed the order. A join matches each order with the customer row that has the same id.

SELECT orders.order_id,
       orders.order_date,
       customers.name
FROM orders
INNER JOIN customers
        ON orders.customer_id = customers.customer_id;

ON states how the rows match. INNER JOIN returns only the rows that have a match in both tables. The word INNER is optional; JOIN alone means the same.

Table aliases

Short aliases make joins much easier to read.

SELECT o.order_id,
       o.order_date,
       c.name,
       c.city
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.customer_id
WHERE o.status = 'delivered'
ORDER BY o.order_date;

When a column name exists in both tables, you must say which one you mean, as in o.customer_id. Qualifying every column is a good habit.

Joining more than two tables

Add one JOIN for each extra table. To see what was on each order, go from order_items to orders and to products:

SELECT o.order_id,
       p.name AS product,
       oi.quantity,
       p.price,
       oi.quantity * p.price AS line_total
FROM order_items AS oi
JOIN orders   AS o ON oi.order_id = o.order_id
JOIN products AS p ON oi.product_id = p.product_id
ORDER BY o.order_id;

Joins and aggregates together

This is the pattern behind most reports: join the tables, then group.

SELECT p.category,
       SUM(oi.quantity)           AS units,
       SUM(oi.quantity * p.price) AS revenue
FROM order_items AS oi
JOIN products AS p ON oi.product_id = p.product_id
GROUP BY p.category
ORDER BY revenue DESC;

Revenue per customer needs all four tables:

SELECT c.name,
       SUM(oi.quantity * p.price) AS revenue
FROM customers AS c
JOIN orders      AS o  ON o.customer_id = c.customer_id
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN products    AS p  ON p.product_id = oi.product_id
WHERE o.status <> 'cancelled'
GROUP BY c.name
ORDER BY revenue DESC;

Watch the row count

A join can multiply rows. Joining orders to order_items returns one row per item, not one per order, so COUNT(*) then counts items. To count orders after such a join, use COUNT(DISTINCT o.order_id).

Whenever a total looks too large, check whether a join has repeated the rows you are adding up.

Practice

List each delivered order with the customer’s name and the order’s total value.