Ladder T-sql: Beyond basic Level 2: Writing subqueries

Gregory larsen,2016/01/01 (first released: 2014/01/29)

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

this Series

This article is part of the stair series: T-SQL Staircase: Beyond the basics

from his staircase to the T-SQL Dml,gregory Larsen covers more advanced aspects of the T-SQL language, such as subqueries.

before you start creating a basic For more complex SQL code for Transact-SQL statements, you may find that you need to use the results of other SELECT statements to limit the query. When a SELECT statement is embedded in a parent Transact-SQL statement, these embedded SELECT statements are referred to as subqueries or correlated subqueries. At this level of the "beyond the basics" stair, I'll discuss the different aspects of a subquery, and at a future level, I'll discuss the related subqueries.

What is a subquery?

A subquery is just a Select statement, which is contained in another Transact-SQL statement. You can use subqueries in any place where you can use an expression. Many subqueries return a single column value because they are associated with the comparison operator (=,! =,<,<=,>,> =) or an expression in conjunction with. When a subquery is not used as an expression or when using a comparison operator, it can return multiple values. In addition, subqueries can even return multiple columns and values when used in the FROM clause or keyword exists.

Sub-queries are easy to Found in Transact-SQL statements, because it will be the SELECT statement in parentheses. Because subqueries are included 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 a blank set of rows.

another form of a subquery is a correlated subquery. However, the dependent subquery cannot be run independently of the external Transact SQL statement. Related subqueries Use 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.

There are a few things to consider when working with subqueries:

L. does not allow text, text, and image data types to be returned from a subquery

L. The ORDER BY clause cannot be used for subqueries unless the top operator is used

L. Cannot update views that use subqueries

L. COMPUTE and INTO clauses cannot be used in subqueries

Sample subquery Sample Data

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

Example of a subquery that returns a single value

As mentioned above, a subquery used in an expression or a value that returns one side of the comparison operator needs to return a single value. There are many different places in Transact-SQL statements that require a subquery to return a single column value, such as the WHERE clause in the select list. In this section, I'll provide a series of examples that show how to use subqueries as expressions or with comparison operators to meet different business requirements.

subqueries in a column list

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

L. Return all Sales.SalesOrderHeader records what has OrderDate equals "2007-02-19 00:00:00.000"

L. List of returned records by SalesOrderID

L. Returns the number of rows per row of the last row RowNumber 1, the next largest rownumber is 2, etc.

The L. Result set requires a column named Totalorders, which needs to populate the total order quantity orderdate equals "2007-02-19 00:00:00.000".

The code that satisfies these requirements is listed in listing 1.

SELECT row_number () over (ORDER by SalesOrderID) RowNumber

, (SELECT COUNT (*)

from [Sales]. [SalesOrderHeader]

WHERE ModifiedDate = ' 2007-02-19 00:00:00.000 ')

As Totalorders

, *

from [Sales]. [SalesOrderHeader]

WHERE OrderDate = ' 2007-02-19 00:00:00.000 ';

Listing 1: Subqueries in the column list

in this single In a Transact-SQL statement, you see two different select clauses. A subquery is a SELECT statement that is embedded in the middle of the statement in Listing 1, and it has parentheses around it. I have deleted the subquery statement 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 (*)

from [Sales]. [SalesOrderHeader]

WHERE OrderDate = ' 2007-02-19 00:00:00.000 '

Listing 2: Sub-query statements in Listing 1

by listing this subquery in the list of columns, this Transact-SQL statement in List 1 can count the number of SalesOrderHeader rows that OrderDate as "2007-02-19 00:00:00.000", This information is returned along with the details for the row information about the Sales.SalesOrderHeader record with the same OrderDate value.

Example of a WHERE clause neutron query

sometimes you want to base The result of the SELECT statement to drive the WHERE clause condition. When you are in the SELECT statement in the WHERE clause, this SELECT statement is actually a subquery. To demonstrate the use of subqueries in the WHERE clause, assume that you need to display a Sales.SalesOrderDetail record that contains the purchase of a oversized long sleeve logo sweatshirt. The code in Listing 3 satisfies my display requirements by using subqueries.

SELECT * FROM [Sales]. [SalesOrderDetail]

