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:

• Filter rows based on results from another query.
• Apply aggregate functions like SUM, COUNT, or AVG dynamically.
• Update data using values from other tables.
• Delete rows based on conditions returned by another query.

Example: First, we will create a demo SQL database and tables, on which we will use the SQL Subqueries.

Query

SELECT * FROM Students
WHERE Score >  ( SELECT AVG(Score) FROM Students );

Output:

• The subquery calculates the average score of all students.
• The main query returns only the students whose score is higher than that average.

Syntax:

SELECT column_name
FROM table_name
WHERE column_name expression operator 
   (SELECT column_name FROM table_name WHERE ...);

Note: While there is no universal syntax for subqueries, they are commonly used in SELECT statements as shown.

SQL Clauses for Subqueries

Clauses that can be used with subqueries are:

• Filters rows in the outer query based on the results returned by the WHERE clause.
• Treats the subquery as a temporary (derived) table that can be queried like a normal table using the FROM clause.
• Filters grouped or aggregated results using values produced by the HAVING clause.

Types of Subqueries

Consider the following two tables for examples:

1. Single-Row Subquery

A single-row subquery is a subquery that returns only one value.

• 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:

• Finds the highest salary in the Employees table.
• Returns the employee(s) whose salary matches that maximum value.

2. Multi-Row Subquery

A multi-row subquery is a subquery that returns more than one value.

• 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:

• Identifies all departments located in New York.
• Returns the employees who belong to any of those departments.

3. Correlated Subquery

A correlated subquery is a subquery that depends on the outer query for its values.

• 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:

• Calculates the average salary within each employee's department.
• Returns employees whose salary is higher than their department’s average.

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:

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:

• 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.

Query:

INSERT INTO Student 
SELECT * FROM Temp_Students;

Output:

• 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:

• 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:

• 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:

• 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:

• 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