Summary: in this tutorial, you will learn about the SQL Server correlated subquery which is a subquery that depends on the outer query for its values.
A correlated subquery is a subquery that uses the values of the outer query. In other words, it depends on the outer query for its values. Because of this dependency, a correlated subquery cannot be executed independently as a simple subquery.
Moreover, a correlated subquery is executed repeatedly, once for each row evaluated by the outer query. The correlated subquery is also known as a repeating subquery.
Consider the following
products table from the sample database:
The following example finds the products whose list price is equal to the maximum list price of its category.
list_price IN (
p2.category_id = p1.category_id
Here is the result:
In this example, for each product evaluated by the outer query, the subquery finds the maximum price of all products in its category. If the price of the current product is equal to the maximum price of all products in its category, the product is included in the result set. This process continues for the next product and so on.
As you can see, the correlated subquery is executed once for each product evaluated by the outer query.
In this tutorial, you have learned about the SQL Server correlated subquery.