2778a_05

7/22/2019 2778A_05

1/27

Module 5:

Working with Subqueries

7/22/2019 2778A_05

2/27

Module 5: Working with Subqueries

Writing Basic Subqueries

Writing Correlated Subqueries

Comparing Subqueries with Joins and Temporary Tables

Using Common Table Expressions

7/22/2019 2778A_05

3/27

Lesson 1: Writing Basic Subqueries

What Are Subqueries?

Using Subqueries as Expressions

Using the ANY, ALL, and SOME Operators

Scalar versus Tabular Subqueries

Rules for Writing Subqueries

7/22/2019 2778A_05

4/27

What Are Subqueries?

Queries nested inside a SELECT, INSERT, UPDATE, or

DELETE statement

Can be used anywhere an Expression is allowed

SELECT ProductID, Name

FROM Production.Product

WHERE Color NOT IN

(SELECT Color


WHERE ProductID = 5)

ProductID Name

--------------------------------------

1 Adjustable Race

2 Bearing Ball

...

(504 row(s) affected)

Result Set:

Example:

7/22/2019 2778A_05

5/27

Using Subqueries as Expressions

SELECT Name, ListPrice,

(SELECT AVG(ListPrice) FROM Production.Product)AS Average, ListPrice

(SELECT AVG(ListPrice) FROM Production.Product)

AS Difference


WHERE ProductSubcategoryID = 1

A Subquery can be substituted anywhere an expression can beused in the following statements, except in an ORDER BY list:

SELECT

UPDATE

INSERT

DELETE

Example:

Name ListPrice Average Difference

---------------------------------------------------------

Mountain-100 Silver, 38 3399.99 438.6662 2961.3238

Mountain-100 Silver, 42 3399.99 438.6662 2961.3238

...


Result Set:

7/22/2019 2778A_05

6/27

Using the ANY, ALL, and SOME Operators

Comparison operators that introduce a subquery can

be modified by the keywords ALL or ANY

SELECT Name

FROM Production.ProductWHERE ListPrice >= ANY

(SELECT MAX (ListPrice)


GROUP BY ProductSubcategoryID)

SOME is an ISO standard equivalent for ANY

SELECT NameFROM Production.Product

WHERE ListPrice >= ALL

(SELECT MAX (ListPrice)


GROUP BY ProductSubcategoryID)

Name

---------------------LL Mountain Seat

ML Mountain Seat ...


Name

--------------------

Road-150 Red, 62

Road-150 Red, 44...

(5 row(s) affected)

ANY Example:

ALL Example:

Result Sets

7/22/2019 2778A_05

7/27

Scalar versus Tabular Subqueries

create table T1 (a int, b int)

create table T2 (a int, b int)

select *from T1

where T1.a > (select max(T2.a)

from T2 where T2.b < T1.b)

A scalar subquery returns a single row of data, whilea tabular subquery returns multiple rows of data

SELECT NameFROM Production.Product

WHERE ListPrice =

(SELECT ListPrice


WHERE Name = 'Chainring Bolts' )

a b

----------------------...

(0 row(s) affected)

Name------------------

Adjustable Race

Bearing Ball ...


Scalar Subquery:

Tabular Subquery:

Result Sets

7/22/2019 2778A_05

8/27

7/22/2019 2778A_05

9/27

Lesson 2: Writing Correlated Subqueries

What Are Correlated Subqueries?

Building a Correlated Subquery

Using Correlated Subqueries

Using the EXISTS Clause with Correlated Subqueries

7/22/2019 2778A_05

10/27

What Are Correlated Subqueries?

Outer query passes columnvalues to the inner query

USE northwind

SELECT orderid, customerid

FROM orders AS or1

WHERE 20 < (SELECT quantity

FROM [order details] AS od

WHERE or1.orderid = od.orderid

AND od.productid = 23)

GO

1Inner query uses that valueto satisfy the inner query2

Inner query returns a valueback to the outer query3 The process is repeated for the

next row of the outer query4

Back to Step 1

7/22/2019 2778A_05

11/27

Building a Correlated Subquery

SELECT c.LastName, c.FirstName

FROM Person.Person c JOIN HumanResources.Employee e

ON e.BusinessEntityID = c.BusinessEntityID

WHERE 5000.00 IN

(SELECT Bonus

FROM Sales.SalesPerson sp

WHERE e.BusinessEntityID = sp.BusinessEntityID) ;

SELECT Bonus

FROM Sales.SalesPerson

SELECT c.LastName, c.FirstNameFROM Person.Person c

JOIN HumanResources.Employee e

ON e.BusinessEntityID =

c.BusinessEntityID

Inner Query

Outer Query

Correlated Subquery

+

7/22/2019 2778A_05

12/27

Using Correlated Subqueries

SELECT DISTINCT c.LastName, c.FirstName

FROM Person.Person c JOIN HumanResources.Employee e

ON e.BusinessEntityID = c.BusinessEntityID

WHERE 5000.00 IN

(SELECT Bonus

FROM Sales.SalesPerson sp

WHERE e.BusinessEntityID = sp.BusinessEntityID);

Example:

LastName FirstName

-----------------------------

Ansman-Wolfe Pamela

Saraive Jos

(2 row(s) affected)

Result Set:

Correlated subqueries are executed repeatedly, once

for each row that may be selected by the outer query

7/22/2019 2778A_05

13/27

Using the EXISTS Clause with CorrelatedSubqueries

