Lesson 10.2

SQL IN with Subqueries

An IN subquery lets the database build the allowed-value list from another SELECT instead of hard-coding it by hand.

Earlier, IN matched a column against a short hand-written list. A subquery can supply that list for you. The outer query asks for products; the inner query finds the supplier IDs that qualify.

This keeps the logic tied to the data. If a supplier changes state, the subquery result changes automatically. You don't need to copy new IDs into the outer query.

Pattern

select item_name
from items
where parent_id in (
  select parent_id
  from parents
  where status = 'active'
)

The inner query returns the list

An IN subquery should return one column. It may return many rows, because those rows become the list the outer query checks against. If you need columns from both tables in the final output, a JOIN is usually a better fit.

Syntax reference

Need syntax details? See the SQL subquery and EXISTS reference

Schema · Garden Shop Tables · suppliers, products 2 tables

Table · suppliers

One row per supplier. Some suppliers have no contact email.

4 columns · 6 rows

supplier_id int supplier_name text contact_email text state text

Table · products

One row per product, with price, cost, and inventory levels.

9 columns · 24 rows