By: Rick Dobson
Overview
A correlated subquery is a special kind of temporary data store in which the result set for an inner query depends on the current row of its outer query. In contrast, an uncorrelated subquery has the same result set no matter what row is current in its outer query. This section reviews a couple of correlated subquery examples and compares them to alternative formulations based on joins for derived tables. The comparisons rely on an examination of the result sets as well as the execution plans for the alternative formulations. These comparisons shed light on the efficiency of uncorrelated subqueries versus joins between derived tables. The next temporary data store tutorial section will focus more thoroughly on derived tables.
In this section we will cover the following topics:
- Revisit of correlated subquery as a select list item
- Correlated and uncorrelated subqueries in where clauses
Additional Information
Here are some links to resources that you may find useful to help you grow your understanding of content from this section of the tutorial.
- Correlated and uncorrelated subqueries
- Understanding Correlated and Uncorrelated Sub-queries in SQL
- SQL SERVER – Correlated and Noncorrelated – SubQuery Introduction, Explanation and Example
- The Difference Between Subqueries and Derived Tables in SQL
- A SUB-QUERY DOES NOT HURT PERFORMANCE
- SQL Server Window Function Syntax
- SQL Server Cross Join
- Maximizing your view into SQL Query Plans
