SQL Subqueries: Nested, Correlated, and EXISTS/NOT EXISTS Operators

What is a Subquery? (In Simple Words)

A Subquery (also called an Inner Query or Nested Query) is a SQL query enclosed inside parentheses and embedded within another parent SQL query (the Outer Query).

• Think of it as answering a question in two steps:
  • Step 1 (Inner Query): “What is the average salary in the company?” ➔ Returns ₹42,000.
  • Step 2 (Outer Query): “Show me all employees earning more than ₹42,000.”
• Instead of running two separate manual queries, a subquery lets the database execute both in a single command!

1. Classification of Subqueries

2. Single-Row Subqueries

A single-row subquery returns a single scalar value.

Practical Example:

Question: “Find the names and salaries of all employees who earn more than the company average salary.”

SELECT Emp_Name, Salary
FROM Employee
WHERE Salary > (
    SELECT AVG(Salary) 
    FROM Employee
);

• How it runs: The database executes the inner query SELECT AVG(Salary) first. Suppose the average is 45,000. The outer query then becomes WHERE Salary > 45000!

3. Multi-Row Subqueries (IN, ANY, ALL)

When the inner query returns a list of multiple values, single-row operators like > or = cannot be used directly. You must use multi-row comparison operators:

A. The IN Operator

Matches if the outer value equals ANY item in the returned list:

-- Find students who are enrolled in any course offered by Dept 1
SELECT Student_Name 
FROM Student
WHERE Dept_ID IN (
    SELECT Dept_ID 
    FROM Department 
    WHERE Building = 'Science Block'
);

B. The ANY (or SOME) Operator

Compares a value to each value in a list:

• > ANY (list): Greater than the minimum value in the list.
• < ANY (list): Less than the maximum value in the list.

-- Find employees who earn more than AT LEAST ONE employee in Dept 2
SELECT Emp_Name, Salary
FROM Employee
WHERE Salary > ANY (
    SELECT Salary 
    FROM Employee 
    WHERE Dept_ID = 2
);

C. The ALL Operator

Compares a value to EVERY value in a list:

• > ALL (list): Greater than the maximum value in the list.
• < ALL (list): Less than the minimum value in the list.

-- Find employees who earn more than EVERY employee in Dept 2
SELECT Emp_Name, Salary
FROM Employee
WHERE Salary > ALL (
    SELECT Salary 
    FROM Employee 
    WHERE Dept_ID = 2
);

4. Correlated Subqueries (Dependent Subqueries)

In an ordinary subquery, the inner query runs just once, and its result is handed to the outer query.

In a Correlated Subquery, the inner query depends directly on a column of the current row being examined by the outer query!

• As a result, the inner query executes repeatedly — once for every single candidate row in the outer query!

Classic Exam Scenario:

Question: “Find employees who earn more than the average salary of THEIR OWN department.”

SELECT E1.Emp_Name, E1.Dept_ID, E1.Salary
FROM Employee E1
WHERE E1.Salary > (
    SELECT AVG(E2.Salary)
    FROM Employee E2
    WHERE E2.Dept_ID = E1.Dept_ID   -- Correlated link!
);

• For an employee in Dept 10, the inner query calculates the average salary of Dept 10.
• For the next employee in Dept 20, the inner query recalculates the average salary of Dept 20!

5. The EXISTS and NOT EXISTS Operators

The EXISTS operator tests for the existence of any rows returned by a subquery:

• It returns TRUE as soon as the inner query produces at least one row.
• It returns FALSE if the inner query produces an empty result set (0 rows).
• Because it checks only for existence, EXISTS stops scanning as soon as the first matching row is found, making it blazingly fast!

Example 1: EXISTS

-- Find all departments that have at least one employee working in them
SELECT D.Dept_Name
FROM Department D
WHERE EXISTS (
    SELECT 1 
    FROM Employee E 
    WHERE E.Dept_ID = D.Dept_ID
);

Example 2: NOT EXISTS

-- Find all departments that currently have ZERO employees assigned
SELECT D.Dept_Name
FROM Department D
WHERE NOT EXISTS (
    SELECT 1 
    FROM Employee E 
    WHERE E.Dept_ID = D.Dept_ID
);

💡 DAVV Exam Preparation Tips

5-Mark Question: “What is a Correlated Subquery? How does it differ from a standard nested subquery?”

• Explain that a standard subquery runs once independently and passes its result to the outer query.
• A correlated subquery references outer table columns and executes once for each candidate row of the outer table.
• Write the “earn more than average of own department” SQL code.

10-Mark Question: “Explain Single-Row and Multi-Row subqueries in SQL. Discuss the ANY, ALL, and EXISTS operators with complete query examples on a STUDENT-ENROLLMENT database.”

• Detail single-row queries using AVG().
• Detail multi-row queries using IN, > ANY, and > ALL.
• Explain EXISTS and NOT EXISTS boolean checks and why SELECT 1 is conventionally used inside EXISTS.