Lesson 2 of 12 · 18 min
IN with a subquery: filtering by a list
By the end of this lesson you can filter rows with WHERE column IN (SELECT …), nest list subqueries across several tables, and explain when IN gives a different row count from a JOIN.
You already know IN with a typed list: WHERE city IN ('Riyadh', 'Jeddah'). The list was fixed when you wrote the query. Very often the list you need lives in another table: the customers who placed a marketplace order, the products someone returned as damaged. Instead of typing it, let a query produce it.
A subquery used with IN must return one column. It can return any number of rows, including zero. PostgreSQL treats that column as the list:
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons