Unit 02.02: Logical order: when an alias is visible

Unit ID: SQL-M02-U03 - Estimated active time: 12-15 minutes Objective: state the logical evaluation order and use it to explain common errors.

SQL does not run in the order you read it

You write SELECT … FROM … WHERE … GROUP BY … ORDER BY. The database evaluates roughly:

FROM   ->  WHERE  ->  GROUP BY  ->  HAVING  ->  SELECT  ->  ORDER BY  ->  LIMIT

Rows are found, filtered, grouped, then the output columns are produced, then the result is sorted.

What the order predicts

Because WHERE runs before SELECT, the standard says a select-list alias is not yet available to it:

SELECT order_total * 0.18 AS gst
FROM orders
WHERE gst > 500;

In PostgreSQL, SQL Server, and Oracle this errors - "column gst does not exist". ORDER BY, which runs after SELECT, accepts the alias everywhere:

SELECT order_total * 0.18 AS gst
FROM orders
ORDER BY gst DESC
LIMIT 3;

DuckDB is more permissive - and that is a trap

Run the first query in this course's DuckDB database and it works, returning 480 rows. DuckDB (and MySQL in some cases) extends the standard to resolve select-list aliases in WHERE as a convenience.

That is genuinely useful, and it is also how people write a query that runs fine locally and fails the moment it is moved to a warehouse. The logical order has not changed; DuckDB is doing you a favour that PostgreSQL will not.

Practical rule: rely on aliases in ORDER BY freely - that is standard. Avoid relying on them in WHERE if the query might ever run on another engine.

Two ways to filter on a computed value

Repeat the expression:

SELECT order_id, order_total * 0.18 AS gst
FROM orders
WHERE order_total * 0.18 > 500;

Or compute it once in a subquery and filter outside - clearer as the expression grows:

SELECT order_id, gst
FROM (
  SELECT order_id, order_total * 0.18 AS gst
  FROM orders
) t
WHERE gst > 500;

Module 6 builds this pattern properly with CTEs.

Non-example

This is not an ordering problem:

-- expect-error: the column is misspelled on purpose
SELECT order_id FROM orders WHERE ordertotal > 500;   -- ERROR

That is a typo - the column is order_total. Read the error text: "no such column: ordertotal" points at a name that does not exist anywhere, whereas the alias error points at a name that exists in your SELECT list. The message tells you which mistake you made.

Practice

Predict what each does in DuckDB, then predict what it would do in PostgreSQL. Run them and check.

1. SELECT order_total AS t FROM orders WHERE t > 4000;
2. SELECT order_total AS t FROM orders ORDER BY t DESC;
3. SELECT status, COUNT(*) AS n FROM orders GROUP BY status HAVING n > 100;

Takeaway

The logical order predicts which clauses can see a select-list alias: ORDER BY can, WHERE standardly cannot. DuckDB relaxes the WHERE case, so a query that runs here may still fail on PostgreSQL - knowing the order is what lets you spot that before it happens.

---

Mark complete