Walk me through what the engine actually does when it runs a SELECT statement.
The engine takes the statement apart into steps, works out the cheapest way to do each one, and then runs the steps in an order that gives the right answer.
Here is the logical order, and it is worth knowing because the order of the clauses in the text is not the order in which the work happens.
First the rows come out of the tables, and that is where the joins happen. Then the WHERE clause throws away the rows that do not match. Then the GROUP BY clause puts the remaining rows into groups. Then the HAVING clause throws away the groups that do not match. Then the SELECT clause works out which columns the answer has. Then DISTINCT removes the duplicates. Then the ORDER BY clause sorts them, and then the limit takes the first few.
-- This is the order the work happens in, whatever order you write it in.
SELECT c.name, SUM(o.amount) AS total
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'PAID'
GROUP BY c.id, c.name
HAVING SUM(o.amount) > 100
ORDER BY total DESC
LIMIT 10;
So what does the engine really do? It does not follow that list step by step. It looks at the tables, looks at the statistics about them, and works out a plan. It may decide to read the orders table first and look up each customer afterwards, rather than doing the join in the order that you wrote it. It may decide to use an index to find the paid orders instead of reading the whole table.
The one thing that it always respects is the order of the clauses in the language. That is why you cannot put WHERE after GROUP BY, and why you cannot use a column alias that you defined in the SELECT clause inside the WHERE clause, because the filter happens before the alias exists.
The next question in this topic asks for a simple select with two renamed columns.