SELECT Name


WHERE EXISTS

(SELECT * FROM Production.ProductSubcategory

WHERE ProductSubcategoryID =

Production.Product.ProductSubcategoryID

AND Name = 'Wheels')

When a subquery is introduced with the keyword

EXISTS, the subquery functions as an existence test

Name------------------------

LL Mountain Front Wheel

ML Mountain Front Wheel

...


Result Set:

Example:

b h d

7/22/2019 2778A_05

14/27

Lesson 3: Comparing Subqueries with Joins andTemporary Tables

Subqueries versus Joins

Temporary Tables

Subqueries versus Temporary Tables

7/22/2019 2778A_05

15/27

Subqueries versus Joins

Joins can yield better performance in some cases whereexistence must be checked

Joins are performed faster by SQL Server than subqueries

Subqueries can often be rewritten as joins

SQL Server 2008 query optimizer is intelligent enough tocovert a subquery into a join if it can be done

Subqueries are useful for answering questions that aretoo complex to answer with joins

7/22/2019 2778A_05

16/27

Temporary Tables

Local Temporary Tables:

Have a single number sign (#) asthe first character of their names

Visible only to the currentconnection for the user

Deleted when the user disconnects

from SQL Server

Global Temporary Tables:

Have a double number sign (##) asthe first character of their names

Visible to any user once created

Deleted when all users referencingthem disconnect

CREATE TABLE #StoreInfo

(

EmployeeID int,

ManagerID int,

Num int

)

CREATE TABLE ##StoreInfo

(

EmployeeID int,

ManagerID int,Num int

)

7/22/2019 2778A_05

17/27

Subqueries versus Temporary Tables

As subqueries get more complex their performancemay decrease

Maintainability can be easier with subqueries in somesituations, and easier with temporary tables in others

Temporary tables can be easier for some to debugwhile others prefer to work with a single subquery

7/22/2019 2778A_05

18/27

Lesson 4: Using Common Table Expressions

What Are Common Table Expressions?

Writing Common Table Expressions

Writing Recursive Queries by Using Common TableExpressions

7/22/2019 2778A_05

19/27

What Are Common Table Expressions?

Result set can be used in SELECT, INSERT, UPDATE,or DELETE

Advantages of common table expressions:

Queries with derived tables become more readable

Provide traversal of recursive hierarchies

WITH TopSales (SalesPersonID, NumSales) AS

( SELECT SalesPersonID, Count(*)FROM Sales.SalesOrderHeader GROUP BY SalesPersonId )

SELECT * FROM TopSales

WHERE SalesPersonID IS NOT NULL

ORDER BY NumSales DESC

A named temporary result set based on aSELECT queryCommon TableExpression

7/22/2019 2778A_05

20/27

Writing Common Table Expressions

Choose a CTE name and column list1

WITH TopSales (SalesPersonID, NumSales) ASWITH TopSales (SalesPersonID, NumSales) AS

(SELECT SalesPersonID, Count(*)

FROM Sales.SalesOrderHeader GROUP BY SalesPersonId)

Create the CTE SELECT query2

Use the CTE in a query3


(SELECT SalesPersonID, Count(*)

FROM Sales.SalesOrderHeader GROUP BY SalesPersonId)

SELECT LoginID, NumSales

FROM HumanResources.Employee e INNER JOIN TopSales

ON TopSales.SalesPersonID = e.EmployeeID


7/22/2019 2778A_05

21/27


( SELECT SalesPersonID, Count(*)

FROM Sales.SalesOrderHeader GROUP BY SalesPersonId )

Ejecucin

SELECT * FROM TopSales

WHERE SalesPersonID IS NOT NULL


Writing Recursive Queries by Using Common

7/22/2019 2778A_05

22/27

Writing Recursive Queries by Using CommonTable Expressions

Modify CTE SELECT query when creating CTE:

Create the anchor member query (top of recursion tree)1

SELECT ManagerID, EmployeeID

FROM HumanResources.Employee

WHERE ManagerID IS NULL

Add the UNION ALL operator2




UNION ALL

Create the recursive member query that self-references theCTE

3




UNION ALL

SELECT e.ManagerID, e.EmployeeIDFROM HumanResources.Employee e

INNER JOIN HumanResources.Employee mgr

ON e.ManagerID = mgr.EmployeeID

Demonstration: Using Common Table

7/22/2019 2778A_05

23/27

Demonstration: Using Common TableExpressions

In this demonstration, you will see how to:

Write a Common Table Expression

Write a Recursive Query

7/22/2019 2778A_05

24/27

Lab: Working with Subqueries

Exercise 1: Writing Basic Subqueries

Exercise 2: Writing Correlated Subqueries

Exercise 3: Comparing Subqueries with Joins andTemporary Tables

Exercise 4: Using Common Table Expressions

Logon information

Virtual machine NY-SQL-01User name Administrator

Password Pa$$w0rd

Estimated time: 60minutes

7/22/2019 2778A_05

25/27

Lab Scenario

You are a database developer at Adventure Works. You havebeen asked by several managers of the company to prepare a

number of reports in order to help the executive committeedetermine the budget for next year.

7/22/2019 2778A_05

26/27

Lab Review

How are basic subqueries evaluated?

How are correlated subqueries evaluated?

What could a Common Table Expression be used for?

7/22/2019 2778A_05

27/27

Module Review and Takeaways

Review Questions

Best Practices

2778a_05

Documents