Why use a subquery in the WHERE clause instead of a fixed number?

A subquery computes its comparison value from live data every time the query runs, so the filter stays accurate automatically as the underlying data changes, unlike a hardcoded constant.

How do I find rows with no matching record in another table?

Use a WHERE subquery with NOT IN, selecting the relevant foreign key column from the related table, such as finding patients absent from the appointments table entirely.

Can I use BETWEEN with a subquery in WHERE?

Yes, if the subquery produces a value usable as a range boundary, such as WHERE salary BETWEEN (SELECT MIN(salary) FROM doctors) AND (SELECT AVG(salary) FROM doctors).

What's the risk of using NOT IN with a subquery?

If the subquery's result contains a NULL value, the entire NOT IN condition can unexpectedly evaluate to no matches at all, a well-known SQL edge case worth testing for.

Is a WHERE subquery evaluated once or for every outer row?

A non-correlated WHERE subquery, like the AVG example, is evaluated once. A correlated subquery, covered in Lesson 7.8, re-evaluates once per outer row instead.

Why use a subquery in the WHERE clause instead of a fixed number?

A subquery computes its comparison value from live data every time the query runs, so the filter stays accurate automatically as the underlying data changes, unlike a hardcoded constant.

How do I find rows with no matching record in another table?

Use a WHERE subquery with NOT IN, selecting the relevant foreign key column from the related table, such as finding patients absent from the appointments table entirely.

Can I use BETWEEN with a subquery in WHERE?

Yes, if the subquery produces a value usable as a range boundary, such as WHERE salary BETWEEN (SELECT MIN(salary) FROM doctors) AND (SELECT AVG(salary) FROM doctors).

What's the risk of using NOT IN with a subquery?

If the subquery's result contains a NULL value, the entire NOT IN condition can unexpectedly evaluate to no matches at all, a well-known SQL edge case worth testing for.

Is a WHERE subquery evaluated once or for every outer row?

A non-correlated WHERE subquery, like the AVG example, is evaluated once. A correlated subquery, covered in Lesson 7.8, re-evaluates once per outer row instead.

Subquery in WHERE Clause for Filtering Data

The WHERE clause is the single most common home for a subquery. Instead of hardcoding a filter value, a WHERE subquery computes that value dynamically at query time — meaning the filter automatically stays correct even as the underlying data changes, without ever needing to edit the query itself.

Key Definitions

WHERE clause subquery: A subquery placed inside a WHERE condition, used to filter outer query rows based on a dynamically computed value or set of values.
Dynamic filter: A filter condition whose value is computed at query time from current data, rather than hardcoded as a fixed constant.

What You'll Learn

Use subqueries within WHERE to filter rows based on dynamic, computed values.
Combine subqueries with comparison operators, IN, and BETWEEN.
Understand why dynamic filtering with a subquery is more maintainable than hardcoded values.
Recognize common real-world WHERE subquery patterns.

Detailed Explanation

Hardcoding a filter value, such as `WHERE salary > 95000`, works only until the data changes — if salaries are updated next month, this fixed number becomes stale and potentially misleading. Replacing it with a subquery, `WHERE salary > (SELECT AVG(salary) FROM doctors)`, makes the filter self-updating: the average is recalculated fresh every time the query runs, always reflecting current data.

WHERE subqueries commonly combine with several operators depending on the shape of data being compared: standard operators (=, >, <) for single-row subqueries, IN for multi-row subqueries, and BETWEEN when comparing against a computed range. A very common real pattern is filtering one table based on related records in another, such as finding all patients who have never had an appointment — which requires a subquery combined with NOT IN.

Visual Summary

A single query box with three arrows pointing into its WHERE clause, each labeled with a different subquery pattern: 'salary > (SELECT AVG...)', 'patient_id IN (SELECT patient_id...)', 'department_id NOT IN (SELECT department_id...)'.

Quick Reference

Pattern	Use Case
WHERE col > (SELECT AVG(col) FROM table)	Find rows above the current average
WHERE col IN (SELECT col FROM related_table WHERE condition)	Find rows related to any matching record
WHERE col NOT IN (SELECT col FROM related_table)	Find rows with no related record at all

SQL Example


CREATE TABLE departments (
  department_id   INT PRIMARY KEY AUTO_INCREMENT,
  department_name VARCHAR(100) NOT NULL
);

