Scalar subqueries and subqueries in WHERE — inline queries explained

A Subquery Is a Query Inside a Query

A subquery runs first. Its result is used by the outer query — as a filter, a value, or a virtual table. Subqueries are wrapped in parentheses.

-- Scalar subquery: returns one value
SELECT name, price,
  (SELECT AVG(price) FROM products) AS catalog_avg
FROM products
WHERE price > (SELECT AVG(price) FROM products);

Subqueries in WHERE with IN

-- Customers who placed an order in 2024
SELECT name FROM customers
WHERE id IN (
  SELECT DISTINCT customer_id
  FROM orders
  WHERE created_at >= '2024-01-01'
);

NOT IN and NULLs: A Trap

If the subquery for NOT IN returns any NULL value, the entire result is empty — no rows match. This is the most common silent bug with NOT IN:

-- If customer_id is NULL in any order row, this returns 0 rows
SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

-- Safe alternative
SELECT name FROM customers
WHERE id NOT IN (
  SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
);

Use NOT EXISTS or a LEFT JOIN / IS NULL pattern instead of NOT IN for reliability.