Why a SELECT alias is rejected in WHERE but accepted in ORDER BY, why window functions cannot be filtered in WHERE, and how far PostgreSQL, MySQL, SQLite and ClickHouse relax the rule.
The problem
SQL is written SELECT-first but evaluated FROM-first. Most 'column does not exist' errors, 'why can't I filter on my alias' questions and 'window function not allowed here' errors have one cause: the clause you wrote runs before the name you used exists. Engines also differ in how far they relax the rule, so the portable habit is to write for the strict order and treat relaxations as local conveniences.
How it works
- Logical order: `FROM`/`JOIN` (build the row set, apply `ON`), `WHERE`, `GROUP BY`, aggregates, `HAVING`, window functions, the `SELECT` list (expressions and aliases), `DISTINCT`, set operators, `ORDER BY`, `LIMIT`/`OFFSET`. It is a logical order only: the optimizer may reorder physical work as long as the result is identical.
- `WHERE` sees input columns only. Aggregates are rejected (PostgreSQL: `aggregate functions are not allowed in WHERE`; MySQL: `ERROR 1111 Invalid use of group function`; SQLite: `misuse of aggregate function SUM()`; ClickHouse: `ILLEGAL_AGGREGATION`) because groups do not exist yet; use `HAVING`.
- Window functions run after `WHERE`, `GROUP BY` and `HAVING`, so they cannot be filtered in `WHERE`: PostgreSQL `window functions are not allowed in WHERE`; MySQL `ERROR 3593 You cannot use the window function 'row_number' in this context.`; SQLite `misuse of window function ROW_NUMBER()`; ClickHouse `Window function ROW_NUMBER() OVER (ORDER BY id ASC) is found in WHERE in query. (ILLEGAL_AGGREGATION)`. Filter one level up in a subquery or CTE; ClickHouse also accepts a `QUALIFY` clause (observed on 26.9).
- Alias in `WHERE`: PostgreSQL rejects it (`column "double_pay" does not exist`), MySQL rejects it (`ERROR 1054 Unknown column 'double_pay' in 'where clause'`), SQLite 3.45, ClickHouse 26.9 and DuckDB 1.5 accept it. Do not rely on the relaxation if the query may move between engines.
- Alias in `GROUP BY` works on all four engines. Alias in `HAVING` is rejected by PostgreSQL (`column "n" does not exist`) and accepted by MySQL, SQLite and ClickHouse. An alias that wraps a window function is rejected in `HAVING` by MySQL (`ERROR 3594`).
- `ORDER BY` runs after the select list, so a bare alias works everywhere. An alias inside an expression (`ORDER BY n + 0`) fails on PostgreSQL (`column "n" does not exist`) and works on MySQL, SQLite and ClickHouse. When two select items share a name, PostgreSQL rejects `ORDER BY` on it (`ORDER BY "dept" is ambiguous`); MySQL does so when both items are plain columns (`Column 'dept' in order clause is ambiguous`) and otherwise sorts by the alias; SQLite sorts by the alias.
- Reusing an alias inside the same select list (`salary * 2 AS d, d + 1 AS e`) is accepted only by ClickHouse (and DuckDB); PostgreSQL gives `column "d" does not exist`, MySQL `Unknown column 'd' in 'field list'`, SQLite `no such column: d`.
- ClickHouse resolves a name to the alias first: `SELECT salary AS name FROM employees WHERE name = 'Ann'` compares the aliased `salary` and fails with `Cannot convert string 'Ann' to type Int32`, while PostgreSQL, MySQL and SQLite use the table column. Avoid aliases that shadow column names.
- `DISTINCT` runs before `ORDER BY`, so `SELECT DISTINCT region ... ORDER BY product` is ambiguous: PostgreSQL rejects it (`for SELECT DISTINCT, ORDER BY expressions must appear in select list`), MySQL rejects it (`ERROR 3065`), SQLite and ClickHouse accept it. `LIMIT` is last, so `ORDER BY ... LIMIT` sorts everything first.
Examples
Strict-order forms that run on all four engines: repeat the expression or wrap the query, use the alias in ORDER BY, filter a window result one level up.
-- A SELECT alias is not visible to WHERE in standard SQL: repeat the expression
-- or wrap the query. ORDER BY runs after SELECT, so it can use the alias.
CREATE TABLE employees (id INT, name VARCHAR(20), dept VARCHAR(10), salary INT NULL);
INSERT INTO employees VALUES (1, 'Ann', 'eng', 100), (2, 'Bob', 'eng', 100), (3, 'Cy', 'ops', 80), (4, 'Di', 'ops', NULL), (5, 'Ed', 'hr', 70);
SELECT name, salary * 2 AS double_pay FROM employees WHERE salary * 2 > 150 ORDER BY double_pay DESC, name;
-- => Ann | 200
-- => Bob | 200
-- => Cy | 160
SELECT name, double_pay FROM (SELECT name, salary * 2 AS double_pay FROM employees) t WHERE double_pay > 150 ORDER BY double_pay DESC, name;
-- => Ann | 200
-- => Bob | 200
-- => Cy | 160
-- Window results exist only after WHERE and GROUP BY: filter them one level up.
SELECT name, rn FROM (SELECT name, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM employees) t WHERE rn <= 2 ORDER BY rn;
-- => Ann | 1
-- => Bob | 2
DROP TABLE employees;Alias in WHERE and HAVING: accepted by SQLite and ClickHouse (PostgreSQL and MySQL reject the WHERE form, PostgreSQL also rejects HAVING).
CREATE TABLE employees (id INT, name VARCHAR(20), dept VARCHAR(10), salary INT NULL);
INSERT INTO employees VALUES (1, 'Ann', 'eng', 100), (2, 'Bob', 'eng', 100), (3, 'Cy', 'ops', 80), (4, 'Di', 'ops', NULL), (5, 'Ed', 'hr', 70);
SELECT name, salary * 2 AS double_pay FROM employees WHERE double_pay > 150 ORDER BY name;
-- => Ann | 200
-- => Bob | 200
-- => Cy | 160
SELECT dept, COUNT(*) AS n FROM employees GROUP BY dept HAVING n > 1 ORDER BY dept;
-- => eng | 2
-- => ops | 2
DROP TABLE employees;ClickHouse only: an alias used by a sibling select item and in GROUP BY.
CREATE TABLE employees (id INT, name VARCHAR(20), dept VARCHAR(10), salary INT NULL);
INSERT INTO employees VALUES (1, 'Ann', 'eng', 100), (2, 'Bob', 'eng', 100), (3, 'Cy', 'ops', 80), (4, 'Di', 'ops', NULL), (5, 'Ed', 'hr', 70);
SELECT salary * 2 AS double_pay, double_pay + 1 AS plus_one FROM employees WHERE id = 1;
-- => 200 | 201
SELECT upper(dept) AS d, COUNT(*) AS n FROM employees GROUP BY d ORDER BY d;
-- => ENG | 2
-- => HR | 1
-- => OPS | 2
DROP TABLE employees;In practice
- PostgreSQL — Follows the standard order strictly: aliases are visible to a bare ORDER BY item and to GROUP BY, nowhere else. Observed on 16.15.
- MySQL — Rejects aliases in WHERE but accepts them in GROUP BY, HAVING and ORDER BY expressions; has dedicated errors 3593 and 3594 for window functions used in the wrong clause. Observed on 8.0.46.
- ClickHouse — Aliases are visible everywhere in the query, including WHERE and sibling select items, and an alias shadows a same-named column. Supports QUALIFY. Observed on 26.9.
- SQLite — Accepts aliases in WHERE, GROUP BY, HAVING and ORDER BY but not inside the same select list. Observed on 3.45.1.
Trade-offs
Pros
- Writing to the strict order runs unchanged on every engine, including PostgreSQL and MySQL.
- A subquery or CTE that computes the alias once removes duplicated expressions and makes window filters possible.
- The order explains most 'not allowed here' errors without memorising them one by one.
Cons
- Wrapping a query to filter on an alias adds a level of nesting.
- Relaxed engines hide mistakes: a query that works on SQLite or ClickHouse can fail on PostgreSQL or MySQL.
- The logical order is not the physical plan; do not infer cost from it.