CREATE TABLE doctors (
  doctor_id       INT PRIMARY KEY AUTO_INCREMENT,
  doctor_name     VARCHAR(100) NOT NULL,
  department_id   INT,
  salary          INT,
  FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

CREATE TABLE patients (
  patient_id      INT PRIMARY KEY AUTO_INCREMENT,
  patient_name    VARCHAR(100) NOT NULL,
  city            VARCHAR(80)
);

CREATE TABLE appointments (
  appointment_id    INT PRIMARY KEY AUTO_INCREMENT,
  patient_id        INT,
  doctor_id         INT,
  appointment_date  DATE,
  status            VARCHAR(20),
  FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
  FOREIGN KEY (doctor_id)  REFERENCES doctors(doctor_id)
);

CREATE TABLE bills (
  bill_id         INT PRIMARY KEY AUTO_INCREMENT,
  appointment_id  INT,
  amount          DECIMAL(10,2),
  paid            BOOLEAN DEFAULT FALSE,
  FOREIGN KEY (appointment_id) REFERENCES appointments(appointment_id)
);

INSERT INTO departments VALUES
  (1, 'Cardiology'), (2, 'Orthopedics'), (3, 'Neurology'), (4, 'Dermatology');

INSERT INTO doctors (doctor_id, doctor_name, department_id, salary) VALUES
  (101, 'Dr. Verma', 1, 95000), (102, 'Dr. Iyer', 2, 78000),
  (103, 'Dr. Sen', 3, 120000), (104, 'Dr. Khan', 1, 88000);

INSERT INTO patients VALUES
  (201, 'Amit Rao', 'Pune'), (202, 'Neha Joshi', 'Mumbai'),
  (203, 'Karan Mehta', 'Delhi'), (204, 'Divya Nair', 'Pune');

INSERT INTO appointments VALUES
  (301, 201, 101, '2026-05-01', 'Completed'),
  (302, 202, 102, '2026-05-02', 'Completed'),
  (303, 203, 101, '2026-05-03', 'Cancelled'),
  (304, 204, 103, '2026-05-04', 'Completed');

INSERT INTO bills (appointment_id, amount, paid) VALUES
  (301, 1500.00, TRUE), (302, 2200.00, FALSE),
  (303, 800.00, TRUE), (304, 3000.00, TRUE);

-- Dynamic filter: doctors earning above the current average salary
SELECT doctor_name, salary
FROM doctors
WHERE salary > (SELECT AVG(salary) FROM doctors);

-- Patients who have never had any appointment
SELECT patient_name
FROM patients
WHERE patient_id NOT IN (
  SELECT patient_id FROM appointments WHERE patient_id IS NOT NULL
);

The first query's threshold is computed fresh from live data every time it runs, so it never needs manual updating even as doctor salaries change. The second query uses NOT IN with a subquery to find patients absent from the appointments table entirely — in this sample data, every patient has at least one appointment, so this returns an empty result, correctly reflecting that fact.

Real-World Examples

Retail dashboards filter products priced above the current average product price using a live WHERE subquery, avoiding hardcoded thresholds.
HR systems filter employees whose salary falls below the current department average to flag them for a compensation review.
Customer relationship systems filter customers who have never placed an order using a NOT IN subquery against the orders table.

Common Mistakes to Avoid

Hardcoding filter values that quickly become outdated instead of using a dynamic subquery.
Using NOT IN without accounting for potential NULL values in the subquery result, which can silently produce zero rows.
Choosing the wrong comparison operator for the subquery's actual row count, causing runtime errors.

Interview Questions

Q1. Why is a subquery in WHERE often better than a hardcoded value?

A subquery computes its comparison value dynamically from live data every time the query runs, so the filter automatically stays accurate as data changes, unlike a hardcoded constant that becomes stale.

Q2. How would you find records in one table with no related record in another table using a WHERE subquery?

Use NOT IN with a subquery selecting the foreign key column from the related table, such as WHERE patient_id NOT IN (SELECT patient_id FROM appointments), which finds patients absent from the appointments table entirely.

Practice MCQs

1. Why is `WHERE salary > (SELECT AVG(salary) FROM doctors)` preferred over `WHERE salary > 95000`?

It runs faster in all cases
It stays accurate automatically as underlying data changes
It uses less memory
It avoids syntax errors

Answer: B. It stays accurate automatically as underlying data changes

Explanation: A subquery-based filter recalculates its comparison value from current data every time the query runs, unlike a hardcoded constant.

2. Which operator is used with a subquery to find rows with no related match in another table?

IN
NOT IN
=
BETWEEN

Answer: B. NOT IN

Explanation: NOT IN with a subquery finds outer rows whose key value does not appear anywhere in the subquery's result list, identifying unmatched records.

Quick Revision Points

WHERE subqueries make filters dynamic, automatically staying accurate as data changes.
IN and NOT IN are the standard operators for relating one table to another via a WHERE subquery.
NOT IN requires care with NULLs in the subquery result — a frequently tested edge case.

Conclusion

Subqueries in WHERE create dynamic, self-updating filter conditions.
IN and NOT IN are the standard tools for relating rows across tables using a WHERE subquery.
Always verify NULL handling when using NOT IN with a subquery.

Placing a subquery inside a WHERE clause creates a dynamic filter that recalculates its comparison value from live data every time the query runs, avoiding the staleness of hardcoded constants. Common patterns include comparing against an aggregate like AVG or MAX, using IN to find rows related to any matching record in another table, and using NOT IN to find rows with no related record at all — though NOT IN requires careful handling of potential NULL values in the subquery result.