Logical Order of Evaluation

FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY

A query is written SELECT-first but evaluated logically as: FROM/JOIN → WHERE → GROUP BY → HAVING → window functions → SELECT → DISTINCT → ORDER BY → LIMIT. This explains why a SELECT alias cannot be used in WHERE (it does not exist yet) but can be used in ORDER BY, why aggregates cannot appear in WHERE (use HAVING), and why window functions cannot be filtered directly (wrap them in a subquery). The planner may execute differently, but results must match this logical order.

Using an alias in WHERE versus ORDER BY, run

I ran this with psql on PostgreSQL 16.2 against the course sample data (shown in the first section). The first query fails because WHERE is evaluated before SELECT creates the alias; ORDER BY can use it. Ravi and Meera tie on salary, so their order is not guaranteed without a tiebreaker.

-- Aliases from SELECT are not visible in WHERE (WHERE runs first)
SELECT name, salary * 12 AS yearly FROM employees WHERE yearly > 2000000;

-- ...but they are visible in ORDER BY (which runs last)
SELECT name, salary * 12 AS yearly FROM employees ORDER BY yearly DESC LIMIT 3;

Output:

ERROR:  column "yearly" does not exist
LINE 1: ... name, salary * 12 AS yearly FROM employees WHERE yearly > 2...
                                                             ^
 name  | yearly  
-------+---------
 Asha  | 3000000
 Meera | 2160000
 Ravi  | 2160000
(3 rows)

Repeat the expression or use a subquery

Write WHERE salary * 12 > ..., or wrap the query in a CTE and filter on the alias outside.

त्वरित जाँच: Where do you filter on an aggregate such as count(*) > 2?

• HAVING
• WHERE
• ON
• ORDER BY