Reference

SQL Subqueries and EXISTS

How to use nested SELECTs, IN subqueries, EXISTS, and correlated checks.

A subquery is a SELECT inside another query. It is useful when one question depends on the result of another question: compare a row to an average, filter by a set from another table, or ask whether a matching row exists.

Scalar subquery

select product_name, price
from products
where price > (
  select avg(price)
  from products
)

Practice

Practice IN with a subquery Practice scalar subqueries Practice correlated subqueries Practice EXISTS and NOT EXISTS Practice NOT IN vs NOT EXISTS Practice ANY and ALL Practice subqueries in SELECT and HAVING Practice IN vs JOIN with duplicates Practice the latest row per group

Scalar subqueries

A scalar subquery returns one value: one row and one column. It can appear in a comparison, a SELECT list, or other expression slots where SQL expects a single value. If the subquery returns more than one row, most databases, DuckDB included, raise an error. SQLite is the exception: it quietly uses the first row. See More than one row returned by a subquery for the fixes.

IN with a subquery

IN can use a subquery instead of a hard-coded list. The inner query produces the allowed values; the outer query keeps rows whose value is in that set.

IN subquery

select product_name
from products
where supplier_id in (
  select supplier_id
  from suppliers
  where state = 'OR'
)

EXISTS and NOT EXISTS

EXISTS checks whether the subquery returns at least one row. It does not care what columns the subquery selects, so select 1 is a common convention. NOT EXISTS keeps rows where no match exists.

EXISTS

select c.customer_id, c.first_name, c.last_name
from customers as c
where exists (
  select 1
  from orders as o
  where o.customer_id = c.customer_id
)

Correlation

A correlated subquery refers to a column from the outer query. In the EXISTS example, o.customer_id = c.customer_id connects each customer row to the orders checked inside the subquery.

Subquery, join, or CTE?

Use	When it fits
`Scalar subquery`	You need one comparison value, such as an average or max.
`IN` subquery	You need a set of allowed keys or values from another query.
`EXISTS`	You only care whether a related row exists, not how many.
`JOIN`	You need columns from both tables in the final result.
`WITH` / CTE	The intermediate result deserves a name or will be reused.