Translation (vi) the advanced route of--t-sql: 2 Levels above the base: Write subqueries

the advanced Way of T-sql: 2 Levels above the base: Write subqueries Gregory Rassen (Gregory Larsen), 2016/01/01 (first published: 2014/01/29) the series

This article is part of the stair series: the staircase leading to T-sql: Beyond the Basics

From his ladder to T-SQL DML, Gregory Larsen covers more advanced aspects of the T-SQL language, such as subqueries.

When you start to create more complex SQL code than basic Transact-SQL statements, you may find that you need to constrain your query with the results of other SELECT statements. When you embed a SELECT statement in a parent Transact-SQL statement, these embedded SELECT statements are referred to as subqueries or correlated subqueries. On this level, I'll discuss the different aspects of subqueries, and at a later level, I'll discuss the related subqueries.

What is a subquery?

A subquery is a query statement that contains other Transact-SQL statements, a subquery can be used in any expression, and many subquery results return a single result because they are used in the connection comparison operation (=,! =,<=,>,>=) or expression. When a subquery is not used as an expression or with a comparison operator, it can return multiple values. In addition, subqueries can even return multiple columns and values when they are used in the FROM clause or keyword.

Subqueries are easily found in Transact-SQL statements because it will be the SELECT statement contained in parentheses. Because subqueries are contained in Transact-SQL statements, subqueries are often referred to as internal queries. A Transact-SQL statement that contains subqueries is called an external query. Another feature of a subquery is that it can run independently of an external query and will run without errors and may return a set of rows, or return an empty rowset.

Another form of a subquery is a correlated subquery. However, the associated subquery cannot be run independently of the external Transact SQL statement. The associated subquery uses columns or columns from an external query to constrain the results returned from the related subquery. This is sufficient for the related subqueries in this article. I'll explore the related subqueries in the future Staircase article.

The following are other things to consider when working with subqueries:

ntext, text, and image data types are not allowed to be returned from subqueries

The ORDER BY clause cannot be used in a subquery unless the top-level operator is used

Views that use subqueries cannot be updated

COMPUTE and INTO clauses cannot be used in subqueries

sample data for the subquery sample

