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 e.Name, e.Salary
FROM Employees e
WHERE e.Salary > (SELECT AVG(Salary) 
                  FROM Employees 
                  WHERE DepartmentID = e.DepartmentID);

Output: Returns employees earning more than the average salary of their own department.

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

New_Student Table

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

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

Query:

INSERT INTO Student 
SELECT * FROM Temp_Students;

Output

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

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

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

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:

SELECT s.NAME, s.LOCATION, ns.SECTION
FROM Student s
INNER JOIN (
    SELECT ROLL_NO, SECTION 
    FROM New_Student WHERE SECTION = 'A'
) ns
ON s.ROLL_NO = ns.ROLL_NO;

Output

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.

SQL Subqueries in DBMS

Visit Course

SQL Subqueries in DBMS

SQL Subqueries SET-2