Single-Row Subquery in SQL with Examples
A single-row subquery is the simplest and most common subquery type: it returns exactly one row and one column, a single value that can be compared directly using =, >, <, >=, <=, or <>. Understanding when a subquery is guaranteed to return a single row is essential, because using the wrong comparison operator with a multi-row result produces a runtime error.
Key Definitions
- Single-row subquery: A subquery that returns exactly one row and one column, allowing it to be used with standard comparison operators like =, >, and <.
- Scalar value: A single, individual value, as opposed to a set of multiple values or rows.
What You'll Learn
- Define a single-row subquery and its guaranteed single-value result.
- Use single-row subqueries with standard comparison operators.
- Recognize which aggregate functions naturally produce single-row subqueries.
- Understand the error that occurs when a single-row operator receives multiple rows.
Detailed Explanation
Aggregate functions like AVG, MAX, MIN, SUM, and COUNT applied without GROUP BY always return exactly one row, making them natural candidates for single-row subqueries. For example, `(SELECT MAX(salary) FROM doctors)` always produces a single number — the highest salary — regardless of how many rows exist in the doctors table.
This guarantee is what allows the subquery to be used directly with an operator like `=` or `>`. If you mistakenly write a subquery that could return multiple rows — such as `(SELECT salary FROM doctors WHERE department_id = 1)`, which could return two or more salaries — and then compare it with `=`, MySQL throws a runtime error: 'Subquery returns more than 1 row.' This is one of the most common subquery errors beginners encounter, and recognizing it quickly is a valuable debugging skill.
Visual Summary
A funnel diagram: multiple doctor salary values (95000, 78000, 120000, 88000) flow into a funnel labeled 'MAX()', narrowing down to a single output value '120000', which then flows into the outer query's WHERE clause as a single comparable value.
Quick Reference
| Subquery | Guaranteed Single Row? | Safe to Use With = |
|---|---|---|
| (SELECT MAX(salary) FROM doctors) | Yes — MAX always returns one value | Yes |
| (SELECT AVG(salary) FROM doctors) | Yes — AVG always returns one value | Yes |
| (SELECT salary FROM doctors WHERE department_id = 1) | No — could return multiple rows | No — causes an error |
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);
-- Single-row subquery: doctor with the highest salary
SELECT doctor_name, salary
FROM doctors
WHERE salary = (SELECT MAX(salary) FROM doctors);
-- This causes an error: subquery can return more than one row
-- SELECT doctor_name FROM doctors
-- WHERE salary = (SELECT salary FROM doctors WHERE department_id = 1);The first query safely uses = because MAX(salary) is guaranteed to return exactly one value, 120000, matching Dr. Sen. The commented-out second example demonstrates the classic mistake: department_id = 1 matches both Dr. Verma and Dr. Khan, so the subquery returns two rows, and MySQL cannot evaluate `salary = (two values)`, raising a 'Subquery returns more than 1 row' error.
Real-World Examples
- Retail systems use single-row subqueries to find the product with the highest sales in a given month.
- Payroll systems use single-row subqueries to compare each employee's salary against the company-wide maximum or average.
- Sports analytics platforms use single-row subqueries to find the player with the most goals or points in a season.
Common Mistakes to Avoid
- Using = with a subquery that isn't guaranteed to return a single row, causing a runtime error.
- Forgetting that aggregate functions without GROUP BY always collapse to a single row, making them safe for single-row comparisons.
- Not testing subqueries independently first to confirm how many rows they actually return before embedding them in a larger query.
Interview Questions
Q1. What is a single-row subquery?
A single-row subquery is a subquery guaranteed to return exactly one row and one column, allowing it to be used directly with standard comparison operators like =, >, <, >=, and <=.
Q2. What error occurs if a single-row operator is used with a subquery that returns multiple rows?
MySQL raises the error 'Subquery returns more than 1 row,' since operators like = cannot compare a single value against a set of multiple values.
Q3. Which aggregate functions are commonly used to guarantee a single-row subquery?
MAX, MIN, AVG, SUM, and COUNT, when applied without a GROUP BY clause, always return a single aggregated value, making them safe choices for single-row subqueries.
Practice MCQs
1. Which operator can safely be used with a single-row subquery?
- IN only
- =, >, <, >=, <=
- EXISTS only
- None, subqueries need special syntax
Answer: B. =, >, <, >=, <=
Explanation: Since a single-row subquery guarantees exactly one value, it can be compared directly using any standard comparison operator.
2. What happens if `= (SELECT ...)` receives a subquery returning three rows?
- It silently uses the first row
- It silently uses the last row
- MySQL throws a 'Subquery returns more than 1 row' error
- It returns NULL
Answer: C. MySQL throws a 'Subquery returns more than 1 row' error
Explanation: The = operator requires exactly one comparable value, so a multi-row subquery result causes a runtime error rather than silently picking a row.
Quick Revision Points
- Single-row subqueries return exactly one row and one column.
- Safe operators: =, >, <, >=, <=, <>.
- Aggregate functions without GROUP BY are the most reliable way to guarantee a single-row result.
Conclusion
- A single-row subquery is guaranteed to return exactly one value.
- Use standard comparison operators only when the subquery is truly guaranteed to return a single row.
- Aggregate functions like MAX, MIN, and AVG without GROUP BY are the safest way to produce single-row subqueries.
A single-row subquery returns exactly one row and one column, making it compatible with standard comparison operators like =, >, and <. Aggregate functions such as MAX, MIN, and AVG, when used without GROUP BY, are the most reliable way to guarantee this single-value result. Using a comparison operator with a subquery that might return multiple rows is one of the most common SQL errors beginners encounter, producing the runtime error 'Subquery returns more than 1 row.'