Intermediate LevelSQL & MySQL
Are CTEs materialized or inlined during query optimization?
❓ Interview Prompt: Explain CTE optimization, CTE Inlining (MySQL 8 / PostgreSQL 12+), `MATERIALIZED` vs `NOT MATERIALIZED` hints, and predicate pushdown.
Older database engines always materialized CTEs into temporary tables on disk. Modern query engines optimize CTEs intelligently:
Inlining & Optimization:
•
CTE Inlining: The optimizer merges non-recursive CTE definitions directly into the main query Abstract Syntax Tree (AST), applying Predicate Pushdown and index scans across joined tables.
Code Implementation Example
sql
-- Modern SQL Optimizer inlining CTE execution
WITH filtered_orders AS (
SELECT user_id, total_amount FROM orders WHERE created_at >= '2026-01-01'
)
SELECT u.email, f.total_amount
FROM users u
JOIN filtered_orders f ON u.id = f.user_id;💡Inlining CTE execution paths in modern query engines.
🎯 Interview Key Takeaway
Modern query optimizers inline non-recursive CTEs into the main query AST to apply predicate pushdown and index lookups.
#SQL#CTE#Optimizer#Inlining#Intermediate
Back to all SQL & MySQL Questions→