Lesson 14.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 do not 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 JOINis usually a better fit.

Schema · Garden ShopTable · suppliers4 columns · 6 rows

Table · suppliers

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

4 columns · 6 rows

supplier_id intsupplier_name textcontact_email textstate text

Your task

Show product_name and supplier_id for products supplied by suppliers in OR or FL. Use a subquery against suppliers to find the matching IDs.

SQL Workbench

query.sqlgarden_shop · SQL engine loading

⌘↵ to run

Expected answer