SQL WITH Clause (CTE): Complete Guide with Examples

Write Your First SQL Query in 10 Seconds—Free

What is the SQL WITH Clause?

The WITH clause, also known as Common Table Expression (CTE), is a powerful SQL feature that lets you define temporary named result sets within a query. Think of it as creating temporary tables that exist only for the duration of your query.

WITH cte_name AS ( SELECT columns FROM table WHERE conditions ) SELECT * FROM

Why Use CTEs?

Before CTEs: Subquery Nightmare

SELECT d.department_name, (SELECT AVG(salary) FROM employees e WHERE e.department_id = d.id) as avg_salary, (SELECT COUNT(*) FROM employees e WHERE e.department_id = d.id) as employee_count, (SELECT MAX(salary) FROM employees e WHERE e.department_id = d.id) as max_salary FROM departments d WHERE (SELECT AVG(salary) FROM employees e WHERE e.department_id = d.id) > 50000

After CTEs: Clean and Readable

WITH department_stats AS ( SELECT department_id, AVG(salary) as avg_salary, COUNT(*) as employee_count, MAX(salary) as max_salary FROM employees GROUP BY department_id ) SELECT d.department_name, ds.avg_salary, ds.employee_count, ds.max_salary FROM departments d JOIN department_stats ds ON d.id = ds.department_id WHERE ds.avg_salary > 50000

CTE Syntax Across Databases

CTEs are supported by all major databases with consistent syntax:

Database	Support	Version
PostgreSQL	Full	8.4+
MySQL	Full	8.0+
SQL Server	Full	2005+
Oracle	Full	9i+
SQLite	Full	3.8.3+
Snowflake	Full	All versions
BigQuery	Full	All versions

Basic CTE Examples

Example 1: Simple CTE

WITH active_customers AS ( SELECT customer_id, name, email FROM customers WHERE status = ‘active’ AND last_order_date >= CURRENT_DATE - INTERVAL ‘90 days’ ) SELECT * FROM active_customers ORDER BY

Example 2: CTE with Aggregation

WITH monthly_sales AS ( SELECT DATE_TRUNC(‘month’, order_date) as month, SUM(total_amount) as revenue, COUNT(*) as order_count FROM orders WHERE order_date >= ‘2025-01-01’ GROUP BY DATE_TRUNC(‘month’, order_date) ) SELECT month, revenue, order_count, revenue / order_count as avg_order_value FROM monthly_sales ORDER BY month

Example 3: Multiple CTEs

WITH high_value_customers AS ( SELECT customer_id FROM orders GROUP BY customer_id HAVING SUM(total_amount) > 10000 ), recent_orders AS ( SELECT customer_id, order_id, order_date, total_amount FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL ‘30 days’ ) SELECT c.name, c.email, ro.order_date, ro.total_amount FROM customers c JOIN high_value_customers hvc ON c.id = hvc.customer_id JOIN recent_orders ro ON c.id = ro.customer_id ORDER BY ro.order_date DESC

Advanced CTE Patterns

Recursive CTEs

Recursive CTEs are powerful for hierarchical data like org charts, category trees, or bill of materials.

Syntax:

WITH RECURSIVE cte_name AS ( — Base case (anchor member) SELECT columns FROM table WHERE condition

UNION ALL

\-- Recursive case
SELECT columns FROM table
JOIN cte\_name ON relationship

) SELECT * FROM

Example: Organization Hierarchy

WITH RECURSIVE org_tree AS ( — Base case: top-level managers (no manager_id) SELECT id, name, title, manager_id, 1 as level, name as path FROM employees WHERE manager_id IS NULL

UNION ALL

\-- Recursive case: employees with managers
SELECT
    e.id,
    e.name,
    e.title,
    e.manager\_id,
    ot.level + 1,
    ot.path || ' > ' || e.name
FROM employees e
JOIN org\_tree ot ON e.manager\_id = ot.id

) SELECT REPEAT(’ ’, level - 1) || name as employee, title, level, path FROM org_tree ORDER BY path

Output:

Example: Category Tree

WITH RECURSIVE category_tree AS ( SELECT id, name, parent_id, ARRAY[name] as path, 1 as depth FROM categories WHERE parent_id IS NULL

UNION ALL

SELECT
    c.id,
    c.name,
    c.parent\_id,
    ct.path || c.name,
    ct.depth + 1
FROM categories c
JOIN category\_tree ct ON c.parent\_id = ct.id

) SELECT id, name, array_to_string(path, ’ > ’) as full_path, depth FROM category_tree ORDER BY path

