What is a Subquery in SQL? Types and Use Cases

Some questions can't be answered by a single, simple SELECT. 'Which doctors earn more than the average doctor salary?' requires knowing the average salary first, then comparing every doctor against it — two steps rolled into one query. A subquery is SQL's way of nesting one query inside another, letting the result of an inner query feed directly into an outer query.

Subqueries appear constantly in real interview questions and production reporting code, precisely because so many real business questions are naturally two-step: 'find X, but only where X relates to some computed value of Y.'

Key Definitions

• Subquery (nested query): A SELECT query placed inside another SQL statement, whose result is used by the outer query.
• Inner query: The subquery itself, executed to produce a value or set of values used by the outer query.
• Outer query: The main query that uses the result produced by the inner (sub)query.
• Derived table: A subquery used in the FROM clause, treated as a temporary, virtual table for the duration of the outer query.

What You'll Learn

• Define what a subquery is and where it can appear in a SQL statement.
• Understand the difference between an inner query and an outer query.
• Preview the major subquery types: single-row, multi-row, multi-column, scalar, and correlated.
• Write a first basic subquery in the WHERE clause.

Detailed Explanation

A subquery can appear in several places within a SQL statement: inside WHERE to filter rows based on a computed condition, inside FROM to act as a temporary derived table, inside SELECT to compute a single scalar value per row, and even inside HAVING to filter grouped results.

Consider `SELECT doctor_name, salary FROM doctors WHERE salary > (SELECT AVG(salary) FROM doctors)`. MySQL first executes the inner query — `SELECT AVG(salary) FROM doctors` — producing a single number, the average salary. That number is then substituted into the outer query's WHERE clause, effectively becoming `WHERE salary > 95250` once evaluated. This two-step evaluation is the essence of every subquery: the inner part resolves first, and its result becomes an input to the outer part.

The rest of this module explores each subquery variety in depth: whether the inner query returns one value or many, whether it re-runs for every row of the outer query (correlated) or just once (non-correlated), and where in the statement it's placed.

Visual Summary

Two nested boxes: an outer box labeled [Outer Query: SELECT doctor_name, salary FROM doctors WHERE salary > ...] containing an inner box labeled [Inner Query (Subquery): SELECT AVG(salary) FROM doctors]. An arrow points from the inner box's result, labeled '95250', into the outer box's WHERE condition.

Quick Reference

Subquery Location	Purpose	Covered In
WHERE clause	Filter rows using a computed condition	Lesson 7.5
FROM clause	Act as a temporary derived table	Lesson 7.6
SELECT clause	Compute a scalar value per row	Lesson 7.7
Correlated (any clause)	Re-evaluate per outer row, referencing outer columns	Lesson 7.8

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

-- Subquery in WHERE: doctors earning above the average salary
SELECT doctor_name, salary
FROM doctors
WHERE salary > (SELECT AVG(salary) FROM doctors);

MySQL first runs the inner query, SELECT AVG(salary) FROM doctors, which computes 95,250 based on the four doctor salaries. This single value replaces the subquery in the outer WHERE clause, so the query effectively becomes WHERE salary > 95250. Only Dr. Sen (120,000) satisfies this condition and appears in the result.

Real-World Examples

• HR systems use subqueries to find employees earning above their department's average salary.
• E-commerce platforms use subqueries to find products priced above the category's average price.
• Banking systems use subqueries to identify accounts with balances above the branch-wide average balance.

Common Mistakes to Avoid

• Assuming a subquery always returns multiple rows, when many subqueries are designed to return a single scalar value.
• Not realizing the inner query is evaluated separately from, and generally before, the outer query.
• Overusing subqueries where a simpler JOIN would be more readable and often more efficient — covered further in Lesson 7.13.

Interview Questions

Q1. What is a subquery in SQL?

A subquery is a SELECT query nested inside another SQL statement. It is evaluated first, and its result is used by the outer query, commonly to filter rows, compute a per-row value, or act as a temporary table.

Q2. In what parts of a SQL statement can a subquery appear?

A subquery can appear in the WHERE clause for filtering, the FROM clause as a derived table, the SELECT clause as a scalar value, and the HAVING clause for filtering aggregated groups.

Q3. What is the difference between an inner query and an outer query?

The inner query is the subquery itself, evaluated first to produce a value or result set. The outer query is the main statement that consumes that result to complete its own logic.

Practice MCQs

1. What is a subquery?

1. A stored procedure
2. A query nested inside another query
3. A type of index
4. A database trigger

Answer: B. A query nested inside another query

Explanation: A subquery is a SELECT statement placed inside another SQL statement, whose result feeds into the outer query.

2. In `WHERE salary > (SELECT AVG(salary) FROM doctors)`, which part executes first?

1. The outer WHERE clause
2. The inner SELECT AVG(salary) subquery
3. Both execute simultaneously
4. Neither executes until COMMIT

Answer: B. The inner SELECT AVG(salary) subquery

Explanation: MySQL evaluates the inner subquery first, producing a single value that is then used to complete the outer query's condition.

Quick Revision Points

• A subquery can appear in WHERE, FROM, SELECT, and HAVING clauses.
• The inner query is generally evaluated before the outer query uses its result.
• Major subquery types: single-row, multi-row, multi-column, scalar, and correlated — each covered individually in this module.

Conclusion

• A subquery nests one SELECT statement inside another to answer multi-step questions.
• The inner query resolves first, and its result feeds into the outer query.
• Subqueries can appear in WHERE, FROM, SELECT, and HAVING clauses, each serving a different purpose.

A subquery is a SELECT statement nested inside another SQL statement, used to answer questions that require an intermediate computed value or result set. The inner query is evaluated first, and its output becomes an input to the outer query, whether as a comparison value in WHERE, a derived table in FROM, a per-row scalar in SELECT, or a filter in HAVING. This module explores every major subquery variety in depth, starting from this foundational concept.