Subqueries with IN operator in MySQL

Introduction

Subqueries with the IN operator help you find rows where a value matches any value from a list returned by another query.

When you want to find all customers who have placed orders.

When you want to list products that appear in any sales records.

When you want to filter employees who work in departments located in a specific city.

When you want to get students enrolled in any of the courses offered this semester.

Syntax

MySQL

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT column_name FROM another_table WHERE condition);

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

The main query returns rows where the column value is found in that list.

Examples

Finds customers who have made at least one order.

MySQL

SELECT name
FROM customers
WHERE id IN (SELECT customer_id FROM orders);

Lists products in the 'Electronics' category.

MySQL

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

Finds employees working in departments located in New York.

MySQL

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

Sample Program

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

MySQL

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, employee_name VARCHAR(20), department_id INT);
INSERT INTO employees VALUES (1, 'Alice', 1), (2, 'Bob', 2), (3, 'Charlie', 3), (4, 'Diana', 2);

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

Important Notes

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

Make sure the subquery returns only one column.

Using IN with a subquery can be slower on large data; indexes help improve speed.

Summary

Use IN with subqueries to match values from another table.

Subqueries return a list of values for the main query to check.

This helps connect related data easily without complex joins.