CTE for Running Totals

WITH daily_sales AS ( SELECT DATE(order_date) as sale_date, SUM(total_amount) as daily_total FROM orders WHERE order_date >= ‘2025-01-01’ GROUP BY DATE(order_date) ) SELECT sale_date, daily_total, SUM(daily_total) OVER (ORDER BY sale_date) as running_total FROM daily_sales ORDER BY

CTE for Year-over-Year Comparison

WITH current_year AS ( SELECT EXTRACT(MONTH FROM order_date) as month, SUM(total_amount) as revenue FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2025 GROUP BY EXTRACT(MONTH FROM order_date) ), previous_year AS ( SELECT EXTRACT(MONTH FROM order_date) as month, SUM(total_amount) as revenue FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2024 GROUP BY EXTRACT(MONTH FROM order_date) ) SELECT cy.month, cy.revenue as revenue_2025, py.revenue as revenue_2024, ROUND((cy.revenue - py.revenue) / py.revenue * 100, 2) as yoy_growth_pct FROM current_year cy LEFT JOIN previous_year py ON cy.month = py.month ORDER BY

CTE for Data Deduplication

WITH ranked_records AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY email ORDER BY created_at DESC ) as rn FROM customers ) SELECT id, email, name, created_at FROM ranked_records WHERE rn = 1

CTE for Cohort Analysis

WITH first_purchase AS ( SELECT customer_id, DATE_TRUNC(‘month’, MIN(order_date)) as cohort_month FROM orders GROUP BY customer_id ), cohort_data AS ( SELECT fp.cohort_month, DATE_TRUNC(‘month’, o.order_date) as order_month, COUNT(DISTINCT o.customer_id) as customers FROM first_purchase fp JOIN orders o ON fp.customer_id = o.customer_id GROUP BY fp.cohort_month, DATE_TRUNC(‘month’, o.order_date) ) SELECT cohort_month, order_month, customers, EXTRACT(MONTH FROM AGE(order_month, cohort_month)) as months_since_first FROM cohort_data ORDER BY cohort_month,

CTE vs Subqueries vs Temporary Tables

Feature	CTE	Subquery	Temp Table
Readability	High	Low	Medium
Reusability in query	Yes	No	Yes
Recursive support	Yes	No	No
Performance	Query optimized	Query optimized	Materialized
Persistence	Query scope	Query scope	Session/Transaction
Best for	Complex logic	Simple, one-time	Multiple queries

CTE Performance Considerations

When CTEs Are Efficient

Reference once - CTE is inlined, no overhead
Recursive operations - Only option for hierarchy traversal
Improving readability - Same performance as subquery

When to Consider Alternatives

Multiple references - Some databases re-execute CTE each time
Large intermediate results - Temp table might be faster
Cross-query reuse - Use views or temp tables

Database-Specific Behavior

PostgreSQL: CTEs are optimization fences by default (materialized). Use WITH cte AS NOT MATERIALIZED for inlining.

WITH active_users AS NOT MATERIALIZED ( SELECT * FROM users WHERE status = ‘active’ ) SELECT * FROM active_users WHERE created_at > ‘2025-01-01’

SQL Server: CTEs are always inlined (not materialized).

MySQL 8.0+: CTEs can be materialized or merged based on optimizer decision.

Real-World CTE Use Cases

1. Customer Segmentation

WITH customer_metrics AS ( SELECT customer_id, COUNT(*) as order_count, SUM(total_amount) as lifetime_value, MAX(order_date) as last_order, MIN(order_date) as first_order FROM orders GROUP BY customer_id ), segments AS ( SELECT customer_id, lifetime_value, order_count, CASE WHEN lifetime_value >= 10000 AND order_count >= 10 THEN ‘VIP’ WHEN lifetime_value >= 5000 OR order_count >= 5 THEN ‘Regular’ WHEN last_order >= CURRENT_DATE - INTERVAL ‘90 days’ THEN ‘Active’ ELSE ‘At Risk’ END as segment FROM customer_metrics ) SELECT segment, COUNT(*) as customer_count, AVG(lifetime_value) as avg_ltv, SUM(lifetime_value) as total_revenue FROM segments GROUP BY segment ORDER BY total_revenue DESC

2. Inventory Analysis

