Real questions rarely fit in one table. "Who spent the most?", "Which category sells best?", "Which products are above average price?" — these need joins, grouping and subqueries.
Learning objectives
- Combine tables with
INNER JOINandLEFT JOIN. - Summarise with
GROUP BYand filter groups withHAVING. - Write subqueries and
EXISTSchecks. - Understand what an index does and when it helps.
1. JOIN
The shop schema from the previous assignment: users, orders, order_items, products.
INNER JOIN — only rows that match on both sides:
SELECT o.id AS order_id, u.username, o.total_price
FROM orders o
INNER JOIN users u ON o.user_id = u.id
ORDER BY o.id;
Three-table join — the order line, with the buyer and the product:
SELECT
u.username,
p.name AS product,
oi.quantity,
oi.unit_price,
(oi.quantity * oi.unit_price) AS subtotal
FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.id
INNER JOIN users u ON o.user_id = u.id
INNER JOIN products p ON oi.product_id = p.id;
LEFT JOIN — keep every row from the left table, even without a match:
SELECT u.username, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;
With INNER JOIN, a user with no orders disappears. With LEFT JOIN, they appear with 0. That difference is usually the bug in a "top customers" report.
2. GROUP BY and HAVING
SELECT u.id, u.username, SUM(oi.quantity * oi.unit_price) AS spent
FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.id
INNER JOIN users u ON o.user_id = u.id
GROUP BY u.id, u.username
ORDER BY spent DESC;
WHERE filters rows before grouping; HAVING filters groups after aggregation:
SELECT u.id, u.username, SUM(oi.quantity * oi.unit_price) AS spent
FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.id
INNER JOIN users u ON o.user_id = u.id
GROUP BY u.id, u.username
HAVING spent > 100000
ORDER BY spent DESC;
Every column in SELECT that is not an aggregate must appear in GROUP BY. SQLite is lenient about this; PostgreSQL is not. Write standard SQL now.
3. Subqueries
-- products priced above the average
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);
-- users who have never ordered
SELECT username
FROM users
WHERE id NOT IN (SELECT user_id FROM orders);
-- same idea with EXISTS
SELECT u.username
FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
EXISTS short-circuits on the first match and handles NULL more predictably than NOT IN.
4. Indexes
An index is a sorted structure that lets the database find rows without scanning the table.
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_posts_created_at ON posts(created_at);
Index columns used in WHERE, JOIN ... ON and ORDER BY on large tables. Every index slows writes and uses space, so do not index everything.
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE user_id = 1;
Look for SCAN versus SEARCH ... USING INDEX. If a hot query scans a big table, add the index the plan is asking for.
5. Transactions
A transaction groups statements so they succeed or fail together:
BEGIN;
INSERT INTO orders (user_id, total_price) VALUES (1, 5000);
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (last_insert_rowid(), 2, 1, 5000);
COMMIT;
If the second insert fails, ROLLBACK leaves no orphaned order. Money and inventory changes always belong in a transaction.
Practice
Write the reporting queries in the assignment against the shop schema. Verify the joins by comparing counts with a plain SELECT COUNT(*).