SQLServerCh06

Chapter 6 How to code subqueries Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 1 Objectives Applied  Code SELECT statements that require subqueries.  Code SELECT statements that use common table expressions (CTEs) to define the subqueries. Knowledge  Describe the way subqueries can be used in the WHERE, HAVING, FROM and SELECT clauses of a SELECT statement.  Describe the difference between a correlated subquery and a noncorrelated subquery.  Describe the use of common table expressions (CTEs). Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 2 Four ways to introduce a subquery in a SELECT statement 1. 2. 3. 4. In a WHERE clause as a search condition In a HAVING clause as a search condition In the FROM clause as a table specification In the SELECT clause as a column specification Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 3 A subquery in the WHERE clause SELECT InvoiceNumber, InvoiceDate, InvoiceTotal FROM Invoices WHERE InvoiceTotal > (SELECT AVG(InvoiceTotal) FROM Invoices) ORDER BY InvoiceTotal; The value returned by the subquery 1879.7413 The result set (21 rows) Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 4 Where a subquery can be introduced If a subquery returns… A single value It can be introduced… Anywhere an expression is allowed A result set with a single column In place of a list of values A result set with one or more columns In place of a table in the FROM clause Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 5 A query that uses an inner join SELECT InvoiceNumber, InvoiceDate, InvoiceTotal FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID WHERE VendorState = 'CA' ORDER BY InvoiceDate; The result set (40 rows) Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 6 The same query restated with a subquery SELECT InvoiceNumber, InvoiceDate, InvoiceTotal FROM Invoices WHERE VendorID IN (SELECT VendorID FROM Vendors WHERE VendorState = 'CA') ORDER BY InvoiceDate; The same result set (40 rows) Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 7 Advantages of joins  The result of a join operation can include columns from both tables.  A join tends to be more intuitive when it uses an existing relationship between two tables.  A query with a join typically performs faster than the same query with a subquery. Advantages of subqueries  A subquery can pass an aggregate value to the outer query.  A subquery tends to be more intuitive when it uses an ad hoc relationship between two tables.  Long, complex queries can sometimes be easier to code using subqueries. Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 8 The syntax of a WHERE clause that uses an IN phrase with a subquery WHERE test_expression [NOT] IN (subquery) A query that returns vendors without invoices SELECT VendorID, VendorName, VendorState FROM Vendors WHERE VendorID NOT IN (SELECT DISTINCT VendorID FROM Invoices); Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 9 The result of the subquery (34 rows) The result set (88 rows) Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 10 The query restated without a subquery SELECT Vendors.VendorID, VendorName, VendorState FROM Vendors LEFT JOIN Invoices ON Vendors.VendorID = Invoices.VendorID WHERE Invoices.VendorID IS NULL; The same result set (88 rows) Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 11 The syntax of a WHERE clause that uses a comparison operator WHERE expression comparison_operator [SOME|ANY|ALL] (subquery) A query with a subquery in the WHERE condition SELECT InvoiceNumber, InvoiceDate, InvoiceTotal, InvoiceTotal - PaymentTotal - CreditTotal AS BalanceDue FROM Invoices WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0 AND InvoiceTotal - PaymentTotal - CreditTotal < (SELECT AVG(InvoiceTotal - PaymentTotal - CreditTotal) FROM Invoices WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0) ORDER BY InvoiceTotal DESC; Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 12 The value returned by the subquery 2910.9472 The result set (9 rows) Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 13 How the ALL keyword works Condition Equivalent expression x x x x x > 2 x < 1 (x = 1) AND (x = 2) (x 1) AND (x 2) > ALL (1, 2) < ALL (1, 2) = ALL (1, 2) ALL (1, 2) Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 14 A query that uses the ALL keyword SELECT VendorName, InvoiceNumber, InvoiceTotal FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID WHERE InvoiceTotal > ALL (SELECT InvoiceTotal FROM Invoices WHERE VendorID = 34) ORDER BY VendorName; Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 15 The result of the subquery The result set (25 rows) Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 16 How the ANY and SOME keywords work Condition Equivalent expression x x x x x > 1 x < 2 (x = 1) OR (x = 2) (x 1) OR (x 2) > ANY (1, 2) < ANY (1, 2) = ANY (1, 2) ANY (1, 2) Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 17 A query that uses the ANY keyword SELECT VendorName, InvoiceNumber, InvoiceTotal FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID WHERE InvoiceTotal < ANY (SELECT InvoiceTotal FROM Invoices WHERE VendorID = 115); Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 18 The result of the subquery The result set (17 rows) Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 19 A query that uses a correlated subquery SELECT VendorID, InvoiceNumber, InvoiceTotal FROM Invoices AS Inv_Main WHERE InvoiceTotal > (SELECT AVG(InvoiceTotal) FROM Invoices AS Inv_Sub WHERE Inv_Sub.VendorID = Inv_Main.VendorID) ORDER BY VendorID, InvoiceTotal; The value returned by the subquery for vendor 95 28.5016 The result set (36 rows) Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 20 Terms  Subquery  Subquery predicate  Correlated subquery  Noncorrelated subquery  Correlation name  Derived table Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 21 The syntax of a subquery with EXISTS WHERE [NOT] EXISTS (subquery) A query that returns vendors without invoices SELECT VendorID, VendorName, VendorState FROM Vendors WHERE NOT EXISTS (SELECT * FROM Invoices WHERE Invoices.VendorID = Vendors.VendorID); The result set (88 rows) Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 22 A subquery coded in the FROM clause SELECT Invoices.VendorID, MAX(InvoiceDate) AS LatestInv, AVG(InvoiceTotal) AS AvgInvoice FROM Invoices JOIN (SELECT TOP 5 VendorID, AVG(InvoiceTotal) AS AvgInvoice FROM Invoices GROUP BY VendorID ORDER BY AvgInvoice DESC) AS TopVendor ON Invoices.VendorID = TopVendor.VendorID GROUP BY Invoices.VendorID ORDER BY LatestInv DESC; Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 23 The derived table generated by the subquery The result set Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 24 A correlated subquery in the SELECT clause SELECT DISTINCT VendorName, (SELECT MAX(InvoiceDate) FROM Invoices WHERE Invoices.VendorID = Vendors.VendorID) AS LatestInv FROM Vendors ORDER BY LatestInv DESC; The result set (122 rows) Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 25 The same query restated using a join SELECT VendorName, MAX(InvoiceDate) AS LatestInv FROM Vendors LEFT JOIN Invoices ON Vendors.VendorID = Invoices.VendorID GROUP BY VendorName ORDER BY LatestInv DESC; The same result set (122 rows) Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 26 A complex query that uses three subqueries SELECT Summary1.VendorState, Summary1.VendorName, TopInState.SumOfInvoices FROM (SELECT V_Sub.VendorState, V_Sub.VendorName, SUM(I_Sub.InvoiceTotal) AS SumOfInvoices FROM Invoices AS I_Sub JOIN Vendors AS V_Sub ON I_Sub.VendorID = V_Sub.VendorID GROUP BY V_Sub.VendorState, V_Sub.VendorName) AS Summary1 Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 27 A complex query (continued) JOIN (SELECT Summary2.VendorState, MAX(Summary2.SumOfInvoices) AS SumOfInvoices FROM (SELECT V_Sub.VendorState, V_Sub.VendorName, SUM(I_Sub.InvoiceTotal) AS SumOfInvoices FROM Invoices AS I_Sub JOIN Vendors AS V_Sub ON I_Sub.VendorID = V_Sub.VendorID GROUP BY V_Sub.VendorState, V_Sub.VendorName) AS Summary2 GROUP BY Summary2.VendorState) AS TopInState ON Summary1.VendorState = TopInState.VendorState AND Summary1.SumOfInvoices = TopInState.SumOfInvoices ORDER BY Summary1.VendorState; Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 28 The result set (10 rows) Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 29 A procedure for building complex queries 1. 2. 3. 4. State the problem to be solved by the query in English. Use pseudocode to outline the query. If necessary, use pseudocode to outline each subquery. Code the subqueries and test them to be sure that they return the correct data. 5. Code and test the final query. Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 30 Pseudocode for the query SELECT Summary1.VendorState, Summary1.VendorName, TopInState.SumOfInvoices FROM (Derived table returning VendorState, VendorName, SumOfInvoices) AS Summary1 JOIN (Derived table returning VendorState, MAX(SumOfInvoices)) AS TopInState ON Summary1.VendorState = TopInState.VendorState AND Summary1.SumOfInvoices = TopInState.SumOfInvoices ORDER BY Summary1.VendorState; Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 31 The code for the Summary1 and Summary2 subqueries SELECT V_Sub.VendorState, V_Sub.VendorName, SUM(I_Sub.InvoiceTotal) AS SumOfInvoices FROM Invoices AS I_Sub JOIN Vendors AS V_Sub ON I_Sub.VendorID = V_Sub.VendorID GROUP BY V_Sub.VendorState, V_Sub.VendorName; The result of the Summary1 and Summary2 subqueries (34 rows) Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 32 Pseudocode for the TopInState subquery SELECT Summary2.VendorState, MAX(Summary2.SumOfInvoices) FROM (Derived table returning VendorState, VendorName, SumOfInvoices) AS Summary2 GROUP BY Summary2.VendorState; The result of the TopInState subquery (10 rows) Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 33 The syntax of a CTE WITH cte_name1 AS (query_definition1) [, cte_name2 AS (query_definition2)] [...] sql_statement Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 34 Two CTEs and a query that uses them WITH Summary AS ( SELECT VendorState, VendorName, SUM(InvoiceTotal) AS SumOfInvoices FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID GROUP BY VendorState, VendorName ), TopInState AS ( SELECT VendorState, MAX(SumOfInvoices) AS SumOfInvoices FROM Summary GROUP BY VendorState ) SELECT Summary.VendorState, Summary.VendorName, TopInState.SumOfInvoices FROM Summary JOIN TopInState ON Summary.VendorState = TopInState.VendorState AND Summary.SumOfInvoices = TopInState.SumOfInvoices ORDER BY Summary.VendorState; Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 35 The result set (10 rows) Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 36 The Employees table Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 37 A recursive CTE that returns hierarchical data WITH EmployeesCTE AS ( -- Anchor member SELECT EmployeeID, FirstName + ' ' + LastName As EmployeeName, 1 As Rank FROM Employees WHERE ManagerID IS NULL UNION ALL -- Recursive member SELECT Employees.EmployeeID, FirstName + ' ' + LastName, Rank + 1 FROM Employees JOIN EmployeesCTE ON Employees.ManagerID = EmployeesCTE.EmployeeID ) SELECT * FROM EmployeesCTE ORDER BY Rank, EmployeeID; Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 38 The final result set Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 39 Terms  Common table expression (CTE)  Recursive query  Recursive CTE Murach's SQL Server 2012, C6 © 2012, Mike Murach & Associates, Inc. Slide 40