0

SELECT, projection & expressions

SQLused in ~100% of apps

This is your first SQL, and the query you will write more than any other. Every `supabase.from('posts').select(...)` you ever type is compiled straight into one of these — so the habits you build in the next ten minutes are the habits your whole app inherits. Two of them matter enormously: naming your columns instead of grabbing `*`, and knowing that Postgres does not read your query top to bottom.

Core idea. SELECT chooses which columns come back and lets you compute new ones on the fly — an alias to rename them, an expression to transform them, CASE to branch, COALESCE to fill in a NULL. But the clauses do not run in the order you wrote them: FROM runs first, then WHERE, then SELECT, then ORDER BY. That single fact explains why a column alias works in ORDER BY and blows up in WHERE.
Playground, this isn't a screenshot
execution orderFROM → WHERE → SELECT → ORDER BYnot top to bottom

Sort the finished rows. `boosted` already exists, so naming it here is fine.

The rule: an alias defined in SELECT can be used in ORDER BY (which runs after it) but not in WHERE (which runs before it). In WHERE you must repeat the whole expression.
↑ play a little first, then dig in