IN with a subquery.

When the list of allowed values lives in another table, use WHERE column IN (SELECT ...). The subquery must return exactly one column; the outer row stays when its value appears in that result. Unlike a JOIN on a one-to-many link, IN does not duplicate outer rows.

Read it naturally: "customers whose id is among the customers with an order above 100". The subquery runs on its own — it cannot see columns of the outer row.

Common mistakes: a subquery with two columns; comparing unrelated columns (id with total); expecting IN to sort the result — it does not.

Small example:

SELECT id, name FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE total >= 50);