Many tables, and a table joined to itself

In short: Several JOINs in a row link a chain of tables, and a self join links a table to itself, for example a category to its parent category.

Problem

Revenue by category

The owner wants to know which categories the money comes from. Quantity and price are in order_items. Whether the order was cancelled is in orders. The product's category is in products, and the category name in categories.

Four tables for one report. JOIN does not stop at two.

The idea

A chain of JOINs

Each following JOIN adds a table to what has already been put together. Order 60, with its products and their categories:

SQLdatabase: shop

SELECT o.id, p.name AS product, cat.name AS category, oi.quantity
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
WHERE o.id = 60;

SELECT o.id, p.name AS product, cat.name AS category, oi.quantity FROM orders o JOIN order_items oi ON oi.order_id = o.id JOIN products p ON p.id = oi.product_id JOIN categories cat ON cat.id = p.category_id WHERE o.id = 60;

▶ RunRestore the queryFresh database

The path follows the foreign keys: orders → order_items (by order_id) → products (by product_id) → categories (by category_id). Each link is a separate JOIN … ON.

Both products and categories have a name column. That is why we name them here with AS: product and category. Otherwise the result would have two name columns.

Try it

Who rated what

For every review, show the customer name, the product name and the rating.

SQLdatabase: shop

SELECT r.customer_id, r.product_id, r.rating
FROM reviews r;

SELECT r.customer_id, r.product_id, r.rating FROM reviews r;

Check▶ Run

See it

The lost money

Revenue by category, without the cancelled orders:

SQLdatabase: shop

SELECT cat.name AS category, SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
WHERE o.status <> 'cancelled'
GROUP BY cat.name
ORDER BY revenue DESC;

SELECT cat.name AS category, SUM(oi.quantity * oi.unit_price) AS revenue FROM order_items oi JOIN orders o ON o.id = oi.order_id JOIN products p ON p.id = oi.product_id JOIN categories cat ON cat.id = p.category_id WHERE o.status <> 'cancelled' GROUP BY cat.name ORDER BY revenue DESC;

▶ RunRestore the queryFresh database

Looks fine. Now the whole revenue, without categories:

SQLdatabase: shop

SELECT SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
WHERE o.status <> 'cancelled';

SELECT SUM(oi.quantity * oi.unit_price) AS revenue FROM order_items oi JOIN orders o ON o.id = oi.order_id WHERE o.status <> 'cancelled';

▶ RunRestore the queryFresh database

The six categories give 1,537 €, but the revenue is 1,697 €.

1. The categories add up to 1,537 €, but the whole revenue is 1,697 €. Where are the 160 €?

In the gift card sales: the gift card has no category, and JOIN categories threw it outIn the cancelled ordersA rounding error

Check

In a chain of JOINs, every link can quietly throw rows out. For a product without a category there is no match in categories. With a LEFT JOIN to the categories the sales stay, and COALESCE gives the missing category a name:

SQLdatabase: shop

SELECT COALESCE(cat.name, 'No category') AS category,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
LEFT JOIN categories cat ON cat.id = p.category_id
WHERE o.status <> 'cancelled'
GROUP BY cat.name
ORDER BY revenue DESC;

SELECT COALESCE(cat.name, 'No category') AS category, SUM(oi.quantity * oi.unit_price) AS revenue FROM order_items oi JOIN orders o ON o.id = oi.order_id JOIN products p ON p.id = oi.product_id LEFT JOIN categories cat ON cat.id = p.category_id WHERE o.status <> 'cancelled' GROUP BY cat.name ORDER BY revenue DESC;

▶ RunRestore the queryFresh database

Seven rows, and the total is 1,697 €. Whenever a report counts money, compare its total with the overall one. The difference shows the lost rows.

Try it

Revenue by city

Show the revenue by customer city, without the cancelled orders.

SQLdatabase: shop

Check▶ Run

The idea

A table joined to itself

In the categories, parent_id points to id in the same table. To see a category next to its parent, you join categories to itself. The table takes part twice, with two different short names:

SQLdatabase: shop

SELECT c.name AS category, parent.name AS parent
FROM categories c
LEFT JOIN categories parent ON parent.id = c.parent_id;

SELECT c.name AS category, parent.name AS parent FROM categories c LEFT JOIN categories parent ON parent.id = c.parent_id;

▶ RunRestore the queryFresh database

c is the category, parent is the same table seen as the parent. ON parent.id = c.parent_id says: the parent is the row whose id equals the category's parent_id. This is called a self join. Here the short names are required: without them name from the two copies cannot be told apart.

It is a LEFT JOIN so that “Food” and “Home”, which have no parent, stay.

Exercise

Two tasks

1. The new catalogue needs all 25 products with their category and parent category. The gift card must be on the list too.

The product, its category and the parent category, for all products:

SQLdatabase: shop

SELECT p.name AS product, cat.name AS category
FROM products p
JOIN categories cat ON cat.id = p.category_id;

SELECT p.name AS product, cat.name AS category FROM products p JOIN categories cat ON cat.id = p.category_id;

Check▶ Run

2. What is the revenue of the two top categories, “Food” and “Home”? Without the cancelled orders.

Revenue by top category:

SQLdatabase: shop

Check▶ Run

More precisely, if you are curious

The order of plain JOINs in a query does not change the result: the database itself chooses which table to start from. With LEFT JOIN the order matters, because “left” is everything put together before it.

A table joined to itself gives only one level up. A tree with many levels (a subcategory of a subcategory) is walked with WITH RECURSIVE, in the module on subqueries.

Test

Check yourself

1. A query joins orders, order_items, products and categories. How many JOIN … ON does it have?

FourThreeTwo

2. Why are the short names required in FROM categories c LEFT JOIN categories parent …?

Because the same table takes part twice, and without them c.name and parent.name cannot be told apartBecause LEFT JOIN requires themThey are not required

3. A report by category with JOIN categories loses the products without a category. How do you keep them?

With WHERE category_id IS NOT NULLWith ORDER BYWith LEFT JOIN categories and COALESCE for the name

4. The result has two name columns (the customer’s and the product’s). What do you do?

Nothing, the database will tell them apartRemove oneName them with AS: customer, product

Check

Start the lesson over

Related lessons

• Builds on: GROUP BY: a report by groups · Primary key and foreign key · JOIN: two tables in one query · LEFT JOIN: rows without a match too
• Continues in: UNION, INTERSECT, EXCEPT and CROSS JOIN · A report in steps: WITH · Trees and sequences: WITH RECURSIVE · Views
• Tasks on this topic: Joining tables