Course contentsShow
← Data Science
Lesson 961 of 2,14525. SQL Subqueries and CTEsFree lesson

IN Operator with Subqueries

Filter rows using IN with subqueries that return multiple values, checking if column values match any subquery result.

IN Operator with Subqueries

You'll learn: How to use the IN operator with subqueries to filter rows by checking if a column value matches any result from a multi-value subquery.

What It Does

The IN operator combined with a subquery lets you filter rows based on whether a column's value exists in a list returned by another query. Think of it like checking if someone's name appears on a guest list—instead of manually typing each name, you pull the guest list from another source (the subquery).

How It Works

When you write WHERE column IN (subquery), SQL:

  1. Executes the subquery first and gets back multiple rows (each with one value)
  2. Checks each row in the main query to see if its column value matches any value from the subquery results
  3. Includes the row if there's a match

Basic Syntax

SELECT customer_id, customer_name
FROM customers
WHERE country_id IN (
    SELECT country_id
    FROM countries
    WHERE continent = 'Europe'
);

This finds all customers whose country_id appears in the list of European country IDs. The subquery returns multiple country IDs, and IN checks if each customer's country matches any of them.

Why Use This?

Instead of hard-coding values like WHERE country_id IN (1, 5, 12, 18), the subquery dynamically generates the list based on current data. If new European countries are added, your query automatically includes them.

Key Takeaway: The IN operator with subqueries filters rows by matching column values against a dynamic list returned by the subquery, making your queries flexible and data-driven.