SQL Subqueries

SQL Subqueries are an important feature of relational databases that allow one SQL query to be placed inside another SQL query. A subquery helps retrieve intermediate results and use those results in the main query.

Subqueries are useful when the result required by one query depends on information that must first be calculated or retrieved by another query. They are commonly used with SELECT, WHERE, HAVING, INSERT, UPDATE, and DELETE statements.

For example, suppose we want to find students who scored more marks than the average marks of all students. First, SQL needs to calculate the average marks. A subquery can perform that calculation, and the outer query can use the result to find the required students.

What is a Subquery in SQL?

A subquery is a query written inside another SQL statement. The inner query is generally enclosed within parentheses, while the surrounding query is called the outer query or main query.

A subquery can return a single value, multiple values, one or more columns, or a temporary result set depending on how it is written.

Basic Syntax

SELECT column_name
FROM table_name
WHERE column_name = (
    SELECT column_name
    FROM another_table
);

The query inside the parentheses is the subquery. The query containing the subquery is called the outer query.

Simple Example of SQL Subquery

Consider a Student table containing student names and marks. Suppose we want to find the student or students who obtained the highest marks.

SELECT StudentName
FROM Student
WHERE Marks = (
    SELECT MAX(Marks)
    FROM Student
);

The inner query calculates the highest marks. The outer query then finds students whose marks are equal to that value.

If several students have the same highest marks, the query can return all of them.

Why are Subqueries Used?

Subqueries are useful when a SQL query requires an intermediate result before the final result can be obtained.

Perform calculations before filtering records.
Compare values with calculated results.
Retrieve data based on another table.
Handle complex filtering conditions.
Check whether related records exist.
Create temporary result sets.
Support data analysis and reporting.
Reduce the need for application-side calculations.

How Does a Subquery Work?

In a simple non-correlated subquery, the inner query can usually be evaluated independently from the outer query. Its result is then used by the outer query.

Example

SELECT StudentName, Marks
FROM Student
WHERE Marks > (
    SELECT AVG(Marks)
    FROM Student
);

The inner query calculates the average marks. The outer query then selects students whose marks are greater than that average.

Important Terms Used in Subqueries

Term	Meaning
Subquery	The inner query written inside another SQL statement.
Outer Query	The main query that uses the result of the subquery.
Inner Query	Another name for the subquery.
Nested Query	A query containing another query.
Correlated Subquery	A subquery that refers to a value from the outer query.

Types of SQL Subqueries

Subqueries can be classified according to the number of rows and columns they return and the way they interact with the outer query.

Type	Description
Single-Value Subquery	Returns a single value.
Single-Row Subquery	Returns one row and may contain multiple columns.
Multiple-Row Subquery	Returns multiple rows.
Multiple-Column Subquery	Returns more than one column.
Correlated Subquery	References values from the outer query.
Nested Subquery	Contains another subquery inside it.

Single-Value Subquery

A single-value subquery returns exactly one value. It is commonly used with comparison operators such as =, >, <, >=, and <=.

Example

SELECT StudentName
FROM Student
WHERE Marks > (
    SELECT AVG(Marks)
    FROM Student
);

The AVG() function returns one value, which is then compared with the Marks column of the outer query.

Single-Row Subquery

A single-row subquery returns one row. Depending on the query, that row can contain one or more columns.

Example

SELECT *
FROM Student
WHERE DepartmentID = (
    SELECT DepartmentID
    FROM Department
    WHERE DepartmentName = 'Computer Science'
);

The inner query identifies the department ID of the Computer Science department. The outer query then retrieves students belonging to that department.

Multiple-Row Subquery

A multiple-row subquery returns more than one row. Operators such as IN, ANY, and ALL are commonly used with multiple-row subqueries.

Example Using IN

SELECT StudentName
FROM Student
WHERE DepartmentID IN (
    SELECT DepartmentID
    FROM Department
    WHERE Building = 'Block A'
);

The inner query may return several department IDs. The outer query retrieves students whose DepartmentID matches any of those values.

