Почему псевдоним из SELECT работает в ORDER BY, но не работает в WHERE?

Потому что части SQL-запроса обрабатываются не в том порядке, в котором мы их пишем.

Например:

SELECT price * quantity AS total
FROM orders
WHERE total > 1000;

Такой запрос в PostgreSQL не сработает: в момент обработки WHERE псевдонима total ещё нет.

Упрощённо порядок выглядит так:

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

Сначала формируется набор строк из FROM, затем WHERE отбрасывает ненужные строки, и только после этого вычисляются выражения из SELECT и появляются их псевдонимы.

Поэтому в WHERE приходится писать само выражение:

SELECT price * quantity AS total
FROM orders
WHERE price * quantity > 1000;

А ORDER BY выполняется уже после SELECT, поэтому псевдоним к этому моменту известен:

SELECT price * quantity AS total
FROM orders
ORDER BY total DESC;

Отсюда правило:

→ WHERE не видит псевдонимы из SELECT
→ ORDER BY видит

Это не особенность alias и не ограничение PostgreSQL ради ограничения. Причина в логическом порядке обработки частей SQL-запроса.

Если помнить этот порядок, становятся понятнее и другие вопросы: почему агрегаты нельзя использовать в WHERE, зачем нужен HAVING и почему некоторые выражения приходится повторять.

INTERSECT и EXCEPT — два оператора, которые иногда могут решить задачу проще, чем JOIN

Оба оператора сравнивают результаты двух SELECT и работают с наборами строк, часто еще говорят с множествами.

INTERSECT оставляет только то, что есть в обоих результатах.

Например, есть пользователи двух сервисов:

SELECT user_id FROM service_a
INTERSECT
SELECT user_id FROM service_b;

В результате останутся только user_id, которые встретились и в service_a, и в service_b.

EXCEPT работает наоборот: оставляет строки из первого SELECT, которых нет во втором.

SELECT user_id FROM service_a
EXCEPT
SELECT user_id FROM service_b;

Так мы получим пользователей service_a, которых нет в service_b.

Главное отличие:

→ INTERSECT = пересечение двух наборов

→ EXCEPT = первый набор минус второй

Для EXCEPT порядок запросов имеет значение:

A EXCEPT B — это не то же самое, что B EXCEPT A.

По умолчанию оба оператора удаляют дубликаты. Если повторяющиеся строки нужно учитывать, в PostgreSQL есть INTERSECT ALL и EXCEPT ALL.

Практический вывод: если задача звучит как «найти строки, которые есть в обоих результатах» — смотрим в сторону INTERSECT.

Если как «найти строки из первого результата, которых нет во втором» — EXCEPT.