Common Table Expression

ELI5 — The Vibe Check

A CTE (WITH clause) lets you name a sub-query and use it later, like setting a variable in your SQL. Instead of nesting queries five levels deep until your eyes bleed, you break it into readable named steps. It's SQL's way of saying 'let me explain this step by step.'

Real Talk

A Common Table Expression (CTE) is a named temporary result set defined within a WITH clause that exists only for the duration of a single query. CTEs improve readability, enable recursive queries, and can be referenced multiple times within the main query. Most modern databases optimize CTEs as inline subqueries.

Show Me The Code

WITH active_users AS (
  SELECT id, name FROM users WHERE last_login > NOW() - INTERVAL '30 days'
),
user_orders AS (
  SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id
)
SELECT a.name, COALESCE(o.order_count, 0)
FROM active_users a
LEFT JOIN user_orders o ON a.id = o.user_id;

When You'll Hear This

"Use a CTE to make that monster query actually readable." / "CTEs are basically named subqueries you can reuse in the same statement."