Advanced SQL Techniques

Subqueries and CTEs

Advanced SQL uses subqueries and common table expressions.

Subqueries

Subqueries in WHERE: SELECT * FROM table WHERE col > (SELECT AVG(col) FROM table).

Correlated subqueries reference outer query. EXISTS checks existence.

Common Table Expressions (CTEs)

WITH cte AS (SELECT ...). Reusable, readable. Multiple CTEs can chain.

Advanced Aggregations

ROLLUP: hierarchical aggregates. CUBE: all combinations.

PIVOT rotates rows to columns. UNPIVOT does reverse.

Key Takeaways

1. CTEs improve readability
2. Subqueries enable complex filtering
3. PIVOT/UNPIVOT transform data structures