Subqueries in WHERE clause in MySQL

Introduction

Subqueries in the WHERE clause help you filter data by using the result of another query inside your main query.

When you want to find records that match a condition based on another table's data.

When you need to compare a column value to a list of values returned by another query.

When you want to check if a value exists in another table before selecting rows.

When you want to filter rows based on an aggregate calculation from another query.

Syntax

MySQL

SELECT column1, column2
FROM table1
WHERE column3 operator (SELECT columnX FROM table2 WHERE condition);

The subquery inside parentheses runs first and returns a value or set of values.

The main query uses the subquery result to filter rows in the WHERE clause.

Examples

Find employees who work in the Sales department by matching department IDs.

MySQL

SELECT name
FROM employees
WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales');

Get products in categories whose names start with 'Elec'.

MySQL

SELECT product_name
FROM products
WHERE category_id IN (SELECT id FROM categories WHERE name LIKE 'Elec%');

Find customers who have not placed any orders after January 1, 2024.

MySQL

SELECT customer_name
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders WHERE order_date > '2024-01-01');

Sample Program

This query finds employees who belong to the Sales department by using a subquery to get the Sales department's id.

MySQL

CREATE TABLE departments (id INT PRIMARY KEY, name VARCHAR(50));
INSERT INTO departments VALUES (1, 'Sales'), (2, 'HR');

CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(50), department_id INT);
INSERT INTO employees VALUES (1, 'Alice', 1), (2, 'Bob', 2), (3, 'Charlie', 1);

SELECT name
FROM employees
WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales');

Important Notes

Subqueries in WHERE can return a single value or multiple values (use = for single, IN for multiple).

Make sure the subquery returns compatible data types for comparison.

Subqueries can slow down queries if the tables are large; indexes help improve speed.

Summary

Subqueries in WHERE let you filter rows based on another query's result.

Use = for single-value subqueries and IN for multiple values.

They help connect data between tables without complex joins.