Subquery with SELECT

A subquery can be used in the SELECT list to calculate or retrieve a value for the result.

Example

SELECT
    StudentName,
    Marks,
    (
        SELECT AVG(Marks)
        FROM Student
    ) AS AverageMarks
FROM Student;

The average marks are calculated by the subquery and displayed alongside every student record.

Subquery with WHERE Clause

The WHERE clause is one of the most common locations for a subquery. It is useful when the filtering condition depends on another query.

Example

SELECT ProductName, Price
FROM Product
WHERE Price > (
    SELECT AVG(Price)
    FROM Product
);

This query displays products whose price is greater than the average product price.

Subquery with HAVING Clause

A subquery can also be used with HAVING when grouped results need to be compared with another calculated value.

Example

SELECT DepartmentID, AVG(Marks) AS AverageMarks
FROM Student
GROUP BY DepartmentID
HAVING AVG(Marks) > (
    SELECT AVG(Marks)
    FROM Student
);

The query displays departments whose average marks are greater than the overall average marks.

Student Database Example

Consider the following Student table:

StudentID	StudentName	Department	Marks
101	Rahul	Computer Science	85
102	Priya	Computer Science	92
103	Amit	Information Technology	78
104	Neha	Information Technology	88

Find Students Above Average

SELECT StudentName, Marks
FROM Student
WHERE Marks > (
    SELECT AVG(Marks)
    FROM Student
);

The inner query calculates the overall average marks, while the outer query displays students who scored above that average.

Subquery with FROM Clause

A subquery can be placed inside the FROM clause. The resulting dataset is treated as a derived table or temporary result set and normally requires an alias.

Example

SELECT StudentData.StudentName,
       StudentData.Marks
FROM (
    SELECT StudentName, Marks
    FROM Student
    WHERE Marks >= 80
) AS StudentData;

The inner query creates a result containing students who scored at least 80 marks. The outer query then works with that derived result.

Subquery with INSERT

A subquery can be used with INSERT ... SELECT to copy or transform selected data from one table into another.

Example

INSERT INTO TopStudents (StudentID, StudentName, Marks)
SELECT StudentID, StudentName, Marks
FROM Student
WHERE Marks > (
    SELECT AVG(Marks)
    FROM Student
);

The query inserts students whose marks are above the overall average into the TopStudents table.

Subquery with UPDATE

Subqueries can be used to determine which records should be updated.

Example

UPDATE Employee
SET Bonus = 5000
WHERE Salary > (
    SELECT AVG(Salary)
    FROM Employee
);

This example assigns a bonus to employees whose salaries are above the average salary.

Note: Exact UPDATE-subquery restrictions can differ between database systems, so queries should be tested against the target DBMS.

Subquery with DELETE

A subquery can help identify records that need to be deleted.

Example

DELETE FROM Student
WHERE StudentID IN (
    SELECT StudentID
    FROM SuspendedStudents
);

The outer query removes students whose IDs are present in the SuspendedStudents table.

IN Operator with Subquery

The IN operator checks whether a value matches any value returned by a subquery.

Example

SELECT StudentName
FROM Student
WHERE DepartmentID IN (
    SELECT DepartmentID
    FROM Department
    WHERE Building = 'Block A'
);

If the subquery returns multiple department IDs, students belonging to any of those departments are selected.

NOT IN with Subquery

NOT IN can be used when we want values that do not match the values returned by a subquery.

Example

SELECT StudentName
FROM Student
WHERE DepartmentID NOT IN (
    SELECT DepartmentID
    FROM Department
    WHERE Building = 'Block A'
);

This query selects students whose departments are not located in Block A.

Important: NULL values can affect NOT IN results. When NULLs are possible, NOT EXISTS is often a safer alternative.

EXISTS Operator

The EXISTS operator checks whether a subquery returns at least one row. It does not normally need the actual values returned by the inner query; it only tests whether a matching row exists.

Syntax

SELECT column_name
FROM table_name
WHERE EXISTS (
    subquery
);