To demonstrate how to use subqueries, I need some test data. All of my examples will use the ADVENTUREWORKS2008R2 database instead of creating my own test data. If you want to follow and run my example in your environment you can get from here (http://msftdbprodsamples.codeplex.com/releases/view/93587 ) Download the ADVENTUREWORKS2008R2 database.

example of a subquery that returns a single value

As mentioned above, a subquery that uses an expression or a return value on one side of the comparison operator needs to return a value. There are many different places in Transact SQL statements that require subqueries to return a column value, just like in a select list, where clause, and so on. In this section, I will provide a series of examples that will demonstrate the use of subquery expressions or comparison operators to meet different business requirements.

Sub-query column list

The subquery in the list is a SELECT statement that returns a single column value in the column list of the SELECT clause. To demonstrate how to use subqueries in a select list, we assume that a result set with the following business requirements must be generated from the SELECT statement:

Returns all sales data. SalesOrderHeader recorded an order date equal to "2007-02-19 00:00. 00 "

Order returned records in SalesOrderID order

Each row returns the oldest number of rows in the order of 1, the next largest number of rows is 2, and so on

The result set requires a column named Totalorders, which needs to contain one with "2007-02-19 00:00. 00 "The total number of orders for the same OrderDate.

The code that satisfies these requirements is shown in Listing 1.

Select Row_number () over (ORDER by SalesOrderID) RowNumber      , (select COUNT (*) from          [sales].[ SalesOrderHeader]          '2007-02-19 00:00:00.000') as                      totalorders      *'2007-02-19 00:00:00.000';

Listing 1: Subqueries in the column list

In this Transact-SQL statement, you can see two different select clauses. A subquery is a SELECT statement embedded in the statement in Listing 1, surrounded by parentheses. I have extracted the subquery and put it in Listing 2 in case you want to test to verify that it can run independently of the full Transact-SQL statement.

SELECT COUNT (*'2007-02-19 00:00:00.000'

Listing 2: Sub-query statements found in Listing 1

By owning this subquery in the column list, the Transact-SQL statement in Listing1 can calculate a OrderDate "2007-02-19 00:00. 000 "The number of SalesOrderHeader lines, and returns the information as well as detailed line information about the sale. SalesOrderHeader records that have the same OrderDate value.

example of a subquery in a WHERE clause

Sometimes you want to drive a WHERE clause condition based on the result of a SELECT statement. When you select a statement in the WHERE clause, the SELECT statement is actually a subquery. To demonstrate the use of subqueries in a WHERE clause, assume that you need to display sales. SalesOrderDetail includes the purchase of oversized long sleeve logo sweatshirt. The code in Listing 3 uses subqueries to meet my display needs.

SELECT *= (select ProductID from                    [production].[ Product]                    'long-sleeve Logo Jersey, XL'

Sub-query in the manifest 3:where clause

The subquery in Listing 3 is on the right side of the Where condition. This subquery identifies the product being produced. Product name of product name is "long sleeve logo shirt, XL". This subquery allows me to find all the sales. SalesOrderDetail records a product with a product name related to the "Long sleeve logo jersey,xl".

example using a subquery to control the TOP clause

The number of rows returned by using the TOP clause can be controlled by an expression. The code in Listing 5 identifies the sales quantity. SalesOrderDetail rows should be returned based on a subquery in the TOP clause.

1 OrderQty             from [Sales]. [SalesOrderDetail]             *  716;

Sub-query in the manifest 4:top clause

The code in Listing 4 uses the OrderQty value returned from the subquery to identify the value that will be used in the TOP clause. By using a subquery to control the number of rows returned by the TOP clause, you can create a subquery that dynamically identifies the number of rows returned from the query at run time.

examples of sub-queries in the HAVING clause

To demonstrate the use of subqueries in the "have" clause, assume that you have the following business requirements:

The build contains sales. The result set of the SalesOrderHeader. Order date and order quantity for each date, where the order quantity exceeds the order quantity for "2006-05-01".

To meet this requirement, I have developed the query in Listing 6, which uses subqueries in the HAVING clause.

SELECT Count (*), OrderDate from [sales].[ Salesorderheader]group by orderdatehaving Count(*) >       (SELECT count (*) from         [sales].[ SalesOrderHeader]        '2006-05-01 00:00:00.000');

Sub-query in the manifest 5:having clause

The code in Listing 5 has a subquery on the right side of the clause and uses the Count function in my subquery to determine the number of orders placed on "2006-05-01".

examples of using subqueries in function calls

To demonstrate the use of subqueries in function calls, assume that you have a requirement to display the number of days between OrderDate and the maximum OrderDate per sales. SalesOrderHeader Records. The code in Listing 6 satisfies this requirement.

Select SalesOrderID      , OrderDate      , DATEDIFF          (            dd,orderdate        , (SELECT MAX (OrderDate) from          [ Sales]. [SalesOrderHeader])          ) As Daysbetweenorders         , (SELECT MAX (OrderDate) from        [sales].[ SalesOrderHeader]) as             Maxorderdatefrom [sales].[ SalesOrderHeader];

Listing 6: Subqueries in a function call

The code in Listing 6 has two different sub-queries. Two subqueries all return the Max OrderDate in sales. SalesOrderHeader table. However, the first subquery is used to pass a date to the second argument of the DateDiff function.

Example of a subquery that returns multiple values

So far, all of my examples contain subqueries, which only return a single value in a single column. Not all sub-queries have this requirement. The next few examples will use subqueries that return multiple values and/or multiple columns.

Example of a subquery in the FROM clause

In the FROM clause, you typically determine which table or table collection your Transact-SQL statement will target. Each table provides a set of records that your query uses to determine the final result set for your query. A subquery can be thought of as a query that returns a set of records, so it can be used as a table in the FROM clause. The query in Listing 7 shows how I can use subqueries in the FROM clause. When you use a subquery in the FROM clause, the result set generated by the subquery is often referred to as a derived table.

Ten SalesOrderID       from [Sales]. [SalesOrderDetail]       716       ORDER by ModifiedDate DESC) as last10salesorders;

Sub-query in the manifest 7:from clause

The code in Listing 7 uses the subquery in the FROM clause to create a table alias named Last10 SalesOrders. My subquery returned the last 10 sales. The Alesorderdetail record contains a 716 product.

The code in Listing 7 is a very simple example of how to use a subquery in the FROM clause. By using subqueries in the FROM clause, you can easily construct more complex code from the syntax that joins the subquery results of other tables, or add additional subqueries, as shown in Listing 8.

Ten SalesOrderID       from [Sales]. [SalesOrderDetail]       716 = Salesorderheader.salesorderidorder by OrderDate       

Listing 8: Adding a derived table with an actual table

In Listing 8, I used the subquery/derived table created in Listing 7 and connected it to the SalesOrderHeader table. By doing so, I can determine a different order date, which is the last 10 times people ordered the Product = 716.

Examples of subqueries using the IN keyword

Another place where you can write a subquery that returns multiple values for a column when the subquery generates a recordset for the keyword. The code in Listing 9 demonstrates how to use a subquery to pass a value to the in keyword.

SELECT * from [sales].[ SalesOrderDetail] WHERE ProductID in         (SELECT ProductID from          [production].[ Product]         '%xl%');

Listing 9: Using a subquery to pass values to the IN keyword

The code in Listing 9 uses a subquery to return different values for the product. Product table that contains the character "XL". These ProductID values returned from the subquery are then used in the In keyword to constrain the rows returned from the sale. SalesOrderDetail table.

Examples of using subqueries in statements that modify data

So far, all of my examples demonstrate how to use subqueries in different parts of a SELECT statement. Subqueries can also be used in insert, UPDATE, or DELETE statements. The code in Listing 10 shows how to use a subquery in an INSERT statement.

int , OrderDate datetime,totaldue money,maxorderdate datetime); -- INSERT with Subqueryinsert into @SQTable    select SalesOrderID,          OrderDate,           TotalDue,           (select MAX (OrderDate)            from [Sales]. [SalesOrderHeader])    from [Sales]. [SalesOrderHeader]    29614 ; -* from @SQtable;

Listing 10: Sub-query in INSERT statement

In the code in Listing 10, I use a subquery to calculate the value inserted into the column maxorderdate. This is just an example of how to use a subquery in an INSERT statement. Keep in mind that subqueries can also be used in update and/or DELETE statements.

Performance considerations between sub-queries and joins

If you have read the "sub-query Fundamentals" document by Microsoft (http://technet.microsoft.com/en-us/library/ms189575 (v = sql.105). aspx), Then you may have run the statement in this statement about performance with subqueries:

"In Transact-SQL, there is usually no performance difference between statements that contain subqueries and semantic equivalents. ”

To compare the performance of queries that use subqueries and those that do not use subqueries, I will rewrite the subquery in Listing 3 to use the join operation. Listing 11 shows the join query I rewritten, which corresponds to the query in Listing 3.

SELECT sod.*='long-sleeve Logo Jersey, XL';

Listing 11: Connection query equivalent to the query in Listing 3

In order to compare the performance of the query using the subquery in Listing 3 and the query in Listing 11 using join, I will run two queries using the code in Listing 12.

SET STATISTICS IO on; SET STATISTICS time on;--Listing3Queryselect*From [Sales]. [SalesOrderDetail] WHERE ProductID=(SELECT ProductID from production.product WHERE Name='long-sleeve Logo Jersey, XL'); --Listing Onequeryselect SOD.*From [Sales]. [SalesOrderDetail] As Sodinner JOIN [Production]. [Product] As PON SOD. ProductID=P.productidwhere p.name='long-sleeve Logo Jersey, XL';

Listing 12: Code to test the performance of listing 3 and listing 4

After running the code in Listing 12, I looked at the message generated by the "SET STATISTICS" statement. Looking at the statistics, I found that two queries had 3,309 logical reads for the SalesOrderDetail table, 2 logical reads for the product table, and 31 milliseconds for each CPU. In addition, I reviewed the execution plan for SQL Server that was created for these two queries. I found that SQL Server produced the same execution plan for both cases. So, in my case, using a subquery or a connection query produces equivalent performance, as Microsoft has recorded.

Summary

A subquery is a SELECT statement that embeds another Transact-SQL statement. Subqueries can run independently of external queries, and are sometimes referred to as stand-alone queries. Keep in mind that whenever a subquery overrides an expression, or if it is used with a comparison operator, it can only return a single column and value. You can often rewrite subqueries using join logic. Subqueries are powerful tools that help you build more complex Transact-SQL statements to meet your business needs.

Original link: http://www.sqlservercentral.com/articles/Stairway+Series/104517/

 

Translation (vi) the advanced route of--t-sql: 2 Levels above the base: Write subqueries