Subqueries in WHERE

A subquery result can become part of a condition

Inside WHERE, a subquery often calculates a value or a set of values that rows from the outer query are then compared against.

SELECT name
FROM products
WHERE price > (
    SELECT AVG(price)
    FROM products
);

First the average price is calculated, then the outer query keeps products priced above that value.

If several values are returned, use a suitable operator

SELECT name
FROM users
WHERE id IN (
    SELECT user_id
    FROM orders
    WHERE status = 'PAID'
);

The inner query may return many user_id values. The IN operator checks whether a user's identifier belongs to that set.