Example

SELECT DepartmentName
FROM Department D
WHERE EXISTS (
    SELECT 1
    FROM Student S
    WHERE S.DepartmentID = D.DepartmentID
);

The query returns departments that have at least one student.

NOT EXISTS Operator

NOT EXISTS returns TRUE when the subquery does not find a matching row.

Example

SELECT DepartmentName
FROM Department D
WHERE NOT EXISTS (
    SELECT 1
    FROM Student S
    WHERE S.DepartmentID = D.DepartmentID
);

This query finds departments that currently have no students.

IN vs EXISTS

Feature	IN	EXISTS
Purpose	Compares a value with returned values.	Checks whether matching rows exist.
Typical Use	Multiple-value filtering.	Existence checking.
NULL Behavior	Can produce unexpected results with NULL.	Generally easier to use safely with correlated conditions.
Common Example	DepartmentID IN (...)	EXISTS (...)

Correlated Subquery

A correlated subquery is a subquery that refers to a column from the outer query. Because of this dependency, the inner query is logically evaluated in relation to rows processed by the outer query.

Example

SELECT S.StudentName,
       S.Marks
FROM Student S
WHERE S.Marks > (
    SELECT AVG(S2.Marks)
    FROM Student S2
    WHERE S2.DepartmentID = S.DepartmentID
);

This query finds students whose marks are higher than the average marks of their own department.

How a Correlated Subquery Works

The outer query processes a student record. The correlated inner query then calculates the average for the department associated with that student. The result is compared with the student's marks.

Because the inner query depends on the outer query, correlated subqueries can require more processing than equivalent non-correlated queries. However, the actual execution strategy depends on the database optimizer and query structure.

Nested Subqueries

A nested subquery occurs when one subquery contains another subquery. This creates multiple levels of query processing.

Example

SELECT StudentName
FROM Student
WHERE DepartmentID = (
    SELECT DepartmentID
    FROM Department
    WHERE DepartmentHeadID = (
        SELECT FacultyID
        FROM Faculty
        WHERE FacultyName = 'Dr. Sharma'
    )
);

The innermost query identifies a faculty member. The next query identifies the department associated with that faculty member, and the outer query retrieves students from that department.

ANY Operator

The ANY operator compares a value with the values returned by a subquery. The condition becomes TRUE when the comparison is TRUE for at least one value returned by the subquery.

Example

SELECT ProductName, Price
FROM Product
WHERE Price > ANY (
    SELECT Price
    FROM Product
    WHERE CategoryID = 2
);

The query selects products whose price is greater than at least one price returned for Category 2.

ALL Operator

The ALL operator requires the comparison to be TRUE for every value returned by the subquery.

Example

SELECT ProductName, Price
FROM Product
WHERE Price > ALL (
    SELECT Price
    FROM Product
    WHERE CategoryID = 2
);

The query selects products whose price is greater than every price returned for Category 2.

Difference Between ANY and ALL

Feature	ANY	ALL
Meaning	Condition must match at least one value.	Condition must match every value.
Comparison	Less restrictive.	More restrictive.
Typical Use	Compare against one or more possible values.	Compare against the complete returned set.

Multiple-Column Subqueries

A multiple-column subquery returns more than one column. It can be useful when several column values must be compared together.

Example

SELECT StudentID, StudentName, DepartmentID, Marks
FROM Student
WHERE (DepartmentID, Marks) IN (
    SELECT DepartmentID, MAX(Marks)
    FROM Student
    GROUP BY DepartmentID
);

This query attempts to find the students having the maximum marks in each department. Support for row-value comparisons can vary between database systems, so the exact syntax should be checked for the target DBMS.

Subquery vs Join

Both subqueries and joins can be used to solve many database problems, but they express relationships differently.

Feature	Subquery	Join
Main Purpose	Uses the result of one query inside another.	Combines rows from multiple tables.
Readability	Often intuitive for filtering problems.	Often clearer for direct table relationships.
Nested Logic	Very suitable.	Less suitable for deeply nested logic.
Performance	Depends on query and optimizer.	Depends on query, indexes, and optimizer.
Alternative	Some subqueries can be rewritten as joins.	Some joins can be expressed using subqueries.

