Difference between a correlated subquery and a nested subquery

A correlated subquery and a nested subquery are two types of subqueries in SQL, but they differ in how they interact with the main query. Here's a detailed explanation:

1. Correlated Subquery

A correlated subquery is a subquery that depends on values from the outer query. It is executed repeatedly, once for each row evaluated by the outer query.

• Key Characteristics:

  • The subquery refers to columns from the outer query.
  • The subquery cannot be executed independently of the outer query.
  • It is evaluated row by row.

• Example:

  SELECT e.EmployeeID, e.Salary
  FROM Employees e
  WHERE e.Salary > (SELECT AVG(s.Salary)
                    FROM Employees s
                    WHERE s.DepartmentID = e.DepartmentID);
  

  • Here, the subquery calculates the average salary for the department of the current employee (e.DepartmentID).
  • It runs once for each row in the Employees table.

• Performance:

  • Correlated subqueries can be slow for large datasets because they are evaluated repeatedly.

2. Nested Subquery

A nested subquery (also called a simple subquery) is executed independently of the outer query. The result of the subquery is passed to the outer query as input.

• Key Characteristics:

  • The subquery is self-contained and does not refer to columns in the outer query.
  • The subquery is executed only once (or as many times as required by the SQL engine optimization).

• Example:

  SELECT EmployeeID, Salary
  FROM Employees
  WHERE Salary > (SELECT AVG(Salary) FROM Employees);
  

  • Here, the subquery calculates the average salary for all employees independently of the outer query.
  • The result is then compared to the Salary column of each row in the outer query.

• Performance:

  • Nested subqueries are generally more efficient than correlated subqueries because they are executed only once.

Comparison Table

Aspect	Correlated Subquery	Nested Subquery
Dependency	Depends on the outer query.	Independent of the outer query.
Execution	Executed repeatedly for each row in the outer query.	Executed once (or as optimized by the engine).
Performance	Slower, especially for large datasets.	Faster due to single execution.
Use Case	When the subquery needs data from the outer query.	When the subquery can work independently.

Which One to Use?

• Use nested subqueries when the subquery result is independent of the outer query, as they are more efficient.
• Use correlated subqueries when the subquery requires values from the outer query to perform its calculations.

In general, where possible, avoid correlated subqueries for performance reasons and consider alternatives like JOIN or WITH (Common Table Expressions).