SELECT, and what a column really is

A SELECT names expressions. A bare column is the simplest expression there is, but it is not a different kind of thing from total * 2 or lower(name) — and the moment you stop reading a select list as “a list of columns” and start reading it as “a list of expressions, evaluated once per row”, a great deal of SQL stops needing to be memorised.

SELECT o.id, o.total, o.total * 2 AS doubled, lower(c.city) FROM orders o JOIN customers c ON c.id = o.customer_id ORDER BY o.id LIMIT 5

Notice the last column is called lower. Postgres names an unaliased expression after the function that produced it, or ?column? when there is nothing to name it after. That is not a detail worth memorising, but it is worth recognising: a result set full of ?column? is telling you the query has expressions nobody has named yet.

The order clauses run in

Clauses are written in one order and evaluated in another. This is the single most useful piece of trivia in the language, because almost every “why can’t I use my alias there” question is answered by it.

1. FROM — decide which rows exist at all, joins included.
2. WHERE — throw rows away. Aliases from the select list do not exist yet.
3. GROUP BY — collapse what survived into groups.
4. HAVING — throw groups away.
5. SELECT — evaluate the expressions, and only now do the aliases exist.
6. ORDER BY — which is why it can use an alias.
7. LIMIT / OFFSET — take a slice of what is left.

SELECT total * 2 AS doubled FROM orders ORDER BY doubled DESC NULLS LAST LIMIT 3