There is no universal rule that joins are always faster than subqueries. Modern database optimizers can transform many logically equivalent queries into similar execution plans. Performance should therefore be checked using the execution plan and actual workload.

Subquery vs CTE

A Common Table Expression (CTE) can sometimes make complex query logic easier to organize than deeply nested subqueries.

Feature	Subquery	CTE
Syntax	Written inside the main query.	Defined using WITH.
Readability	Good for small operations.	Often better for complex logic.
Reusability	Limited within the statement.	Can be referenced multiple times in the same statement.
Recursive Queries	Not the normal approach.	Can support recursive queries in database systems that implement recursive CTEs.

Performance Considerations for SQL Subqueries

Subqueries are not automatically slow. Performance depends on the query structure, indexes, table size, database optimizer, and execution plan.

Use appropriate indexes on columns involved in filtering and joins.
Avoid unnecessary levels of nesting.
Filter data as early as practical.
Use EXISTS when the requirement is only to check existence.
Check execution plans for expensive queries.
Avoid unnecessary correlated subqueries on very large datasets.
Compare alternative query forms when performance matters.

Real-World Example: Banking System

A banking application may need to identify customers whose account balance is greater than the average account balance.

SELECT CustomerName
FROM Customer
WHERE CustomerID IN (
    SELECT CustomerID
    FROM Account
    WHERE Balance > (
        SELECT AVG(Balance)
        FROM Account
    )
);

The innermost query calculates the average balance. The next query identifies qualifying accounts, and the outer query retrieves customer names.

Real-World Example: E-Commerce System

An e-commerce application can use a correlated subquery to find products that are more expensive than the average product price within their own category.

SELECT P.ProductName,
       P.Price
FROM Product P
WHERE P.Price > (
    SELECT AVG(P2.Price)
    FROM Product P2
    WHERE P2.CategoryID = P.CategoryID
);

This approach compares each product with the average price of its category.

Real-World Example: University Management System

A university can identify students who scored higher than the average marks of their department.

SELECT S.StudentName,
       S.Marks
FROM Student S
WHERE S.Marks > (
    SELECT AVG(S2.Marks)
    FROM Student S2
    WHERE S2.DepartmentID = S.DepartmentID
);

This type of query is useful for academic performance analysis and department-wise reporting.

Real-World Example: Employee Management

An organization may want to find employees earning more than the overall average salary.

SELECT EmployeeName,
       Salary
FROM Employee
WHERE Salary > (
    SELECT AVG(Salary)
    FROM Employee
);

The query dynamically calculates the average salary rather than requiring the application to provide a fixed value.

Advantages of SQL Subqueries

Useful for solving complex database problems.
Allows dynamic filtering based on calculated values.
Can make certain queries easier to understand.
Supports advanced data analysis.
Useful for existence checks.
Can create derived result sets.
Reduces the need for hard-coded intermediate values.
Works with several SQL statements and clauses.

Limitations of SQL Subqueries

Deep nesting can reduce readability.
Correlated subqueries may require substantial processing.
Complex queries can become difficult to debug.
NULL behavior can cause unexpected results with operators such as NOT IN.
Database-specific syntax differences may exist.
Some problems may be clearer using joins or CTEs.

Common Mistakes in SQL Subqueries

Using = when the subquery can return multiple rows.
Forgetting parentheses around the subquery.
Ignoring NULL behavior.
Creating unnecessary levels of nesting.
Using correlated subqueries without checking performance.
Not testing the inner query independently.
Assuming a subquery is always slower than a join.
Ignoring indexes and execution plans for large datasets.

Important Rules of SQL Subqueries

