Sub Queries (Nested Queries)

1. What is a Sub Query?

A subquery is a query within another query. The inner query executes first and its result is used by the outer query.

• Also called nested query
• Written inside parentheses
• Can be used with SELECT, INSERT, UPDATE, DELETE

2. Sub Query Syntax

SELECT column_name
FROM table_name
WHERE column_name = (
    SELECT column_name
    FROM table_name
    WHERE condition
);

3. Single Row Sub Query

A single-row subquery returns only one value.

SELECT Name
FROM student
WHERE Marks = (
    SELECT MAX(Marks)
    FROM student
);

This query finds the student who scored the highest marks.

4. Multiple Row Sub Query

A multiple-row subquery returns more than one value and uses operators like IN, ANY, ALL.

SELECT Name
FROM student
WHERE CourseID IN (
    SELECT CourseID
    FROM course
    WHERE Duration = 3
);

5. Sub Query using IN

SELECT * FROM student
WHERE RollNo IN (
    SELECT RollNo
    FROM enroll
);

6. Sub Query using ANY

ANY returns TRUE if the condition is satisfied for at least one value returned by the subquery.

SELECT * FROM student
WHERE Marks > ANY (
    SELECT Marks
    FROM student
    WHERE CourseID = 101
);

7. Sub Query using ALL

ALL returns TRUE only if the condition is satisfied for all values returned by the subquery.

SELECT * FROM student
WHERE Marks > ALL (
    SELECT Marks
    FROM student
    WHERE CourseID = 102
);

8. Correlated Sub Query

A correlated subquery depends on the outer query and executes once for each row processed.

SELECT s1.Name
FROM student s1
WHERE s1.Marks > (
    SELECT AVG(s2.Marks)
    FROM student s2
    WHERE s1.CourseID = s2.CourseID
);

9. Execution Flow of Sub Query

Outer Query
   ↑
Uses Result
   ↑
Inner Query (executes first)

10. Sub Query vs Join

Sub Query                    JOIN
--------------------------- ---------------------------
Nested query                 Combines tables directly
Easy to understand           Faster in many cases
May reduce performance       Efficient for large data

Practice Questions

1. What is a subquery?
2. Difference between single-row and multi-row subquery.
3. Explain ANY and ALL operators.
4. What is correlated subquery?
5. Subquery vs Join.

Practice Task