-- Assuming CTE is always materialized

WITH expensive_calc AS (

SELECT department, AVG(salary) as avg_sal

FROM employees

GROUP BY department

)

SELECT * FROM expensive_calc WHERE avg_sal > 70000

UNION ALL

SELECT * FROM expensive_calc WHERE avg_sal <= 70000;

-- Assumption: CTE computed once

-- Reality: Might execute twice (database-dependent)

-- PostgreSQL: Force materialization

WITH expensive_calc AS MATERIALIZED (

SELECT department, AVG(salary) as avg_sal

FROM employees

GROUP BY department

)

SELECT * FROM expensive_calc WHERE avg_sal > 70000

UNION ALL

SELECT * FROM expensive_calc WHERE avg_sal <= 70000;

-- Or use temp table for guaranteed single execution

CREATE TEMP TABLE expensive_calc AS

SELECT department, AVG(salary) as avg_sal

FROM employees

GROUP BY department;

SELECT * FROM expensive_calc WHERE avg_sal > 70000

UNION ALL

SELECT * FROM expensive_calc WHERE avg_sal <= 70000;

CTEs are not always materialized. If CTE is referenced multiple times, it might execute multiple times depending on database optimizer. PostgreSQL allows MATERIALIZED hint to force single execution. SQL Server usually materializes. MySQL typically inlines. Always check execution plan.

Check execution plan with EXPLAIN. Use MATERIALIZED hint in PostgreSQL if CTE used multiple times.

Medium

CTE may execute multiple times without materialization

-- Using multiple WITH keywords

WITH cte1 AS (SELECT * FROM employees)

WITH cte2 AS (SELECT * FROM departments) -- WRONG!

SELECT * FROM cte1 JOIN cte2 ON ...;

-- Syntax error: unexpected WITH

-- Separate multiple CTEs with commas

WITH

cte1 AS (SELECT * FROM employees),