CTE

ELI5 — The Vibe Check

A CTE is a temporary named result set you define at the top of a query with the WITH keyword. It's like giving a subquery a name so you can reuse it. Makes complex queries readable instead of a nested mess of parentheses. Some people call them 'WITH clauses.'

Real Talk

A CTE (Common Table Expression) is a named temporary result set defined using the WITH clause that exists only within the scope of a single SQL statement. CTEs improve readability, enable recursive queries, and can be referenced multiple times. They are supported by PostgreSQL, MySQL 8+, SQL Server, and SQLite. Non-recursive CTEs are typically expanded inline by the query planner.

Show Me The Code

WITH active_users AS (
  SELECT id, name FROM users WHERE status = 'active'
),
recent_orders AS (
  SELECT user_id, COUNT(*) as order_count
  FROM orders WHERE created_at > NOW() - INTERVAL '30 days'
  GROUP BY user_id
)
SELECT u.name, COALESCE(o.order_count, 0)
FROM active_users u LEFT JOIN recent_orders o ON u.id = o.user_id;

When You'll Hear This

"Use a CTE instead of a subquery — it's much easier to read." / "Recursive CTEs are perfect for traversing tree structures like org charts."