23.1 What is a Subquery?

A Subquery (or Nested Query) is a query within another SQL query. It is typically used to provide a value or a set of values to the outer query.

23.2 Types of Nested Queries

1. Independent (Non-Correlated) Subquery

The inner query is independent of the outer query. It runs once, returns a result, and the outer query uses that result.

• Example: "Find employees whose salary is more than the average salary."

    SELECT Name FROM Emp 
    WHERE Salary > (SELECT AVG(Salary) FROM Emp);

2. Correlated Subquery

The inner query refers to a column from the outer query. The inner query is executed once for every row processed by the outer query.

• Example: "Find employees who earn more than the average salary in their department."

    SELECT e1.Name FROM Emp e1 
    WHERE e1.Salary > (SELECT AVG(e2.Salary) FROM Emp e2 WHERE e2.DeptID = e1.DeptID);

23.3 Set-Comparison Operators

When a subquery returns multiple rows, we use special operators: