CTE (Common Table Expression)

Common Table Expression (CTE) est une requête nommée temporaire (existing pour la durée d'une seule statement) définie via clause `WITH` en SQL standard. Améliore lisibilité de queries complex en décomposant en blocks logiques, et active recursive queries (impossible avec subqueries classiques).

Syntaxe basique :
```sql
WITH active_users AS (
SELECT user_id, name FROM users WHERE active = true
),
recent_orders AS (
SELECT user_id, COUNT(*) AS count FROM orders
WHERE created_at > NOW() - INTERVAL '30 days'
GROUP BY user_id
)
SELECT au.name, ro.count
FROM active_users au
LEFT JOIN recent_orders ro ON au.user_id = ro.user_id;
```

Avantages :
(1) **Readability** — break complex query in named pieces, top-to-bottom flow vs nested subqueries.
(2) **Reusable** — same CTE referenced multiple times in main query.
(3) **Recursive queries** — hierarchical data (org charts, tree structures, graph traversal).
(4) **Debugging** — comment out parts easily.

Recursive CTE exemple — org chart :
```sql
WITH RECURSIVE org AS (
SELECT id, name, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, o.level + 1
FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT * FROM org ORDER BY level;
```
Recursive CTE utiles : hierarchies, tree traversal, graph shortest path, generate series, fibonacci sequences, etc.

Support :
- PostgreSQL : excellent, materialized vs not-materialized hints (CTE_MATERIALIZED flag).
- MySQL : depuis 8.0.
- SQL Server : depuis 2005.
- Oracle : depuis 9i (avec recursion sub-syntax different).
- MariaDB : depuis 10.2.
- SQLite : depuis 3.8.3.

Performance considérations : (1) **Postgres** avant 12 — CTEs were optimization fence (materialized always). Now `MATERIALIZED` keyword explicit ; default inlined like subquery. (2) **Other DBs** typically inline CTEs into main query for optimizer flexibility. (3) **Recursive CTEs** can be expensive — careful with cycles (use UNION (vs UNION ALL) to dedupe), depth limits.

Alternatives historiques : nested subqueries (less readable), temp tables (more overhead, persistent), views (permanent objects). CTE le sweet spot pour many cases. Compétences DP-300, DEA-C01.