Joins, grouping and subqueries

Data is spread across tables

A well-designed database stores each fact once (this is called normalization). An order doesn't repeat the customer's name and email — it stores customer_id, and the customer's details live in customers. To answer real questions, you combine tables again with joins.

customers (id, name, email, city)
    │ 1
    │        orders (id, customer_id, ordered_at, status)
    └──── *         │ 1
                    │       order_items (order_id, product_id, quantity, unit_price)
                    └──── *            │ *
                                       │       products (id, name, category, price, stock)
                                       └──── 1

INNER JOIN

Pair up rows that match on both sides:

SELECT o.id, c.name, o.status
FROM orders o
JOIN customers c ON c.id = o.customer_id;   -- JOIN means INNER JOIN

o and c are aliases to keep things short. Rows without a match on either side are dropped.

LEFT JOIN

Keep every row from the left table, filling in NULL where there's no match:

SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;
-- customers with no orders appear once, with order_id = NULL

That makes "who has never ordered?" easy: add WHERE o.id IS NULL.

Aggregates

Function	Returns
`COUNT(*)`	Number of rows
`COUNT(column)`	Number of non-NULL values
`SUM(x)`, `AVG(x)`	Total, average
`MIN(x)`, `MAX(x)`	Smallest, largest

SELECT COUNT(*), AVG(price), MAX(price) FROM products;

GROUP BY and HAVING

GROUP BY collapses rows that share a value into groups, and aggregates are computed per group:

SELECT category, COUNT(*) AS products, ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category
ORDER BY products DESC;

To filter groups, use HAVING (WHERE filters rows before grouping):

SELECT c.name, COUNT(*) AS orders
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status <> 'cancelled'      -- filter rows first
GROUP BY c.id
HAVING COUNT(*) > 1;               -- then filter groups

Subqueries

A query can use the result of another query:

-- Products more expensive than average
SELECT name, price FROM products
WHERE price > (SELECT AVG(price) FROM products);
 
-- Customers who ordered a Laptop
SELECT name FROM customers
WHERE id IN (
  SELECT o.customer_id
  FROM orders o
  JOIN order_items i ON i.order_id = o.id
  JOIN products p ON p.id = i.product_id
  WHERE p.name = 'Laptop'
);

Order of execution

SQL is written SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT, but logically evaluated as FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. That's why you can't use a SELECT alias in WHERE, but you can in ORDER BY.

Practice

Interactive

SQL sandbox

A real SQLite database running in your browser — a small online shop

Challenge

For each order, show the order id, the customer's name and the order status.

Hint

JOIN customers ON customers.id = orders.customer_id.

Loading editor…

Ctrl + Enter to run

Key takeaways

Normalized data is spread across tables; JOIN recombines it via keys.
INNER JOIN keeps matches only; LEFT JOIN keeps all left rows with NULLs for missing matches.
GROUP BY + aggregates summarize; HAVING filters groups, WHERE filters rows.
Subqueries let one query use another's result.