Subqueries in WHERE

One of the most common uses of a subquery is filtering: instead of typing a hard-coded value into a WHERE clause, you compute the value (or set of values) to filter against by running another query first. This lets a filter condition stay accurate as the underlying data changes, since it is calculated fresh every time the query runs.

Combining with Comparison Operators

When a subquery is guaranteed to return a single value (a scalar subquery), it can sit directly next to =, >, <, >=, <=, or <> just like any literal value would.

SQL

-- Products priced above the overall average price
SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- The employee with the highest salary
SELECT employee_name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

Combining with IN

When a subquery can legitimately return multiple rows of a single column, use IN to test whether the outer row's value appears anywhere in that list.

SQL

-- Customers who have placed at least one order over $1000
SELECT customer_id, name, email
FROM customers
WHERE customer_id IN (
  SELECT customer_id
  FROM orders
  WHERE total > 1000
);

Read this from the inside out: the inner query scans the orders table and produces a list of every customer_id belonging to an order over $1000 — a customer with three big orders just contributes duplicate IDs to that list. The outer query then keeps only the customers whose customer_id shows up anywhere in that list. NOT IN works the same way in reverse, but it has a well-known NULL pitfall covered on the EXISTS page.

Combining with EXISTS

EXISTS takes a different approach: rather than comparing values, it just asks whether the subquery would return any row at all for the current outer row. It is often the most efficient choice for existence checks and is covered fully on its own page.

SQL

-- Same result as the IN example above, expressed with EXISTS
SELECT c.customer_id, c.name, c.email
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
    AND o.total > 1000
);

Operator	Subquery Shape	Meaning
`=`, `>`, `<`, etc.	Scalar (one row, one column)	Direct comparison against a single computed value
`IN`	One column, any number of rows	Outer value must match one of the subquery's returned values
`NOT IN`	One column, any number of rows	Outer value must match none of the subquery's returned values (NULL caution — see EXISTS page)
`EXISTS`	Any columns, presence only matters	True if the subquery returns at least one row for the outer row
`ANY` / `ALL`	One column, any number of rows	Compare against at least one value / against every value returned

Note

A subquery used with IN, EXISTS, or ANY/ALL is not restricted to a single row — only scalar subqueries used directly with = or another comparison operator require exactly one row. Choosing the right operator for the shape of your subquery is the key skill here.

A Multi-Condition Example

SQL

-- Customers whose largest single order was over $1000, AND who are
-- based in a country that itself has averaged more than $500 in orders
SELECT c.customer_id, c.name, c.country
FROM customers c
WHERE c.customer_id IN (
  SELECT customer_id FROM orders WHERE total > 1000
)
AND c.country IN (
  SELECT country
  FROM customers c2
  JOIN orders o ON o.customer_id = c2.customer_id
  GROUP BY country
  HAVING AVG(o.total) > 500
);

Use a scalar subquery with =, >, <, etc. when the inner query returns exactly one value.
Use IN when the inner query returns a list of values to match against.
Use EXISTS when you only care whether a matching row exists, not what its values are.
You can combine several subquery-based conditions with AND / OR in a single WHERE clause.