SQL Correlated Subqueries Explained

What is a Correlated Subquery?

A correlated subquery is a subquery that uses values from the outer query. Unlike a regular subquery, it cannot be executed independently because it depends on the outer query's current row.

This means the subquery is evaluated repeatedly, once for each row processed by the outer query.

• References columns from the outer query.
• Executes once per outer query row.
• Used for row-wise filtering or comparison.

Correlated vs Non-Correlated Subqueries

Understanding the difference between correlated and non-correlated subqueries is essential for writing efficient SQL queries.

Writing Correlated Subqueries

Correlated subqueries are typically used in WHERE or SELECT clauses to filter or compute values based on related data.

Here is a basic example to illustrate the concept.

Performance Considerations

Because correlated subqueries execute once per outer row, they can be less efficient than joins or non-correlated subqueries.

To optimize performance, consider the following best practices.

• Use indexes on columns involved in the subquery predicates.
• Rewrite correlated subqueries as joins when possible.
• Avoid complex calculations inside the subquery.
• Analyze query execution plans to identify bottlenecks.