Subquery with IN operator in SQL

Introduction

Use a subquery with the IN operator to find rows where a column's value matches any value from a list returned by another query.

You want to find customers who have placed orders.

You want to list products that appear in recent sales.

You want to select employees who work in departments located in a specific city.

You want to filter students enrolled in courses offered this semester.

Syntax

SQL

SELECT column1, column2
FROM table1
WHERE columnX IN (SELECT columnY FROM table2 WHERE condition);

The subquery inside the IN operator returns a list of values.

The main query selects rows where columnX matches any value from that list.

Examples

Find employees who work in departments located in New York.

SQL

SELECT name
FROM employees
WHERE department_id IN (SELECT id FROM departments WHERE location = 'New York');

List products that belong to the 'Beverages' category.

SQL

SELECT product_name
FROM products
WHERE category_id IN (SELECT id FROM categories WHERE name = 'Beverages');

Get students enrolled in the course with ID 101.

SQL

SELECT student_name
FROM students
WHERE student_id IN (SELECT student_id FROM enrollments WHERE course_id = 101);

Sample Program

This query finds employees who work in departments located in New York.

SQL

CREATE TABLE departments (id INT, name VARCHAR(20), location VARCHAR(20));
INSERT INTO departments VALUES (1, 'Sales', 'New York'), (2, 'HR', 'Chicago'), (3, 'IT', 'New York');

CREATE TABLE employees (id INT, name VARCHAR(20), department_id INT);
INSERT INTO employees VALUES (1, 'Alice', 1), (2, 'Bob', 2), (3, 'Charlie', 3), (4, 'Diana', 2);

SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE location = 'New York');

Important Notes

The subquery runs first and returns a list of values.

The IN operator checks if the main query's column matches any value from that list.

Subqueries with IN are useful for filtering based on related tables.

Summary

Use IN with a subquery to filter rows based on a list of values from another query.

The subquery returns values that the main query compares against.

This helps find related data across tables easily.