Multi-Row Subquery in SQL with Examples

Unlike a single-row subquery, a multi-row subquery can legitimately return several rows of a single column — a whole list of values rather than one. Comparing an outer column against this list requires a different set of operators: IN, ANY, or ALL, since the standard = operator cannot compare a value against a set.

Key Definitions

• Multi-row subquery: A subquery that can return more than one row of a single column, requiring set-based comparison operators like IN, ANY, or ALL.
• IN operator: Checks whether a value matches any value within a given list or subquery result set.

What You'll Learn

• Define a multi-row subquery and how it differs from a single-row subquery.
• Use the IN operator to compare a value against a subquery's result list.
• Recognize when a subquery naturally produces multiple rows.
• Preview ANY and ALL as alternative multi-row comparison operators.

Detailed Explanation

Consider finding all patients who have had a Completed appointment: `SELECT patient_name FROM patients WHERE patient_id IN (SELECT patient_id FROM appointments WHERE status = 'Completed')`. The inner query can legitimately return multiple patient_id values — as many as there are completed appointments — so using = would fail. IN, however, is designed exactly for this: it checks whether the outer row's patient_id exists anywhere within the list produced by the subquery.

This pattern is extremely common for 'find all X that are related to any Y matching some condition' questions. The subquery does not need to know how many rows it will return in advance — IN handles any list size gracefully, including an empty result (in which case no rows match at all).

Visual Summary

A single outer value 'patient_id = 201' being checked against a list produced by the subquery: [201, 202, 204], displayed as a bracket containing three numbers. An arrow labeled 'IN — is 201 present in this list?' points from the outer value into the list, with a checkmark showing a match.

Quick Reference

Subquery Result	Row Count	Correct Operator
(SELECT patient_id FROM appointments WHERE status = 'Completed')	Multiple rows (3 in sample data)	IN
(SELECT MAX(salary) FROM doctors)	Exactly 1 row	=
(SELECT department_id FROM departments)	Multiple rows (4 in sample data)	IN

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);

-- Multi-row subquery: patients with at least one Completed appointment
SELECT patient_name
FROM patients
WHERE patient_id IN (
  SELECT patient_id FROM appointments WHERE status = 'Completed'
);

-- Multi-row subquery: doctors NOT in the Cardiology department
SELECT doctor_name
FROM doctors
WHERE doctor_id NOT IN (
  SELECT doctor_id FROM appointments WHERE doctor_id IS NOT NULL
);

The first query's inner subquery returns three patient_id values (201, 202, 204) corresponding to Completed appointments; IN checks each patient against this list, correctly including Amit Rao, Neha Joshi, and Divya Nair while excluding Karan Mehta, whose appointment was Cancelled. The second query demonstrates NOT IN, the negated form, excluding any doctor whose doctor_id appears in the appointments table at all.

Real-World Examples

• E-commerce platforms use multi-row subqueries to find customers who have purchased any product from a specific category.
• University systems use multi-row subqueries to find students enrolled in any course taught by a specific instructor.
• HR systems use multi-row subqueries to find employees who belong to any department located in a specific city.

Common Mistakes to Avoid

• Using = instead of IN when the subquery can legitimately return multiple rows.
• Using NOT IN with a subquery result that might contain NULL values, which can cause the entire NOT IN condition to unexpectedly match zero rows.
• Not verifying how many rows a subquery actually returns before choosing between = and IN.

Interview Questions

Q1. What is a multi-row subquery?

A multi-row subquery is a subquery that can return more than one row of a single column, requiring operators like IN, ANY, or ALL instead of standard comparison operators like =.

Q2. How does the IN operator work with a subquery?

IN checks whether the outer query's column value matches any value within the list of values returned by the inner subquery, regardless of how many rows that list contains.

Q3. What happens if a multi-row subquery used with IN returns zero rows?

If the subquery returns no rows, the IN condition simply matches nothing, and the outer query returns an empty result set — this does not cause an error, unlike using = with multiple rows.

Practice MCQs

1. Which operator is designed to work with a subquery that returns multiple rows?

1. =
2. IN
3. <
4. >=

Answer: B. IN

Explanation: IN checks whether a value exists anywhere within the list produced by a multi-row subquery, unlike single-value comparison operators.

2. What does NOT IN do with a subquery?

1. Matches values present in the subquery list
2. Matches values absent from the subquery list
3. Always returns zero rows
4. Causes a syntax error

Answer: B. Matches values absent from the subquery list

Explanation: NOT IN is the negated form of IN, matching outer rows whose value does not appear anywhere in the subquery's result list.

Quick Revision Points

• Multi-row subqueries require IN, ANY, or ALL — never a bare = operator.
• IN checks membership in the subquery's result list, regardless of list size.
• Caution: NOT IN can behave unexpectedly if the subquery result contains NULL — a frequently tested edge case.

Conclusion

• A multi-row subquery can return several rows of a single column.
• IN is the standard operator for checking membership against a multi-row subquery result.
• Be cautious with NOT IN when the subquery might return NULL values.

A multi-row subquery can legitimately return several rows of a single column, requiring set-based operators like IN rather than a standard comparison operator like =. IN checks whether the outer query's value exists anywhere within the subquery's result list, gracefully handling any number of matching rows, including zero. This pattern is essential for 'find all X related to any Y matching a condition' questions, though care is needed with NOT IN when NULL values might appear in the subquery result.