WHERE ProductID = (SELECT ProductID

from [Production]. [Product]

WHERE Name = ' 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 ProductID of the Product.product record, where the product name is "Long-sleeve Logo JERSEY,XL". This subquery allows me to find all Sales.SalesOrderDetail records for the ProductID associated with the product name "Long-sleeve Logo JERSEY,XL".

use sub-queries to control Examples of top clauses

Use The number of rows returned by the TOP clause can be controlled by an expression. The code in Listing 5 identifies the number of sales.salesorderdetail rows that should be returned based on the subquery in the TOP clause.

Select Top (select top 1 OrderQty

from [Sales]. [SalesOrderDetail]

ORDER by ModifiedDate) *

from [Sales]. [SalesOrderDetail]

WHERE ProductID = 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 subqueries to control the number of rows returned by the TOP clause, you can construct a subquery that dynamically identifies the number of rows returned from the query at run time.

Sub-clause example

in order to demonstrate the The HAVING clause uses subqueries, assuming you have the following business requirements:

Build contains Sales.SalesOrderHeader.OrderDate and the result set of the order quantity for each date, where the order quantity exceeds the number of orders executed on "2006-05-01".

to meet this requirement, I developed a query in Listing 6 that uses a subquery in the HAVING clause.

SELECT Count (*), OrderDate

from [Sales]. [SalesOrderHeader]

GROUP by OrderDate

Having count (*) >

(SELECT count (*)

from [Sales]. [SalesOrderHeader]

WHERE OrderDate = ' 2006-05-01 00:00:00.000 ');

Sub-query in the manifest 5:having clause

The code in listing 5 has a subquery to the right of the HAVING clause and uses the Count function in my subquery to determine the order quantity on "2006-05-01".

Example of using a subquery in a function call

to demonstrate the use of subqueries in function calls, assume that you need to display The number of days between OrderDate and the maximum OrderDate recorded for each sales.salesorderheader. The code in Listing 6 conforms to this requirement.

SELECT SalesOrderID

, OrderDate

, DATEDIFF

(

Dd,orderdate

, (SELECT MAX (OrderDate)

from [Sales]. [SalesOrderHeader])

) as Daysbetweenorders

, (SELECT MAX (OrderDate)

from [Sales]. [SalesOrderHeader])

As Maxorderdate

from [Sales]. [SalesOrderHeader];

Listing 6: Subqueries in a function call

The code in listing 6 has two different sub-queries. Two subqueries return the maximum OrderDate in the Sales.SalesOrderHeader table. But 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

all of my examples so far contain subqueries that return only 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.

Sample subquery in the FROM clause

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

SELECT SalesOrderID

From (SELECT TOP ten SalesOrderID

from [Sales]. [SalesOrderDetail]

WHERE ProductID = 716

ORDER by ModifiedDate DESC) as last10salesorders;

Sub-query in the manifest 7:from clause

• The code in Listing 7 uses a subquery in the FROM clause to create a table alias named Last10salesorders. My subquery returns the last 10 Sales.alesorderdetail records that contain ProductID 716.

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 build a more complex from syntax that combines the results of a subquery with other tables or other subqueries, as shown in Listing 8.

SELECT DISTINCT OrderDate

From (SELECT TOP ten SalesOrderID

from [Sales]. [SalesOrderDetail]

WHERE ProductID = 716

ORDER by ModifiedDate DESC) as Last10salesorders

JOIN [Sales]. [SalesOrderHeader] As SalesOrderHeader

On last10salesorders.salesorderid = Salesorderheader.salesorderid

ORDER by OrderDate

Listing 8: Using actual table joins to derive tables

in Listing 8, I see the subquery/derived table I created in Listing 7 and add it to the SalesOrderHeader table. By doing so, I can determine the last 10 orders ProductID = 716 of OrderDate.

Use a Example of a subquery in a keyword

The place where you can write a subquery that returns multiple values for a column is when your subquery is generated with the When the In keyword is used together with the recordset. 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]

WHERE Name like '%xl% ');

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

The code in listing 9 uses a subquery to return a different ProductID value from the Product.product table whose name contains the character "XL". These ProductID values returned from the subquery are then used in the In keyword to constrain which rows are returned from the Sales.SalesOrderDetail table.

Example of using a subquery in a statement that modifies data

so far, all of my examples have been demonstrating how to Sub-queries are used in different parts of the SELECT statement. You can also use subqueries in insert,update or DELETE statements. The code in Listing 10 shows how to use a subquery in an INSERT statement.

