Session 8 · Joins on an e-commerce database#
The big idea#
Online shops are the classic joins problem. Customers place orders; each order is for a
product. Because one order row only stores two ids, every useful report (“who bought what, and
how much did they spend?”) needs joins across all three tables, plus SUM and GROUP BY to add
things up.
Everyday example: a shop’s bill book. Each carbon copy says “customer no. 7, item no. 3, qty 2”. To write the monthly statement you look up customer 7 in the customer register, item 3 in the price list, and multiply. SQL does that lookup for every row in a single query.
Setup and queries#
code/08-ecommerce.sql creates three tables and fills them (three customers, four products, three
orders), then runs three reports.
\set QUIET on
SET client_min_messages = warning;
DROP TABLE IF EXISTS orders, products, customers CASCADE;
CREATE TABLE customers (customer_id SERIAL PRIMARY KEY, customer_name VARCHAR(50));
CREATE TABLE products (product_id SERIAL PRIMARY KEY, product_name VARCHAR(50), price DECIMAL(10,2));
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INT REFERENCES customers(customer_id),
product_id INT REFERENCES products(product_id),
quantity INT
);
INSERT INTO customers (customer_name) VALUES ('Alice'), ('Bob'), ('Charlie');
INSERT INTO products (product_name, price) VALUES ('Laptop', 1000.00), ('Smartphone', 500.00), ('Headphones', 100.00), ('Charger', 20.00);
INSERT INTO orders (customer_id, product_id, quantity) VALUES (1, 1, 1), (1, 3, 2), (2, 2, 1);
\echo '-- every order with the customer and product names'
SELECT o.order_id, c.customer_name, p.product_name, o.quantity
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN products p ON p.product_id = o.product_id
ORDER BY o.order_id;
\echo '-- order value'
SELECT c.customer_name, SUM(p.price * o.quantity) AS total_spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
LEFT JOIN products p ON p.product_id = o.product_id
GROUP BY c.customer_name
ORDER BY total_spent DESC NULLS LAST;
\echo '-- products nobody has bought'
SELECT p.product_name
FROM products p LEFT JOIN orders o ON o.product_id = p.product_id
WHERE o.order_id IS NULL;
-- every order with the customer and product names
order_id | customer_name | product_name | quantity
----------+---------------+--------------+----------
1 | Alice | Laptop | 1
2 | Alice | Headphones | 2
3 | Bob | Smartphone | 1
(3 rows)
-- order value
customer_name | total_spent
---------------+-------------
Alice | 1200.00
Bob | 500.00
Charlie |
(3 rows)
-- products nobody has bought
product_name
--------------
Charger
(1 row)
What the reports teach#
Every order, with names.
ordersis the hub; twoJOINs fetch the customer name and product name. The order table alone only had ids.Total spent per customer.
p.price * o.quantityis the value of each order line,SUMadds them per customer,GROUP BY customer_namemakes one row per customer. Alice bought a laptop (1000) and two headphones (2 x 100), so 1200.00.Charlie appears with an empty total. Because both joins are
LEFT JOIN, a customer with no orders is kept; the total isNULL, not 0.NULLS LASTkeeps him at the bottom. UseCOALESCE(SUM(...), 0)if you want a 0 shown.Products nobody bought. The anti-join pattern again: left join and keep rows where the right side is
NULL. Only the Charger is returned.
Reading a three-table join step by step#
Take the first report. Start with FROM orders o: three rows, each holding only ids (customer 1,
product 1, quantity 1). The first JOIN customers c ON c.customer_id = o.customer_id looks up each
customer id in the customers table and attaches the name, so the rows are now wider. The second
JOIN products p ON p.product_id = o.product_id does the same for the product, attaching its name and
price. Last, SELECT picks the columns to show and ORDER BY sorts.
You can build any report this way: begin with the table that has one row per thing you are counting (here, orders), then join in each table that holds a fact you need. If the row count changes unexpectedly between steps, you have found a bug before it reaches a customer’s invoice.
Common mistakes#
Joining in the wrong order or on the wrong columns.
ON o.customer_id = p.product_idruns without error and returns nonsense. Name the keys the same way in both tables and read theONaloud.Summing after a join that duplicates rows. If one order matched two rows in another table,
SUMwould double count. Check row counts before and after a join.Using
INNER JOINfor “all customers” reports. Customers with no orders vanish.Storing the price only on
products. If the price changes next year, old orders change too. Real shops copy the price onto the order line when it is placed.Float for money. Use
NUMERIC/DECIMAL, as inprice DECIMAL(10,2).
Hands-on exercises#
Exercise 1. Show each customer with the number of orders they placed (0 for Charlie).
Solution
SELECT c.customer_name, count(o.order_id) AS orders
FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_name
ORDER BY orders DESC, c.customer_name;
Alice has 2, Bob has 1, Charlie has 0.
Exercise 2. Show total revenue per product, including products never sold (revenue 0).
Solution
SELECT p.product_name, COALESCE(SUM(p.price * o.quantity), 0) AS revenue
FROM products p LEFT JOIN orders o ON o.product_id = p.product_id
GROUP BY p.product_name
ORDER BY revenue DESC;
COALESCE swaps the NULL for 0. The Charger shows revenue 0.
Exercise 3. Find the customer who spent the most.
Solution
SELECT c.customer_name, SUM(p.price * o.quantity) AS total_spent
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN products p ON p.product_id = o.product_id
GROUP BY c.customer_name
ORDER BY total_spent DESC
LIMIT 1;
Alice, with 1200.00.
Practice#
Take the Session 8 quiz. Then try the same ideas on your own data, or on the DVD Rental database from the reference section.