{"id":428,"date":"2018-05-08T10:43:37","date_gmt":"2018-05-08T03:43:37","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=428"},"modified":"2021-11-06T12:05:13","modified_gmt":"2021-11-06T05:05:13","slug":"sql-server-subquery","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-subquery\/","title":{"rendered":"SQL Server Subquery"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about the SQL Server subquery and how to use the subquery for querying data.<\/p>\n\n\n\n

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

A subquery is a query nested inside another statement such as SELECT<\/a><\/code>, INSERT<\/a><\/code>, UPDATE<\/a><\/code>, or DELETE<\/a><\/code>.<\/p>\n\n\n\n

Let’s see the following example.<\/p>\n\n\n\n

Consider the orders<\/code> and customers<\/code> tables from the sample database<\/a>.<\/p>\n\n\n\n

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

The following statement shows how to use a subquery in the WHERE<\/a><\/code> clause of a SELECT<\/a><\/code> statement to find the sales orders of the customers located in New York<\/code>:<\/p>\n\n\n

SELECT<\/span>\n    order_id,\n    order_date,\n    customer_id\nFROM<\/span>\n    sales.orders\nWHERE<\/span>\n    customer_id IN<\/span> (\n        SELECT<\/span>\n            customer_id\n        FROM<\/span>\n            sales.customers\n        WHERE<\/span>\n            city = 'New York'<\/span>\n    )\nORDER<\/span> BY<\/span>\n    order_date DESC<\/span>;\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, the following statement is a subquery:<\/p>\n\n\n
SELECT<\/span>\n    customer_id\nFROM<\/span>\n    sales.customers\nWHERE<\/span>\n    city = 'New York'<\/span>\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that you must always enclose the SELECT<\/code> query of a subquery in parentheses ()<\/code>.<\/p>\n\n\n\n
A subquery is also known as an inner query or inner select, while the statement containing the subquery is called an outer select or outer query:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
SQL Server executes the whole query example above as follows:<\/p>\n\n\n\n
First, it executes the subquery to get a list of customer identification numbers of the customers located in New York<\/code>.<\/p>\n\n\n
SELECT<\/span>\n    customer_id\nFROM<\/span>\n    sales.customers\nWHERE<\/span>\n    city = 'New York'<\/span>\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
Second, SQL Server substitutes customer identification numbers returned by the subquery in the IN<\/a><\/code> operator and executes the outer query to get the final result set.<\/p>\n\n\n\n
As you can see, by using the subquery, you can combine two steps. The subquery removes the need for selecting the customer identification numbers and plugging them into the outer query. Moreover, the query itself automatically adjusts whenever the customer data changes.<\/p>\n\n\n\n
Nesting subquery #<\/a><\/h2>\n\n\n\n
A subquery can be nested within another subquery. SQL Server supports up to 32 levels of nesting. Consider the following example:<\/p>\n\n\n
SELECT<\/span>\n    product_name,\n    list_price\nFROM<\/span>\n    production.products\nWHERE<\/span>\n    list_price > (\n        SELECT<\/span>\n            AVG<\/span> (list_price)\n        FROM<\/span>\n            production.products\n        WHERE<\/span>\n            brand_id IN<\/span> (\n                SELECT<\/span>\n                    brand_id\n                FROM<\/span>\n                    production.brands\n                WHERE<\/span>\n                    brand_name = 'Strider'<\/span>\n                OR<\/span> brand_name = 'Trek'<\/span>\n            )\n    )\nORDER<\/span> BY<\/span>\n    list_price;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
First, SQL Server executes the following subquery to get a list of brand identification numbers of the Strider<\/code> and Trek<\/code> brands:<\/p>\n\n\n
SELECT<\/span>\n    brand_id\nFROM<\/span>\n    production.brands\nWHERE<\/span>\n    brand_name = 'Strider'<\/span>\nOR<\/span> brand_name = 'Trek'<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
Second, SQL Server calculates the average price list of all products that belong to those brands.<\/p>\n\n\n
SELECT<\/span>\n    AVG<\/span> (list_price)\nFROM<\/span>\n    production.products\nWHERE<\/span>\n    brand_id IN<\/span> (6<\/span>,9<\/span>)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, SQL Server finds the products whose list price is greater than the average list price of all products with the Strider<\/code> or Trek<\/code> brand.<\/p>\n\n\n\n
SQL Server subquery types #<\/a><\/h2>\n\n\n\n
You can use a subquery in many places:<\/p>\n\n\n\n