SQL Subquery und CTE Referenz
Referenz für subqueries, gemein Tabelle expressions (CTEs), und recursive queries.
Subquery Types
Scalar subqueries return a single value, usable in SELECT and WHERE. Row subqueries return a single row. Table subqueries return a result set, usable in FROM. Correlated subqueries reference the outer query, evaluated per row. Subqueries in WHERE: IN, EXISTS, ANY/ALL. Derived tables (subqueries in FROM) must be aliased. Subqueries can replace joins but may perform differently. Modern optimizers often flatten subqueries into joins.
- scalar
- Single value
- correlated
- References outer
- EXISTS
- Non-empty check
SELECT/WHEREper-row evalsemi-joinCTEs and Recursion
Common Table Expressions (WITH clause) name subqueries for readability and reuse. CTEs can be referenced multiple times in the main query. Recursive CTEs have a base case and recursive step, enabling tree/hierarchy traversal. The UNION (not UNION ALL) in recursive CTEs deduplicates. Materialized CTEs (MATERIALIZED hint) force intermediate result storage. Recursive CTEs are essential for org charts, category trees, and graph traversal. Window CTEs chain window function computations. CTEs improve query modularity.
- WITH
- Named subquery
- recursive
- Base + recursive step
- MATERIALIZED
- Force storage
CTEtree traversalhint