Subquery in WHERE clause in SQL

Introduction

Subqueries in the WHERE clause help you filter data by using the result of another query. This lets you find rows that match conditions based on other data.

You want to find customers who have placed orders.

You need to list products that are in a specific category found in another table.

You want to get employees who work in departments located in a certain city.

You want to filter records based on values calculated or selected from another table.

Syntax

SQL

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

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

The main query uses this result to filter its rows.

Examples

This finds customers who have made at least one order.

SQL

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

This finds products in the 'Beverages' category.

SQL

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

This finds employees working in departments located in New York.

SQL

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.

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, 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 can return one or many values. Use IN for multiple values, = for a single value.

Make sure the subquery returns compatible data types for comparison.

Summary

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

Use IN when the subquery returns multiple values, = when it returns one.

This helps connect data from different tables easily.