SQL - Using MAX in a WHERE clause

Question

Assume value is an int and the following query is valid:

SELECT blah
FROM table
WHERE attribute = value

Though MAX(expression) returns int, the following is not valid:

SELECT blah
FROM table
WHERE attribute = MAX(expression)

OF course the desired effect can be achieved using a subquery, but my question is why was SQL designed this way - is there some reason why this sort of thing is not allowed? Students coming from programming languages where you can always replace a data-type by a function call that returns that type find this issue confusing. Is there an explanation one can give them rather than just saying "that's the way it is"?

Answer 1

It's just because of the order of operations of a query.

1. FROM clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. SELECT clause
6. ORDER BY clause

WHERE just filters the rows returned by FROM. An aggregate function like MAX() can't have a result returned because it hasn't even been applied to anything.

That's also the reason, why you can't use aliases defined in the SELECT clause in a WHERE clause, but you can use aliases defined in FROM clause.