WITH stock_status AS ( SELECT product_id, quantity_on_hand, reorder_point, CASE WHEN quantity_on_hand <= 0 THEN ‘Out of Stock’ WHEN quantity_on_hand <= reorder_point THEN ‘Low Stock’ ELSE ‘In Stock’ END as status FROM inventory ), recent_sales AS ( SELECT product_id, SUM(quantity) as units_sold_30d FROM order_items oi JOIN orders o ON oi.order_id = o.order_id WHERE o.order_date >= CURRENT_DATE - INTERVAL ‘30 days’ GROUP BY product_id ) SELECT p.name, ss.quantity_on_hand, ss.status, COALESCE(rs.units_sold_30d, 0) as units_sold_30d, CASE WHEN rs.units_sold_30d > 0 THEN ROUND(ss.quantity_on_hand::numeric / (rs.units_sold_30d / 30.0), 1) ELSE NULL END as days_of_stock FROM products p JOIN stock_status ss ON p.id = ss.product_id LEFT JOIN recent_sales rs ON p.id = rs.product_id WHERE ss.status != ‘In Stock’ ORDER BY days_of_stock NULLS FIRST

3. Funnel Analysis

WITH funnel_stages AS ( SELECT session_id, MAX(CASE WHEN event = ‘page_view’ THEN 1 ELSE 0 END) as viewed, MAX(CASE WHEN event = ‘add_to_cart’ THEN 1 ELSE 0 END) as added_to_cart, MAX(CASE WHEN event = ‘checkout_started’ THEN 1 ELSE 0 END) as started_checkout, MAX(CASE WHEN event = ‘purchase’ THEN 1 ELSE 0 END) as purchased FROM events WHERE event_date >= CURRENT_DATE - INTERVAL ‘7 days’ GROUP BY session_id ) SELECT SUM(viewed) as stage_1_views, SUM(added_to_cart) as stage_2_cart, SUM(started_checkout) as stage_3_checkout, SUM(purchased) as stage_4_purchase, ROUND(SUM(added_to_cart)::numeric / NULLIF(SUM(viewed), 0) * 100, 2) as view_to_cart_pct, ROUND(SUM(started_checkout)::numeric / NULLIF(SUM(added_to_cart), 0) * 100, 2) as cart_to_checkout_pct, ROUND(SUM(purchased)::numeric / NULLIF(SUM(started_checkout), 0) * 100, 2) as checkout_to_purchase_pct FROM

Common CTE Mistakes to Avoid

1. Referencing CTE Before Definition

— Wrong: can’t reference cte2 before it’s defined WITH cte1 AS (SELECT * FROM cte2), — Error! cte2 AS (SELECT * FROM table) SELECT * FROM cte1;

— Correct: define in order of dependency WITH cte2 AS (SELECT * FROM table), cte1 AS (SELECT * FROM cte2) SELECT * FROM

2. Forgetting CTE Scope

— CTE is not available in second query WITH active_users AS (SELECT * FROM users WHERE status = ‘active’) SELECT * FROM active_users;

SELECT * FROM active_users; — Error: active_users doesn’t exist here

3. Recursive CTE Without Termination

— Infinite loop risk WITH RECURSIVE bad_cte AS ( SELECT 1 as n UNION ALL SELECT n + 1 FROM bad_cte — Never stops! ) SELECT * FROM bad_cte;

— Always add a termination condition WITH RECURSIVE good_cte AS ( SELECT 1 as n UNION ALL SELECT n + 1 FROM good_cte WHERE n < 100 — Stops at 100 ) SELECT * FROM

Conclusion

The SQL WITH clause (CTE) is one of the most valuable tools for writing clean, maintainable, and powerful queries. Whether you’re simplifying complex logic, traversing hierarchies with recursion, or building multi-step data transformations, CTEs make your SQL more readable and easier to debug.

Key takeaways:

Use CTEs to break complex queries into logical steps
Leverage recursive CTEs for hierarchical data
Combine multiple CTEs for sophisticated analysis
Be aware of database-specific materialization behavior

Want to generate complex CTEs automatically? Try AI2sql - describe your query in plain English and get optimized SQL with proper CTE structure.

Start your free trial

Build Your Own AI Agent Team in 15 Min — Free OpenClaw Guide

Feb 5, 2026

TOOLS

OpenClaw AI Assistant: Local 24/7 Automation Guide 2026

Feb 4, 2026

TOOLS