Difference between a correlated subquery and a nested subquery

Question

Explain the difference between a correlated subquery and a nested subquery

Jaish Mathews · Answer

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).

Jignesh Kumar · Answer

Hello Kiran, Key differnce between these two : Co-related Query You can see below example : I t depends on the e1.DepartmentID from the outer query its depend on outer query so The subquery is re-evaluated for every row in the outer query. SELECT e1.EmployeeID, e1.Salary FROM Employees e1 WHERE e1.Salary > ( SELECT AVG(e2.Salary) FROM Employees e2 WHERE e2.DepartmentID = e1.DepartmentID ); Nested SubQuery: You can see below example : The subquery ( SELECT DepartmentID FROM Departments ) runs independently and retrieves department IDs for New York. The outer query uses this result to filter employees. SELECT EmployeeID, Salary FROM Employees WHERE DepartmentID IN ( SELECT DepartmentID FROM Departments WHERE Location = 'New York' ); Final Conclusion : Use a correlated subquery when the subquery's result depends on each row of the outer query. Use a nested subquery when the subquery can be executed independently of the outer query.