SQL Subquery
A subquery in SQL is a query nested inside another SQL query. It allows complex filtering,
aggregation and data manipulation by using the result of one query inside another. They are an
essential tool when we need to perform operations like:
Filtering: selecting rows based on conditions from another query.
Aggregating: applying functions like SUM, COUNT, AVG with dynamic conditions.
Updating: modifying data using values from other tables.
Deleting: removing rows based on criteria from another query.
While there is no universal syntax for subqueries, they are commonly used in SELECT
statements as follows.
Syntax:
SELECT column_name
FROM table_name
WHERE column_name expression operator
(SELECT column_name FROM table_name WHERE ...);
Common SQL Clauses for Subqueries
Clauses that can be used with subqueries are:
WHERE: filter rows based on subquery results.
FROM: treat subquery as a temporary (derived) table.
HAVING: filter aggregated results after grouping.
Types of Subqueries
1. Single-Row Subquery
Returns exactly one row as the result.
Commonly used with comparison operators such as =, >, <
Example:
SELECT * FROM Employees
WHERE Salary = (SELECT MAX(Salary) FROM Employees);
Output: Returns the employee(s) with the highest salary.
2. Multi-Row Subquery
Returns multiple rows as the result.
Requires operators that can handle multiple values, such as IN, ANY or ALL
Example:
SELECT * FROM Employees
WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE Location =
'New York');
Output: Fetches employees working in all New York departments.
3. Correlated Subquery
A dependent subquery: it references columns from the outer query.
Executed once for each row of the outer query, making it slower for large datasets.
Example:
SELECT [Link], [Link]
FROM Employees e
WHERE [Link] > (SELECT AVG(Salary)
FROM Employees
WHERE DepartmentID = [Link]);
Output: Returns employees earning more than the average salary of their own department.
1
Examples of Using SQL Subqueries
These examples showcase how subqueries can be used for various operations like selecting,
updating, deleting or inserting data, providing insights into their syntax and functionality.
Through these examples, we will understand flexibility and importance of subqueries in
simplifying complex database tasks.
Consider the following two tables:
Student Table
NAME ROLL_NO LOCATION PHONE_NUMBER
Ram 101 Chennai 9988775566
Raj 102 Coimbatore 8877665544
Sasi 103 Madurai 7766553344
Ravi 104 Salem 8989898989
Sumathi 105 Kanchipuram 8989856868
New_Student Table
NAME ROLL_NO SECTION
Ravi 104 A
Sumathi 105 B
Raj 102 A
Example 1: Fetching Data Using Subquery in WHERE Clause
This example demonstrates how to use a subquery inside the WHERE clause. The inner query
retrieves roll numbers of students who belong to section 'A' and the outer query fetches their
corresponding details (name, location and phone number) from the Student table.
Query:
SELECT NAME, LOCATION, PHONE_NUMBER
FROM Student
WHERE ROLL_NO IN (
SELECT ROLL_NO FROM New_Student WHERE SECTION = 'A'
);
Output
2
NAME LOCATION PHONE_NUMBER
Ravi Salem 8989898989
Raj Coimbatore 8877665544
Explanation:
The subquery SELECT ROLL_NO FROM New_Student WHERE SECTION = 'A' finds roll
numbers of students in section A.
The outer query then uses these roll numbers to fetch details from the Student table.
Thus, only Ravi and Raj are returned, since they are in section A.
Example 2: Using Subquery with INSERT
Here we demonstrate how a subquery can be used with INSERT. Instead of manually entering
data, we copy data from another table (Temp_Students) into the main Student table.
Temp_Students
NAME ROLL_NO LOCATIONPHONE_NUMBER
Ajay 201 Mumbai 9090901234
Meena 202 Delhi 8080805678
Query:
INSERT INTO Student
SELECT * FROM Temp_Students;
Output
NAME ROLL_NO LOCATION PHONE_NUMBER
Ram 101 Chennai 9988775566
Raj 102 Coimbatore 8877665544
Sasi 103 Madurai 7766553344
Ravi 104 Salem 8989898989
Sumathi 105 Kanchipuram 8989856868
Ajay 201 Mumbai 9090901234
3
NAME ROLL_NO LOCATION PHONE_NUMBER
Meena 202 Delhi 8080805678
Explanation:
The subquery SELECT * FROM Temp_Students selects all rows from the helper table.
The INSERT INTO Student adds these rows into the main Student table.
Thus, Ajay and Meena are successfully added.
Example 3: Using Subquery with DELETE
In this example, we use a subquery with DELETE to remove certain rows from the Student table.
Instead of hardcoding roll numbers, the subquery finds them based on conditions.
Query:
DELETE FROM Student
WHERE ROLL_NO IN (
SELECT ROLL_NO FROM Student WHERE ROLL_NO <= 101 OR ROLL_NO = 201
);
Output
NAME ROLL_NO LOCATION PHONE_NUMBER
Raj 102 Coimbatore 8877665544
Sasi 103 Madurai 7766553344
Ravi 104 Salem 8989898989
Sumathi 105 Kanchipuram 8989856868
Meena 202 Delhi 8080805678
Explanation:
The subquery selects roll numbers 101 and 201.
The outer query deletes students having those roll numbers.
As a result, Ram (101) and Ajay (201) are removed.
Example 4: Using Subquery with UPDATE
Subqueries can also be used with UPDATE. In this example, we update student names to
"Geeks" if their location matches the result of a subquery.
Query:
UPDATE Student
SET NAME = 'Geeks'
WHERE LOCATION IN (
SELECT LOCATION FROM Student WHERE LOCATION IN ('Salem', 'Delhi')
);
Output
4
NAME ROLL_NO LOCATION PHONE_NUMBER
Raj 102 Coimbatore 8877665544
Sasi 103 Madurai 7766553344
Geeks 104 Salem 8989898989
Sumathi 105 Kanchipuram 8989856868
Geeks 202 Delhi 8080805678
Explanation:
The subquery selects locations 'Salem' and 'Delhi'.
The outer query updates the NAME field for students whose location matches those values.
Thus, Ravi and Meena are renamed to "Geeks".
Example 5: Simple Subquery in the FROM Clause
This example demonstrates using a subquery inside the FROM clause, where the subquery acts
as a temporary (derived) table.
Query:
SELECT NAME, PHONE_NUMBER
FROM (
SELECT NAME, PHONE_NUMBER, LOCATION
FROM Student
WHERE LOCATION LIKE 'C%'
) AS subquery_table;
Output
NAME PHONE_NUMBER
Ram 9988775566
Raj 8877665544
Explanation:
The subquery (SELECT NAME, PHONE_NUMBER, LOCATION FROM Student WHERE
LOCATION LIKE 'C%') fetches students whose location starts with "C" (Coimbatore,
Chennai, etc.).
The outer query then selects only NAME and PHONE_NUMBER from this derived table.
Only Raj qualifies because his location is Coimbatore.
Example 6: Subquery with JOIN
We can also use subqueries along with JOIN to connect data across tables.
Query:
5
SELECT [Link], [Link], [Link]
FROM Student s
INNER JOIN (
SELECT ROLL_NO, SECTION
FROM New_Student WHERE SECTION = 'A'
) ns
ON s.ROLL_NO = ns.ROLL_NO;
Output
NAME LOCATION SECTION
Ravi Salem A
Raj Coimbatore A
Explanation: The subquery extracts roll numbers of students in section A. Joining this with the
Student table on ROLL_NO returns Ravi and Raj along with their locations and section.