SQL error

Fix “Column Not Found” When Filtering on a SQL Alias

WHERE runs before SELECT assigns aliases, so the alias does not exist yet.

The symptom

You create a calculated column with as, then try to filter on that alias in WHERE. In many databases, the query is rejected with an error like "column not found" or "referenced column does not exist".

Not portable: many engines reject this

select product_name,
       price - cost as margin
from products
where margin > 10

Why it happens

SQL does not evaluate clauses from top to bottom as written. WHEREfilters rows before the SELECT list creates output aliases, so portable SQL should not depend on margin existing when the filter runs.

DuckDB, which powers SQLShed, allows this alias as a convenience. Many other engines do not. Treat the pattern as a portability trap, not a habit to build.

The fix

Either repeat the expression in WHERE, or compute it in a CTE or subquery and filter in the outer query. Repeating is fine for short expressions. A CTE is clearer when the calculation is longer or reused.

Right: repeat a short expression

select product_name,
       price - cost as margin
from products
where price - cost > 10

Right: filter the named result

with product_margins as (
  select product_name,
         price - cost as margin
  from products
)
select product_name, margin
from product_margins
where margin > 10

ORDER BY is different

Most databases let ORDER BY use a SELECT alias because ordering happens after the SELECT list is available. That exception does not apply toWHERE in portable SQL.

How other engines word it

Engine	Message
DuckDB	No error. DuckDB resolves `margin` in WHERE as a convenience.
PostgreSQL	`column "margin" does not exist`
SQL Server	`Invalid column name 'margin'.`
MySQL	`Unknown column 'margin' in 'where clause'`
SQLite	`no such column: margin`