Explain the order in which SQL actually evaluates a query, and why it differs from the order you write it in.
The engine works in a fixed order, and it is not the order that you write the clauses in.
Here is the order that the work happens in.
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 the columns of the answer. Then DISTINCT removes the duplicate rows. Then the ORDER BY clause sorts them, and then the limit takes the first few.
-- The 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 why does it differ from the order you write it in? Two reasons.
The first one is that the language has to be read from left to right, so the select list comes first in the text, even though the work it describes happens near the end. That is why you cannot use a select alias in the WHERE clause, and why you can use one in the ORDER BY clause.
The second one is that the engine does not really follow the list step by step. It looks at the tables and at the statistics about them, and it works out a plan. It may read the orders first and look up the customers afterwards, rather than doing the join in the order you wrote it. It may decide to use an index for the filter. The order above is the logical order, and it is the order that decides what the answer means, but the physical work can be done in a different order.
The next question in this topic shows two queries where the order changes the answer.