Reusable Expressions: Derived Tables and CTEs

Filtering on a calculated value means computing it before WHERE runs.

The problem this solves

The Execution Order Model explains why WHERE can't see a SELECT alias — SELECT hasn't run yet. The fix is to compute the value in a stage that runs before WHERE: wrap it in a subquery that behaves like a table.

-- derived table
SELECT *
FROM (
  SELECT FirstName, Salary + Bonus AS TotalPay
  FROM Employees
) AS e
WHERE TotalPay > 70000;

-- common table expression
WITH pay AS (
  SELECT FirstName, Salary + Bonus AS TotalPay
  FROM Employees
)
SELECT * FROM pay WHERE TotalPay > 70000;

The inner query runs first and produces something that behaves like a real table, so by the time the outer WHERE runs, TotalPay is a genuine column rather than a forward reference.

Rule

Default to the CTE for anything that will be read again: it reads top to bottom in the order it executes, and it can be referenced more than once or chained into further CTEs. Reserve the derived table for a single small, throwaway nesting.

Is a CTE a temporary table?

No. It's a named subquery, and in most engines it's inlined into the plan rather than materialised. Postgres inlines CTEs by default since version 12 unless the query explicitly forces a materialisation barrier with the MATERIALIZED keyword.

Does repeating the expression compute it twice?

No — if a value is written once in WHERE and once in SELECT without a CTE, the optimiser still computes it once per row. The reason to prefer a CTE isn't performance; it's that a long expression written twice will eventually drift out of sync during maintenance.

Try it

WITH pay AS (
  SELECT FirstName, Department, Salary + COALESCE(Bonus, 0) AS TotalPay
  FROM Employees
)
SELECT * FROM pay WHERE TotalPay > 70000 ORDER BY TotalPay DESC;