SQL Subqueries are one of the most powerful features of SQL. A subquery is a query written inside another SQL query. It allows developers to solve complex database problems by breaking them into smaller and more manageable parts.
Subqueries are commonly used in business applications, banking systems, educational portals, e-commerce websites, healthcare systems, and enterprise reporting tools. They help retrieve data based on the results of another query and provide flexibility when working with relational databases.
In this tutorial, you will learn the fundamentals of SQL Subqueries, their syntax, types, practical examples, and real-world applications.
A Subquery is an SQL query nested inside another SQL statement such as SELECT, INSERT, UPDATE, DELETE, or WHERE.
The inner query executes first and its result is passed to the outer query for further processing.
SELECT column_name
FROM table_name
WHERE column_name =
(
SELECT column_name
FROM another_table
);
The query inside parentheses is called the subquery, while the surrounding query is called the outer query.
Subqueries provide a simple way to retrieve information that depends on other data stored in the database.
The database engine executes the subquery first. Once the subquery returns a result, that result is supplied to the outer query.
SELECT Name
FROM Student
WHERE Marks =
(
SELECT MAX(Marks)
FROM Student
);
The inner query finds the highest marks. The outer query then displays the student who achieved those marks.
SQL Subqueries can be categorized into several types.
| Type | Description |
|---|---|
| Single Row Subquery | Returns one row only. |
| Multiple Row Subquery | Returns multiple rows. |
| Multiple Column Subquery | Returns multiple columns. |
| Correlated Subquery | Depends on outer query values. |
| Nested Subquery | Contains another subquery inside it. |
A Single Row Subquery returns only one value. These subqueries are often used with comparison operators such as =, >, <, >=, and <=.
SELECT Name
FROM Student
WHERE Marks >
(
SELECT AVG(Marks)
FROM Student
);
The subquery calculates the average marks, and the outer query displays students whose marks are greater than the average.
SELECT EmployeeName
FROM Employee
WHERE Salary >
(
SELECT AVG(Salary)
FROM Employee
);
This query identifies employees earning above the average salary.
A Multiple Row Subquery returns more than one value. Such subqueries are commonly used with IN, ANY, or ALL operators.
SELECT StudentName
FROM Student
WHERE DepartmentID IN
(
SELECT DepartmentID
FROM Department
WHERE Building = 'A'
);
The query displays students belonging to departments located in Building A.
Subqueries can be placed directly within a SELECT statement.
SELECT
StudentName,
(
SELECT AVG(Marks)
FROM Student
) AS AverageMarks
FROM Student;
The average marks value is displayed along with each student record.
The WHERE clause is one of the most common places to use subqueries.
SELECT ProductName
FROM Product
WHERE Price >
(
SELECT AVG(Price)
FROM Product
);
Products with prices higher than the average price are displayed.
Suppose the Student table contains information about students and their marks.
| StudentID | Name | Marks |
|---|---|---|
| 1 | Rahul | 85 |
| 2 | Priya | 92 |
| 3 | Amit | 78 |
SELECT Name
FROM Student
WHERE Marks >
(
SELECT AVG(Marks)
FROM Student
);
Students scoring above average marks will be displayed.
A subquery can also be placed inside the FROM clause. In such cases, the result of the subquery behaves like a temporary table.
SELECT *
FROM
(
SELECT StudentID,
Name,
Marks
FROM Student
) AS StudentData;
The inner query creates a temporary dataset which is processed by the outer query.
Subqueries can be used to insert data into a table based on data from another table.
INSERT INTO TopStudents
SELECT *
FROM Student
WHERE Marks >
(
SELECT AVG(Marks)
FROM Student
);
Students scoring above average marks are inserted into the TopStudents table.
Subqueries can update records using values from another query.
UPDATE Employee
SET Bonus = 5000
WHERE Salary >
(
SELECT AVG(Salary)
FROM Employee
);
Employees earning above average salaries receive a bonus.
Subqueries can identify records that need to be removed.
DELETE FROM Student
WHERE StudentID IN
(
SELECT StudentID
FROM SuspendedStudents
);
The query deletes students listed in the SuspendedStudents table.
Banks frequently use subqueries to identify customers with balances higher than average.
SELECT CustomerName
FROM Account
WHERE Balance >
(
SELECT AVG(Balance)
FROM Account
);
Online stores can identify expensive products using subqueries.
SELECT ProductName
FROM Product
WHERE Price >
(
SELECT AVG(Price)
FROM Product
);
Universities can identify top-performing students using nested queries.
SELECT Name
FROM Student
WHERE Marks =
(
SELECT MAX(Marks)
FROM Student
);
A Correlated Subquery is a special type of subquery that depends on the outer query for its values. Unlike ordinary subqueries, a correlated subquery cannot execute independently because it references columns from the outer query.
The database executes the correlated subquery once for every row processed by the outer query. This makes correlated subqueries powerful but sometimes slower on large datasets.
SELECT StudentName,
Marks
FROM Student S
WHERE Marks >
(
SELECT AVG(Marks)
FROM Student
WHERE DepartmentID =
S.DepartmentID
);
This query displays students whose marks are higher than the average marks of their own department.
Suppose there are multiple departments in a university. The outer query processes one student at a time, while the inner query calculates the department average for that specific student.
Because the inner query references values from the outer query, it must execute repeatedly.
A Nested Subquery is a subquery placed inside another subquery. SQL supports multiple levels of nesting depending on the database system.
SELECT StudentName
FROM Student
WHERE DepartmentID =
(
SELECT DepartmentID
FROM Department
WHERE DepartmentHead =
(
SELECT FacultyID
FROM Faculty
WHERE FacultyName =
'Dr. Sharma'
)
);
This example contains multiple layers of subqueries to retrieve students belonging to a department managed by a specific faculty member.
The IN operator is commonly used when a subquery returns multiple values.
SELECT StudentName
FROM Student
WHERE DepartmentID IN
(
SELECT DepartmentID
FROM Department
WHERE Building = 'Block A'
);
Students belonging to departments located in Block A are displayed.
The EXISTS operator checks whether a subquery returns at least one record.
If the subquery returns any row, EXISTS evaluates to TRUE.
SELECT column_name
FROM table_name
WHERE EXISTS
(
subquery
);
SELECT DepartmentName
FROM Department D
WHERE EXISTS
(
SELECT *
FROM Student S
WHERE S.DepartmentID =
D.DepartmentID
);
Only departments that have students are displayed.
NOT EXISTS works opposite to EXISTS. It returns records only when the subquery produces no results.
SELECT DepartmentName
FROM Department D
WHERE NOT EXISTS
(
SELECT *
FROM Student S
WHERE S.DepartmentID =
D.DepartmentID
);
Departments without students are displayed.
The ANY operator compares a value with any value returned by the subquery.
SELECT ProductName
FROM Product
WHERE Price >
ANY
(
SELECT Price
FROM Product
WHERE CategoryID = 2
);
Products whose price is greater than at least one product in Category 2 are returned.
The ALL operator compares a value with all values returned by the subquery.
SELECT ProductName
FROM Product
WHERE Price >
ALL
(
SELECT Price
FROM Product
WHERE CategoryID = 2
);
Only products more expensive than every product in Category 2 are displayed.
| Feature | ANY | ALL |
|---|---|---|
| Comparison | At least one value | Every value |
| Result Size | Usually Larger | Usually Smaller |
| Condition Strictness | Less Strict | More Strict |
Both Subqueries and Joins are used to retrieve related information from databases, but they serve different purposes.
| Feature | Subqueries | Joins |
|---|---|---|
| Complexity | Easy for logical filtering | Better for combining tables |
| Readability | Often easier | Can become lengthy |
| Performance | May be slower | Often faster |
| Nested Logic | Excellent | Limited |
Subqueries should be optimized carefully, especially when working with large databases.
Banks often identify customers whose account balance exceeds the average balance.
SELECT CustomerName
FROM Customer
WHERE CustomerID IN
(
SELECT CustomerID
FROM Account
WHERE Balance >
(
SELECT AVG(Balance)
FROM Account
)
);
An online shopping platform may identify products priced above the average category price.
SELECT ProductName
FROM Product P
WHERE Price >
(
SELECT AVG(Price)
FROM Product
WHERE CategoryID =
P.CategoryID
);
Universities frequently identify students performing better than the department average.
SELECT StudentName
FROM Student S
WHERE Marks >
(
SELECT AVG(Marks)
FROM Student
WHERE DepartmentID =
S.DepartmentID
);
A query written inside another SQL query.
To solve complex problems using nested queries.
The inner query executes first.
A subquery that returns one value.
A subquery that returns multiple values.
A subquery that depends on the outer query.
A subquery placed inside another subquery.
To compare values against multiple results.
Checks whether records exist.
Checks whether records do not exist.
Comparison with at least one returned value.
Comparison with every returned value.
Yes.
Yes.
Yes.
Yes.
Yes.
Yes.
Often yes, because they execute repeatedly.
Efficient record existence checking.
Simple handling of multiple values.
Yes.
Simplifying complex logic.
In some situations, yes.
Joins are often faster.
To improve performance.
No, it only checks existence.
An error may occur.
Banking, healthcare, education, and e-commerce.
They help retrieve and analyze complex relational data efficiently.
SQL Subqueries are an essential feature of relational database systems. They enable developers to solve complex problems, perform advanced filtering, and retrieve meaningful information using nested query structures. From simple single-row subqueries to advanced correlated and nested subqueries, these techniques are widely used in professional database applications.
By mastering IN, EXISTS, NOT EXISTS, ANY, ALL, correlated subqueries, and optimization techniques, students and developers can write powerful SQL queries suitable for real-world business environments. Strong knowledge of SQL Subqueries is valuable for interviews, database development, reporting systems, and data analysis projects.