DECLARE @SQTable TABLE (

OrderID int,

OrderDate datetime,

TotalDue Money,

Maxorderdate datetime);

--INSERT with subquery

INSERT into @SQTable

SELECT SalesOrderID,

OrderDate,

TotalDue,

(SELECT MAX (OrderDate)

from [Sales]. [SalesOrderHeader])

from [Sales]. [SalesOrderHeader]

WHERE CustomerID = 29614;

--Display Records

SELECT * from @SQtable;

Subquery in the manifest 10:insert statement

In the code in listing 10, I use a subquery to calculate the value to insert the column maxorderdate. This is just one 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.

sub-query and Performance considerations between Joins

If you have read the The "Subquery Basics" document generated by Microsoft (http://technet.microsoft.com/en-us/library/ms189575 (v=sql.105). aspx), Then you may have run the performance of the statement that contains the subquery in this statement:

"In Transact-SQL, statements that contain subqueries and those that do not have a semantically similar version typically have no performance differences.

To compare the performance of a query that uses subqueries with an equivalent query that does not use subqueries, I will rewrite my subquery in Listing 3 to use the join operation. Listing 11 shows the join query I rewritten, which is equivalent to the query in Listing 3.

SELECT sod.*

from [Sales]. [SalesOrderDetail] As SOD

INNER JOIN

[Production]. [Product] As P

On SOD. ProductID = P.productid

WHERE p.name = ' Long-sleeve Logo Jersey, XL ';

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

to compare the performance of the query in Listing 3 using a subquery 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;

--Listing 3 query

SELECT * FROM [Sales]. [SalesOrderDetail]

WHERE ProductID = (SELECT ProductID

From Production.Product

WHERE Name = ' Long-sleeve Logo Jersey, XL ');

--Listing Query

SELECT sod.*

from [Sales]. [SalesOrderDetail] As SOD

INNER JOIN

[Production]. [Product] As P

On SOD. ProductID = P.productid

WHERE p.name = ' Long-sleeve Logo Jersey, XL ';

Listing 12: Testing Performance codes for listing 3 and listing 4

After running the code in listing 12, I reviewed the message generated by the "SET STATISTICS" statement. By looking at the statistics, I found that the two queries had 3,309 logical reads for the SalesOrderDetail table and two logical reads for the product table, each using a CPU of Ms. I also looked at the execution plans that SQL Server created for these two queries. I found that SQL Server generated the same execution plan for both. So, for my case, using a subquery or a join query yields equivalent performance, as Microsoft has recorded.

Profile

a subquery is embedded in another The SELECT statement for the Transact-SQL statement. Subqueries can run independently of external queries, and are sometimes referred to as stand-alone queries. Remember, any time you have a subquery instead of an expression, or use it with a comparison operator, it can only return a column and a 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.

Questions and Answers

In this section, you can see what you know by using the subquery concept by answering the following questions.

question 1:

complete the sentence "one subquery is a SELECT statement in another Transact-SQL statement, _____________________."

• Cannot run independently of the full query.

• A reference column from an external query.

• When run independently of an external query, it returns the result.

Question 2:

When does a subquery need only one column and value to return (select all that apply)?

• When using subqueries in the FROM clause

• Using subqueries in the IN clause

• When you use a subquery in an expression

• When subqueries are used with comparison operators

Question 3:

in the Transact-SQL statements that use a subquery in the WHERE clause are always slower than equivalent queries that do not contain subqueries (TRUE or false).

· True

· False

Reply:

question 1:

the correct answer is c. A subquery can run independently of an external query and return a result. It does not require any columns from an external query, and if it has columns from an external query, it is called a correlated subquery.

Question 2:

the correct answer is C and D. When used as an expression or in a comparison operation, a subquery needs to return a column value. When a subquery is used with the In keyword, it can return single or multiple values for a column. If a subquery is used in the FROM clause, it can return only one column and one value, but it may also return multiple columns and values.

Question 3:

the correct answer is wrong. the SQL Server optimizer is very smart and is likely to calculate the same execution plan for two equivalent queries. If the execution plan for a query that contains subqueries and the execution plan for queries that do not have subqueries eventually have the same execution plan, then two queries will have the same performance.

Ladder T-sql: Beyond basic Level 2: Writing subqueries