Subquery in sql server

A subquery is a query nested inside another statement such as SELECTINSERTUPDATE, or DELETE.
Let’s see the following example.
Consider the orders and customers tables from the sample database.
The following statement shows how to use a subquery in the WHERE clause of a SELECT statement to find the sales orders of the customers who locate in New York:
Here is the result:
SQL Server Subquery example
In this example, the following statement is a subquery:
Note that you must always enclose the SELECT query of a subquery in parentheses ().
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:
SQL Server Subquery
SQL Server executes the whole query example above as follows:
First, it executes the subquery to get a list of customer identification numbers of the customers who locate in New York.
SQL Server Subquery result
Second, SQL Server substitutes customer identification numbers returned by the subquery in the IN operator and executes the outer query to get the final result set.
As you can see, by using the subquery, you can combine two steps together. 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.

Nesting subquery

A subquery can be nested within another subquery. SQL Server supports up to 32 levels of nesting. Consider the following example:
SQL Server Subquery nesting subquery examples
First, SQL Server executes the following subquery to get a list of brand identification numbers of the Strider and Trek brands:
SQL Server Subquery brand id list
Second, SQL Server calculates the average price list of all products that belong to those brands.
Third, SQL Server finds the products whose list price is greater than the average list price of all products with the Strider or Trek brand.

SQL Server subquery types

You can use a subquery in many places:

SQL Server subquery is used in place of an expression

If a subquery returns a single value, it can be used anywhere an expression is used.
In the following example, a subquery is used as a column expression named max_list_price in a SELECT statement.
SQL Server subquery is used in place of an expression

SQL Server subquery is used with IN operator

A subquery that is used with the IN operator returns a set of zero or more values. After the subquery returns values, the outer query makes use of them.
The following query finds the names of all mountain bikes and road bikes products that the Bike Stores sell.
SQL Server subquery is used with IN operator
This query is evaluated in two steps:
  1. First, the inner query returns a list of category identification numbers that match the names Mountain Bikes and code Road Bikes.
  2. Second, these values are substituted into the outer query that finds the product names which have the category identification number match with one of the values in the list.

SQL Server subquery is used with ANY operator

The subquery is introduced with the ANY operator has the following syntax:
Assuming that the subquery returns a list of value v1, v2, … vn. The ANY operator returns TRUE if one of a comparison pair (scalar_expression, vi) evaluates to TRUE; otherwise, it returns FALSE.
For example, the following query finds the products whose list prices are greater than or equal to the average list price of any product brand.
SQL Server subquery is used with ANY operator
For each brand, the subquery finds the maximum list price. The outer query uses these max prices and determines which individual product’s list price is greater than or equal to any brand’s maximum list price.

SQL Server subquery is used with ALL operator

The ALL operator has the same syntax as the ANY operator:
The ALL operator returns TRUE if all comparison pairs (scalar_expression, vi) evaluate to TRUE; otherwise, it returns FALSE.
The following query finds the products whose list price is greater than or equal to the average list price returned by the subquery:
SQL Server subquery is used with ALL operator

SQL Server subquery is used with EXISTS or NOT EXISTS

The following illustrates the syntax of a subquery introduced with EXISTS operator:
The EXISTS operator returns TRUE if the subquery return results; otherwise it returns FALSE.
On the other hand, the NOT EXISTS is opposite to the EXISTS operator.
The following query finds the customers who bought products in 2017:
SQL Server subquery is used with EXISTS operator
If you use the NOT EXISTS instead of EXISTS, you can find the customers who did not buy any products in 2017.
SQL Server subquery is used with NOT EXISTS operator

SQL Server subquery in the FROM clause

Suppose that you want to find the average of the sum of orders of all sales staff. To do this, you can first find the number of orders by staffs:
SQL Server subquery in the FROM clause
Then, you can apply the AVG() function to this result set. Since a query returns a result set that looks like a virtual table, you can place the whole query in the FROM clause of another query like this:
The query that you place in the FROM clause must have a table alias. In this example, we used the t as the table alias for the subquery.  To come up with the final result SQL Server carries the following steps:
  • Execute the subquery in the FROM clause.
  • Use the result of the subquery and execute the outer query.
In this tutorial, you have learned about the SQL Server subquery concept and how to use various subquery types to query data.

Post a Comment

0 Comments