×

sql · level 2 · unit 4 · lesson 3

  1. meet it
  2. guess first
  3. your turn
  4. fix it
  5. nailed it

Tap a green word if you get lost.

meet it

A subquery can build the list that IN checks.

IN needs a list. Instead of typing one, let a subquery make it. WHERE id IN (SELECT customer_id FROM orders) keeps every customer whose id appears anywhere in orders.

The gives back one column, with any number of rows. The then checks each of its own rows against that list.

NOT IN with a subquery finds what is missing, like an anti-join. Make sure the inner query's column holds no NULLs, or NOT IN matches nothing.

SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders)
ORDER BY name;

Repeats in the list do not matter. A customer passes once, however many times their id appears.