A subquery is normally enclosed within parentheses.
A single-value subquery can be compared using operators such as =, >, <, >=, and <=.
Multiple-row subqueries commonly work with IN, ANY, and ALL.
EXISTS checks whether the subquery produces at least one row.
A correlated subquery can reference columns from its outer query.
A subquery in the FROM clause generally requires a table alias.
NULL values should be considered carefully when using NOT IN.

SQL Subqueries Interview Questions and Answers

1. What is a Subquery?

A subquery is a SQL query written inside another SQL statement.

2. What is another name for a Subquery?

A subquery is also commonly called an inner query or nested query.

3. What is an Outer Query?

The outer query is the main SQL query that contains or uses the subquery.

4. What is a Single-Value Subquery?

A subquery that returns exactly one value.

5. What is a Single-Row Subquery?

A subquery that returns one row, potentially containing multiple columns.

6. What is a Multiple-Row Subquery?

A subquery that returns more than one row.

7. What is a Correlated Subquery?

A subquery that references a column from the outer query.

8. What is a Nested Subquery?

A query containing another subquery, sometimes at multiple levels.

9. What is the IN operator used for?

IN checks whether a value matches one of the values returned by an expression or subquery.

10. What does EXISTS do?

EXISTS checks whether a subquery returns at least one row.

11. What does NOT EXISTS do?

NOT EXISTS checks whether a subquery returns no matching rows.

12. What is ANY?

ANY makes a comparison against the values returned by a subquery and succeeds when the comparison is true for at least one value.

13. What is ALL?

ALL requires the comparison to be true for every value returned by the subquery.

14. Can a subquery be used in SELECT?

Yes, depending on the SQL statement and database system.

15. Can a subquery be used in WHERE?

Yes. WHERE is one of the most common locations for subqueries.

16. Can a subquery be used in FROM?

Yes. It can act as a derived table.

17. Can a subquery be used with INSERT?

Yes, particularly with INSERT ... SELECT.

18. Can a subquery be used with UPDATE?

Yes, subject to the syntax and restrictions of the database system.

19. Can a subquery be used with DELETE?

Yes. Subqueries can help identify the rows that should be deleted.

20. What happens if a single-value comparison receives multiple rows?

The query can produce an error because a scalar comparison expects one value.

21. Can a subquery return multiple columns?

Yes. Multiple-column subqueries can be used in supported comparison contexts.

22. What is the main advantage of a subquery?

It allows one query to use the result of another query dynamically.

23. Are subqueries always slower than joins?

No. Performance depends on the query, indexes, database optimizer, and execution plan.

24. Why can correlated subqueries be expensive?

Because their logic depends on rows from the outer query and may require repeated evaluation.

25. Why is EXISTS useful?

EXISTS is useful when the requirement is to determine whether at least one matching row exists.

26. What is the difference between IN and EXISTS?

IN compares a value with a set of returned values, while EXISTS checks whether a matching row exists.

27. What is the difference between ANY and ALL?

ANY requires the comparison to be true for at least one returned value, while ALL requires it to be true for every returned value.

28. What is a derived table?

A derived table is a result set created by a subquery in the FROM clause.

29. Why should NULL values be considered with NOT IN?

NULL introduces UNKNOWN comparison results, which can cause NOT IN to behave differently from what is expected.

30. Where are SQL Subqueries used?

They are widely used in reporting, banking, education, e-commerce, analytics, enterprise applications, and database development.

Conclusion

SQL Subqueries provide a powerful way to solve database problems that require one query to depend on the result of another query. They can be used for calculations, filtering, existence checks, data modification, and creating derived result sets.

Understanding single-value subqueries, multiple-row subqueries, correlated subqueries, nested queries, IN, EXISTS, NOT EXISTS, ANY, and ALL gives students and developers a strong foundation for writing practical SQL queries.

For better performance, subqueries should be written according to the requirements of the application and analyzed using appropriate indexes and execution plans. In some situations, a join or CTE may provide a clearer or more efficient solution.

A strong understanding of SQL Subqueries is particularly useful for database development, data analysis, university examinations, technical interviews, and real-world application development.

← Previous: SQL Functions Next: SQL Triggers →