Joins and GROUP BY

Once you can read one table, interviews move to two tables and to totals. These two ideas, joins and grouping, cover most of what junior data and backend roles need.

Joins

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

INNER JOIN returns only customers who have orders. To keep every customer and show NULL where there is no order, use LEFT JOIN. The question "which customers have never ordered?" is LEFT JOIN ... WHERE o.id IS NULL.

GROUP BY

SELECT c.name, COUNT(o.id) AS orders, SUM(o.total) AS spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name
HAVING SUM(o.total) > 1000
ORDER BY spent DESC;

Every column in SELECT that is not inside an aggregate must appear in GROUP BY. WHERE filters rows before grouping; HAVING filters the groups after.

Aggregates

COUNT(*) counts rows; COUNT(column) ignores NULLs in that column. SUM, AVG, MIN and MAX ignore NULLs too, which is why an average can look wrong when half the values are missing.

Keys and indexes

A primary key uniquely identifies a row. A foreign key in orders points at the customer's primary key and stops orphan orders. An index on customer_id makes the join fast; without it the database scans the whole orders table for each customer.

In interviews, say the join type and why. "LEFT JOIN so we keep customers with no orders, then GROUP BY name" is a complete answer.