SQL Nested Queries

A nested query (or subquery) is an SQL query placed inside another query to make the overall operation more structured and readable. The inner query executes first and its result is then used by the outer query. Subqueries can appear in the SELECT, FROM or WHERE clauses, making them useful for tasks such as filtering, aggregation and retrieving data based on the results of another query. They are helpful for breaking down complex SQL operations into smaller, structured steps that are easier to read and maintain.

Types of Nested Queries in SQL

There are two primary types of nested queries in SQL, Independent Nested Queries and Correlated Nested Queries. Each type has its own use case and benefits depending on the complexity of the task at hand.

We will use the following sample tables to demonstrate nested queries:

1. STUDENT Table

The STUDENT table stores information about students, including their unique ID, name, address, phone number and age.

2. COURSE Table

The COURSE table stores course details, including a unique course ID and course name.

3. STUDENT_COURSE Table

This table maps students to the courses they have enrolled in, with columns for student ID (S_ID) and course ID (C_ID):

1. Independent Nested Queries

In an independent nested query, the execution of the inner query is independent of the outer query. The inner query runs first and its result is used directly by the outer query. Operators like IN, NOT IN, ANY and ALL are commonly used with independent nested query.

Example 1: Using IN

In this Example we will find the S_IDs of students who are enrolled in the courses ‘DSA’ or ‘DBMS’. We can break the query into two parts:

Step 1: Find the C_IDs of the courses:

This query retrieves the IDs of the courses named 'DSA' or 'DBMS' from the COURSE table.

SELECT C_ID FROM COURSE WHERE C_NAME IN ('DSA', 'DBMS');

Output

Step 2: Use the result of Step 1 to find the corresponding S_IDs:

The inner query finds the course IDs and the outer query retrieves the student IDs associated with those courses from the STUDENT_COURSE table

SELECT S_ID FROM STUDENT_COURSE 
WHERE C_ID IN (
  SELECT C_ID FROM COURSE WHERE C_NAME IN ('DSA', 'DBMS')
);

Output

Explanation: The inner query finds the course IDs of DSA and DBMS. The outer query then retrieves the student IDs enrolled in those courses.

2. Correlated Nested Queries

In correlated nested queries, the inner query depends on the outer query for its execution. For each row processed by the outer query, the inner query is executed. This means the inner query references columns from the outer query. The EXISTS keyword is often used with correlated queries.

Example 2: Using EXISTS

In this Example, we will find the names of students who are enrolled in the course with C_ID = 'C1':

SELECT S_NAME FROM STUDENT S
WHERE EXISTS (
  SELECT 1 FROM STUDENT_COURSE SC
  WHERE S.S_ID = SC.S_ID AND SC.C_ID = 'C1'
);

Output

Explanation: For each student, the inner query checks if they are enrolled in C1. If yes, that student’s name is returned.

Common SQL Operators for Nested Queries

SQL provides several operators that can be used with nested queries to filter, compare and perform conditional checks.

1. IN Operator

The IN operator is used to check whether a column value matches any value in a list of values returned by a subquery. This operator simplifies queries by avoiding the need for multiple OR conditions.

Example: Retrieve student names who enrolled in ‘DSA’ or ‘DBMS’:

SELECT S_NAME FROM STUDENT
WHERE S_ID IN (
  SELECT S_ID FROM STUDENT_COURSE
  WHERE C_ID IN (
    SELECT C_ID FROM COURSE WHERE C_NAME IN ('DSA', 'DBMS')
  )
);

Output

Explanation:

• The innermost query fetches course IDs of ‘DSA’ and ‘DBMS’.
• The middle query finds student IDs enrolled in those courses.
• The outer query retrieves names of those students.

2. NOT IN Operator

The NOT IN operator excludes rows based on a set of values from a subquery. It is particularly useful for filtering out unwanted results. This operator helps identify records that do not match the conditions defined in the subquery.

Example: Retrieve student IDs not enrolled in ‘DSA’ or ‘DBMS’:

SELECT S_ID FROM STUDENT
WHERE S_ID NOT IN (
  SELECT S_ID FROM STUDENT_COURSE
  WHERE C_ID IN (
    SELECT C_ID FROM COURSE WHERE C_NAME IN ('DSA', 'DBMS')
  )
);

Output

Explanation:

• The inner queries first collect IDs of students enrolled in DSA or DBMS.
• The outer query returns only those student IDs that are not in that list.

3. ANY Operator

It compares a value with any value returned by the subquery. If at least one comparison is true, the condition is satisfied.

Example: Retrieve student names whose age is greater than at least one student from Delhi

SELECT S_NAME FROM STUDENT
WHERE S_AGE > ANY (
  SELECT S_AGE FROM STUDENT WHERE S_ADDRESS = 'DELHI'
);

Output

Explanation:

• The inner query fetches ages of students living in Delhi.
• The outer query checks which students have an age greater than at least one of those ages.
• Returns students who satisfy that condition.

4. ALL Operator

It compares a value with all values returned by the subquery. The condition is satisfied only if it is true for every value.

Example: Retrieve student names whose age is greater than all students from Delhi

SELECT S_NAME FROM STUDENT
WHERE S_AGE > ALL (
  SELECT S_AGE FROM STUDENT WHERE S_ADDRESS = 'DELHI'
);

Output

Explanation:

• The inner query fetches ages of all students from Delhi.
• The outer query looks for students whose age is greater than every one of those ages.
• Since no student fulfills this condition, no rows are returned.