AI Hints
Homeโ€บSQL Tutorialโ€บSubqueries
SQL ยท Lesson 55 of 126

Subqueries with IN

Matching against a list.

โฑ 2 min readโ–ถ Video at 1:18:54๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

Remember in? Where city in, Dublin, London, Sydney. It checks a value against a list. Well, that list doesn't have to be typed by hand. A subquery can build it for you.

Subqueries with IN
From the video: Subqueries with IN
  • IN checks a list
  • You typed the list by hand
  • A subquery can build it

IN with a subquery

Helen wants to call customers who have returned an order, to ask what went wrong. The subquery lists the customer IDs from returned orders. The outer query keeps customers whose ID is in that list. Run it. Here are the first five customers to call.

query.sqlsql
SELECT first_name, last_name, city
FROM customers
WHERE customer_id IN (SELECT customer_id
                      FROM orders
                      WHERE status = 'returned')
LIMIT 5;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ first_name โ”‚ last_name โ”‚   city    โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ Sam        โ”‚ Costa     โ”‚ Dublin    โ”‚
โ”‚ Diego      โ”‚ Ali       โ”‚ Vancouver โ”‚
โ”‚ Carmen     โ”‚ Ahmed     โ”‚ Melbourne โ”‚
โ”‚ Vera       โ”‚ Smith     โ”‚ Vancouver โ”‚
โ”‚ Ava        โ”‚ Brown     โ”‚ Toronto   โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

How many to call?

Same idea, but counted. Run it. Fifty eight customers have returned at least one order. Notice, sixty three orders were returned. But some customers returned more than once, and in only counts each customer one time.

query.sqlsql
SELECT COUNT(*) AS customers_who_returned
FROM customers
WHERE customer_id IN (SELECT customer_id
                      FROM orders
                      WHERE status = 'returned');
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ customers_who_returned โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 58                     โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

IN versus JOIN

You could answer this with a join too. So which should you use? A join gives you columns from both tables. In just filters, and never creates duplicate rows. If you only need to filter, in is often simpler, and avoids the double counting trap.

JOIN

  • Columns from both tables
  • Can duplicate rows
  • Use to show data

IN (subquery)

  • Columns from one table
  • Never duplicates
  • Use to filter

Summary

In, with a subquery, filters using a list that SQL builds for you. Next, subqueries in even more places.

Key takeaways
  • IN (SELECT ...)
  • The list builds itself
  • No duplicates
Next up: Subqueries in FROM and SELECT โ€” tables made on the fly