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
)

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 raise an error.

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 theEXISTS example, o.customer_id = c.customer_idconnects 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.