Subqueries in WHERE

Overview

Subqueries in `WHERE` — Filter Rows Using Another Query

A subquery in the WHERE clause lets you filter rows based on the result of another query.

This is one of the most common and powerful SQL patterns.

You use it when you want to answer questions like:

Which students are enrolled in at least one course?
Which courses have students enrolled?
Which instructors belong to departments that actually offer courses?

Subqueries in WHERE return one or more values, and the outer query filters based on them.

The Basic Pattern


SELECT columns
FROM table_name
WHERE column operator (
    SELECT column
    FROM other_table
    WHERE condition
);

The subquery can return:

A single value (scalar)
A list of values (used with IN, ANY, ALL)

Common Operators Used With Subqueries

IN
NOT IN
=
<>
> / <
>= / <=
EXISTS / NOT EXISTS
ANY / ALL

Examples

Example 1: Students who are enrolled in at least one course


SELECT s.student_id,
       s.first_name,
       s.last_name
FROM students s
WHERE s.student_id IN (
    SELECT e.student_id
    FROM enrollments e
);

This returns only students who appear in the enrollments table.

Example 2: Courses that have at least one student enrolled


SELECT c.course_id,
       c.course_name
FROM courses c
WHERE c.course_id IN (
    SELECT e.course_id
    FROM enrollments e
);

Courses with no enrollments are excluded.

Example 3: Instructors whose department offers at least one course


SELECT i.first_name,
       i.last_name,
       i.department
FROM instructors i
WHERE i.department IN (
    SELECT c.department
    FROM courses c
);

This filters instructors based on related course data.

Example 4: Students enrolled in a specific course (course_id = 101)


SELECT s.student_id,
       s.first_name,
       s.last_name
FROM students s
WHERE s.student_id IN (
    SELECT e.student_id
    FROM enrollments e
    WHERE e.course_id = 101
);

A clean way to filter based on related rows.

Example 5: Courses with fewer than 5 enrollments


SELECT c.course_id,
       c.course_name
FROM courses c
WHERE c.course_id IN (
    SELECT e.course_id
    FROM enrollments e
    GROUP BY e.course_id
    HAVING COUNT(*) < 5
);

This example shows how subqueries combine with GROUP BY and HAVING.

Why Use Subqueries in `WHERE`?

They simplify filtering based on related tables
They avoid joins when you only need to check membership
They support complex logic (aggregates, conditions, nested filters)
They are essential for real-world reporting and analytics