Quick recap: JOINs, GROUP BY, and aggregates
What we're recapping — and why
Advanced SQL builds directly on three fundamentals: JOINs, GROUP BY, and aggregate functions. If any of these feel shaky, this lesson will sharpen them. If they feel solid, skim it — the exercises at the end will confirm you're ready.
We'll use the ShopMetrics database throughout. If you haven't set it up yet, go back to the previous lesson.
JOINs
A JOIN combines rows from two tables based on a condition.
SELECT
o.order_id,
c.name AS customer,
o.created_at,
o.status
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'completed'
ORDER BY o.created_at DESC
LIMIT 5; order_id | customer | created_at | status
----------+---------------+----------------------------+-----------
187 | Customer 23 | 2025-11-14 09:12:44+00 | completed
203 | Customer 7 | 2025-11-12 17:33:01+00 | completed
...The JOIN types you need to know
| Type | Returns |
|---|---|
INNER JOIN (or just JOIN) |
Only rows with a match in both tables |
LEFT JOIN |
All rows from the left table, NULL for unmatched right |
RIGHT JOIN |
All rows from the right table, NULL for unmatched left |
FULL OUTER JOIN |
All rows from both, NULL where no match |
CROSS JOIN |
Every combination of rows (cartesian product) |
In practice, JOIN and LEFT JOIN cover 95% of use cases.