CTEs (Common Table Expressions) create temporary named result sets. Improve query readability and enable recursion.

Basic CTE Syntax

WITH cte_name AS (

SELECT ...

)

SELECT * FROM cte_name;

Simple CTE Example

WITH high_value_customers AS (

SELECT customer_id, SUM(total) as total_spent

FROM orders

GROUP BY customer_id

HAVING SUM(total) > 10000

)

SELECT c.name, hvc.total_spent

FROM customers c

JOIN high_value_customers hvc ON c.id = hvc.customer_id;

Multiple CTEs

WITH

active_products AS (

SELECT * FROM products WHERE status = 'active'

),

recent_orders AS (

SELECT * FROM orders WHERE order_date > DATE_SUB(NOW(), INTERVAL 30 DAY)

)

SELECT ap.name, COUNT(ro.id) as recent_sales

FROM active_products ap

LEFT JOIN order_items oi ON ap.id = oi.product_id

LEFT JOIN recent_orders ro ON oi.order_id = ro.id

GROUP BY ap.id;

Recursive CTE

WITH RECURSIVE category_tree AS (

SELECT id, name, parent_id, 0 as level

FROM categories WHERE parent_id IS NULL

UNION ALL

SELECT c.id, c.name, c.parent_id, ct.level + 1

FROM categories c

JOIN category_tree ct ON c.parent_id = ct.id

)

SELECT * FROM category_tree;

Generate CTEs

AI2sql creates CTEs for complex queries.