Skip to the content
Start

IN with a subquery

SQL · Unit 9: Subqueries

Which orders are going to Denver? Before you learned to join tables, that question was two queries. One said who lives in Denver, and the other had their numbers typed in by hand in an IN (1, 5, 8).

The list of an IN can come from another query. With = or >, the subquery had to give a single value. With IN it returns one column with however many rows, and the orders that pass are the ones whose customer_id is among them. You get five, Ann's and Valerie's. Brian, customer 8, is on the list and hasn't ordered anything.

SELECT id, customer_id, placed
FROM orders
WHERE customer_id IN (
  SELECT id
  FROM customers
  WHERE city = 'Denver'
);

Returns

idcustomer_idplaced
112026-01-03
212026-01-04
552026-01-10
712026-01-12
852026-01-12

The rest of the explanation is in the lesson, which is part of the full plan.

Exercises in this lesson

You do them in the app, which checks them on the spot and explains why.

  1. 1. Predict the output

    The big boxes are the orders of 3 units or more. There are three: order 3 from customer 2, order 4 from customer 4, and order 8 from customer 5. In customers, 2 through 5 are Luke, Chloë, Mason, and Valerie, and 8 is Brian. What does this query return?

  2. 2. Complete the code

    Which products has Ann, customer 1, ordered? Her orders are 1, 2, and 7. Fill in the blank with the column that builds the list.

  3. 3. Predict the output

    Who bought in the first few days, before January 5? There were three orders in those days, two from Ann and one from Luke. What does this query return?

  4. 4. Find the bug

    Which products has Luke, customer 2, ordered? He's placed three orders, for the Mouse, the Monitor, and the Tablet. The query runs and returns only the Mouse. Which line has the problem?

  5. 5. Put the lines in order

    NOT IN takes a subquery too, and lets through whoever isn't on the list. Which customers have never gotten a big box? Put in order the query that gives their names in alphabetical order. Two lines don't belong.

  6. 6. Multiple choice

    For SQL, id NOT IN (1, 2, NULL) is id <> 1 AND id <> 2 AND id <> NULL. You already know that any comparison with NULL gives NULL. And that's not enough for an AND, because it needs every one of its pieces to be true. Nora Page has id 5. What happens to her row?

  7. 7. Complete the code

    The library is setting up a display case for the authors with nothing in its collection from before 2000. Three of its books are older than that: "The Lighthouse," "Loose Leaves," and "Short Stories," which is from 1975 and has no author. Fill in the blank so that gap doesn't get into the list.

  8. 8. Find the case that fails

    For a shelf of short reads, the library wants the authors with no book over 300 pages in its collection. They're Martha Sloan and Nora Page. Nobody took the NULLs out of this query's list, and it still gets it right. Which of these changes makes it return nobody?

  9. 9. Put the lines in order

    The library is celebrating Ireland this month. Which books does it have by Irish authors? Put in order the query that gives each one's title and pages, from longest to shortest. Two lines don't belong.

  10. 10. Write the code

    This month the books by Irish authors move to a display by the front desk. Which ones stay on the shelves? Write a query with one column, the title of the books that aren't by an Irish author, in alphabetical order. "Short Stories," which has no author, stays too.

Do this lesson

It opens in your browser. This lesson is part of the full plan; the first unit of each course is free.

See all lessons →