What are subqueries and where can they appear?

Subqueries let you compose a query from smaller parts. The shape of the result determines where it can be used: one row and one column is scalar, one column is a list, and a full table works in FROM or a join.

SELECT p.id, p.title, p.view_count
FROM posts p
WHERE p.author_id IN (
  SELECT u.id
  FROM users u
  WHERE u.plan = 'pro'
)
AND p.view_count > (
  SELECT AVG(view_count) FROM posts
);

Subquery shapes

• Scalar subquery: returns one row and one column, usable where a value is expected
• Row or list subquery: used with IN, ANY, or ALL
• Derived table: a subquery in FROM that behaves like a temporary table
• Correlated subquery: references columns from the outer query
• EXISTS: checks whether the subquery returns any row

Many subqueries are rewritten by the optimizer into joins or semi-joins. That means a readable subquery is not automatically slower. Still, check the plan when the subquery is correlated or returns a large intermediate set.