On the frontend you play with JSON - nested objects, arrays inside arrays. The SQL world is the opposite: data lives in separate, flat tables. Orders in the orders table, their items in another table, users in yet another. The question is, when you need it, how do you join this scattered data back together? The answer is JOIN.
INNER JOIN vs LEFT JOIN - the difference is the point
INNER JOIN only brings rows whose match exists in both tables. But if you want to know 'which users have not placed a single order yet', INNER JOIN will not work there - because you are looking for them precisely because there is no match. That needs LEFT JOIN: everyone from the left table comes, with the right table's data when there is a match, NULL when there is not.
-- How much each user has ordered (users with no orders still listed)
SELECT u.name, COUNT(o.id) AS total_orders, COALESCE(SUM(o.total), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name
ORDER BY total_spent DESC;GROUP BY squeezes all of one user's orders into a single row, and functions like COUNT/SUM calculate over that squeezed group. HAVING is the filter after GROUP BY - like 'only those who ordered more than 5 times'. The easy rule to remember: WHERE filters rows before grouping, HAVING filters groups after grouping.
Subqueries - a question inside a question
When the answer depends on another answer, that is a subquery. Like 'orders bigger than the average order' - first you have to find the average, then compare. But do not fear subqueries when you see them - many subqueries can be written as JOINs, and JOINs are usually faster. In interviews, this judgement is exactly what gets looked at.
-- Products pricier than the average price
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);The mistake everyone makes
The most common mistake: forgetting to write the JOIN condition in ON, or giving the wrong column. Then every row matches every row and the result runs into hundreds of thousands of rows (Cartesian product). Right after writing a JOIN, look at the result with a small limit first.
Today's practice
Add MySQL or PostgreSQL to the week 1 project - build users, orders, order_items tables. Write three queries: (1) total spent per user, (2) a list of users who never ordered, (3) the 5 best-selling products.
How to say it in an interview
With JOINs I bring data from separate tables together - INNER when I want only rows with a match, LEFT when I want to keep everyone from the main table and look for matches. With GROUP BY I calculate aggregates over groups, and I know the difference between WHERE and HAVING - one works before grouping, the other after.