Joins and Aggregates

Real-world questions almost always need data from more than one table. JOIN combines rows from related tables, and GROUP BY summarizes them.

INNER JOIN

INNER JOIN returns rows where the join condition matches in both tables. If a user has no orders, they will not appear in the result. This is the most common join — use it when you need only the records that exist on both sides.

SELECT u.name, o.total
FROM users u INNER JOIN orders o ON u.id = o.user_id;

LEFT and RIGHT JOIN

LEFT JOIN returns every row from the left table, even when there is no match on the right (the right-side columns come back as NULL). This is how you find users with zero orders. RIGHT JOIN is the mirror image. Most teams stick with LEFT JOIN because rewriting any RIGHT JOIN as a LEFT JOIN is just a matter of swapping the table order.

GROUP BY and Aggregates

GROUP BY collapses rows that share the same value in one or more columns. After grouping, you apply an aggregate function:

COUNT(*) — number of rows in the group
SUM(column) — total of a numeric column
AVG(column) — mean
MIN(column) / MAX(column) — extremes

SELECT user_id, COUNT(*) AS order_count, SUM(total) AS spent
FROM orders
GROUP BY user_id;

HAVING vs WHERE

WHERE filters rows before grouping; HAVING filters groups after. Use HAVING SUM(total) > 100 to keep only big-spending users. Mixing them up is a classic SQL bug.

Try It Yourself

Write a query that lists users along with their order count, including users who have zero orders
Find the average order total for each user, sorted highest first
Use HAVING to find users whose total spending exceeds $50