How a query actually runs

SQL is written in one order and run in another. Knowing both explains most beginner errors.

You always write a query’s clauses in the same fixed order, starting with SELECT. The database runs them in a different order, starting with FROM.

You’ll meet GROUP BY, HAVING and JOIN later in this track. For now, focus on the idea: SELECT runs near the end, not the start.

Why it matters: aliases in WHERE

This looks reasonable but fails on most databases:

SELECT name, amount * 1.03 AS new_amount
FROM opportunities
WHERE new_amount > 50000;   -- error: new_amount doesn't exist yet

WHERE runs at step 2. The alias new_amount is only created at step 5, in SELECT. Repeat the calculation instead:

SELECT name, amount * 1.03 AS new_amount
FROM opportunities
WHERE amount * 1.03 > 50000;

Why it matters: aliases in ORDER BY

ORDER BY runs after SELECT, so it can use the alias:

SELECT name, amount * 1.03 AS new_amount
FROM opportunities
ORDER BY new_amount DESC;

Good to know: SQLite, which the sandbox below and the practice test run on, is forgiving and lets you use a SELECT alias in WHERE. SQL Server, PostgreSQL and MySQL don’t. Write it the portable way and your SQL works everywhere.

Snowflake notes

Snowflake is forgiving too: it lets you use a SELECT alias in WHERE, and even reuse an alias later in the same SELECT list. It’s convenient, but queries written that way break when moved to SQL Server or PostgreSQL. Snowflake also adds a QUALIFY clause, which runs after window functions. The window function lessons cover it.

Try it

Show each opportunity’s name and its amount after a 3% increase, named new_amount. Keep only rows where the new amount is over $100,000, largest first. Write the filter the portable way, without using the alias in WHERE.

Write it in the sandbox below, then press Check answer.

Back to the top of the lesson