{"id":449,"date":"2018-05-08T14:24:04","date_gmt":"2018-05-08T07:24:04","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=449"},"modified":"2021-11-06T12:07:39","modified_gmt":"2021-11-06T05:07:39","slug":"sql-server-correlated-subquery","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-correlated-subquery\/","title":{"rendered":"SQL Server Correlated Subquery"},"content":{"rendered":"\n

Summary<\/strong>: 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.<\/p>\n\n\n\n

Introduction to the SQL Server correlated subquery #<\/a><\/h2>\n\n\n\n

A correlated subquery is a subquery<\/a> that uses the values of the outer query. In other words, the correlated subquery depends on the outer query for its values. <\/p>\n\n\n\n

Because of this dependency, a correlated subquery cannot be executed independently as a simple subquery.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

Consider the following products<\/code> table from the sample database<\/a>:<\/p>\n\n\n\n

\"products\"<\/figure>\n\n\n\n

The following example finds the products whose list price is equal to the highest list price of the products within the same category:<\/p>\n\n\n

SELECT<\/span>\n    product_name,\n    list_price,\n    category_id\nFROM<\/span>\n    production.products p1\nWHERE<\/span>\n    list_price IN<\/span> (\n        SELECT<\/span>\n            MAX<\/span> (p2.list_price)\n        FROM<\/span>\n            production.products p2\n        WHERE<\/span>\n            p2.category_id = p1.category_id\n        GROUP<\/span> BY<\/span>\n            p2.category_id\n    )\nORDER<\/span> BY<\/span>\n    category_id,\n    product_name;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the result:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
In this example, for each product evaluated by the outer query, the subquery finds the highest price of all products in its category. <\/p>\n\n\n\n
If the price of the current product is equal to the highest 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.<\/p>\n\n\n\n
As you can see, the correlated subquery is executed once for each product evaluated by the outer query.<\/p>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
  • A correlated subquery is a subquery that uses the values of the outer query <\/li><\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t