Subqueries and CTEs

A subquery is a SELECT in parentheses inside another statement (SELECT, INSERT, UPDATE, DELETE, SET or DO), letting one query use another's answer: "above the average price", "customers with no reviews". Subqueries differ in the shape they return (a scalar, a column, a row or a table) and in whether they refer to the outer query. A common table expression (CTE) names a subquery up front with WITH, and a recursive one walks trees. The examples run on a freshly loaded bookshop database.

Subsections

• Scalar Subqueries
• Subquery Kinds
• IN, ANY, ALL, and EXISTS
• Correlated Subqueries
• Derived Tables and LATERAL
• Subqueries in DML
• WITH Clauses
• Recursive CTEs