Learn to compose complex SQL queries clearly in PostgreSQL by nesting scalar, row and table subqueries, using IN/EXISTS/ANY/ALL, mastering correlated subqueries, and refactoring tangled logic into readable Common Table Expressions. You will finish able to break a hard reporting question into named, testable steps and even walk hierarchies with recursive CTEs.
Watch the free preview
Scalar subqueries — a query that returns one value — free to watch, no account needed.
What you'll learn
- Compose complex queries clearly by nesting and naming subqueries
- Choose correctly between scalar, row and table subqueries for a given problem
- Use IN, EXISTS, ANY and ALL for membership and existence tests without NULL surprises
- Write correlated subqueries and reason about their per-row execution
- Refactor tangled SQL into readable CTEs, including recursive CTEs for hierarchies
Syllabus
Subqueries — The Building Blocks
Scalar subqueries — a query that returns one valueFree preview
Row and table subqueries, and subqueries in FROM
Membership & Existence — IN, EXISTS, ANY, ALL
IN and NOT IN — and the NULL trap
EXISTS, ANY and ALL — existence and quantified comparison
Correlated Subqueries
Understanding correlated subqueries
Correlated subqueries, performance and alternatives
Common Table Expressions (CTEs)
WITH — naming steps for readable queries
Recursive CTEs for hierarchies and graphs
Lab — 10 Exercises & Solutions
Exercises 1–5
Exercises 6–10