Module 4: Joining Data from Multiple Tables

1. Module 4:Joining Data from Multiple Tables

2. Module 4: Joining Data from Multiple Tables • Querying Multiple Tables by Using Joins • Applying Joins for Typical Reporting Needs • Combining and Limiting Result Sets

3. Lesson 1: Querying Multiple Tables by Using Joins • Fundamentals of Joins • Categorizing Statements by Types of Joins • Joining Data Using Inner Joins • Joining Data Using Outer Joins • Joining Data Using Cross Joins • Identifying the Potential Impact of a Cartesian Product

4. Fundamentals of Joins Joins: • Select Specific Columns from Multiple Tables • JOIN keyword specifies that tables are joined and how to join them • ON keyword specifies join condition • Query Two or More Tables to Produce a Result Set • Use Primary and Foreign Keys as join conditions • Use columns common to specified tables to join tables Simplified JOIN Syntax: FROM first_tablejoin_type second_table [ON (join_condition)]

5. Categorizing Statements by Types of Joins • Inner Join • Includes equi-joins and natural joins • Use comparison operators to match rows • Outer Join • Includes left, right, or full outer joins • Cross Join • Also called Cartesian products • Self Join • Refers to any join used to join a table to itself

6. Joining Data Using Inner Joins • An inner join is a join in which the values in the columns being joined are compared using a comparison operator Example: SELECT e.LoginID FROM HumanResources.Employee AS e INNER JOIN Sales.SalesPerson AS s ON e.BusinessEntityID = s.BusinessEntityID Result Set: LoginID ------------------------- adventure-works\syed0 adventure-works\david8 adventure-works\garrett1 ... (17 row(s) affected)

7. Joining Data Using Outer Joins • Outer Joins return all rows from at least one of the tables or views mentioned in the FROM clause Example: SELECT p.Name, pr.ProductReviewID FROM Production.Product p LEFT OUTER JOIN Production.ProductReview pr ON p.ProductID = pr.ProductID Result Set: Name ProductReviewID ---------------------------------- Adjustable Race NULL Bearing Ball NULL ... (505 row(s) affected)

8. Joining Data Using Cross Joins • In a Cross Join, each row from the left table is combined with all rows from the right table Example: SELECT p.BusinessEntityID, t.Name AS Territory FROM Sales.SalesPerson p CROSS JOIN Sales.SalesTerritory t ORDER BY p.BusinessEntityID Result Set: BusinessEntityID Territory ---------------------------- 274 Northwest 274 Northeast ... (170 row(s) affected) Use CROSS JOINs with caution if you do not need a true Cartesian Product

9. Identifying the Potential Impact of a Cartesian Product A Cartesian Product: • Is defined as all possible combinations of rows in all tables ü • Results in a rowset containing the number of rows in the first table times the number of rows in the second ü • Can result in huge result sets that take several hours to complete! ü

10. Demonstration: Querying a Table Using Joins In this demonstration, you will see how to: • Query the Table Using an Inner Join • Query the Table Using an Outer Join • Query the Table Using a Cross Join

11. Lesson 2: Applying Joins for Typical Reporting Needs • Joining Three or More Tables • Joining a Table to Itself • Joining Tables by Using Non-Equi Joins • Joining Tables in a User-Defined Function

12. Joining Three or More Tables • FROM clauses can contain multiple Join specifications which allows many tables to be joined in a single Query Example: SELECT p.Name, v.Name FROM Production.Product p JOIN Purchasing.ProductVendor pv ON p.ProductID = pv.ProductID JOIN Purchasing.Vendor v ON pv.BusinesEntityID = v.BusinessEntityID WHERE ProductSubcategoryID = 15 ORDER BY v.Name Result Set: Name Name ----------------------------------------------- LL Mountain Seat/Saddle Chicago City Saddles ML Mountain Seat/Saddle Chicago City Saddles ... (18 row(s) affected)

13. Joining a Table to Itself • A Table can be Joined to itself by using a Self-Join Example: SELECT DISTINCT pv1.ProductID, pv1.BusinessEntityID FROM Purchasing.ProductVendor pv1 INNER JOIN Purchasing.ProductVendor pv2 ON pv1.ProductID = pv2.ProductID AND pv1.BusinessEntityID <> pv2.BusinessEntityID ORDER BY pv1.ProductID Result Set: ProductID BusinessEntityID ------------------------------ 317 1578 317 1678 ... (347 row(s) affected)

14. Joining Tables by Using Non-Equi Joins • The same Operators and Predicates used for Inner Joins can be used for Not-Equal Joins Example: SELECT DISTINCT p1.ProductSubcategoryID, p1.ListPrice FROM Production.Product p1 INNER JOIN Production.Product p2 ON p1.ProductSubcateogoryID = p2.ProductSubcategoryID AND p1.ListPrice <> p2.ListPrice WHERE p1.ListPrice < $15 AND p2.ListPrice < $15 ORDER BY ProductSubcategoryID Result Set: ProductSubcateogoryID ListPrice ----------------------------------- 23 8.99 23 9.50 ... (8 row(s) affected)

15. Joining Tables in a User-Defined Function • User-defined functions can be used to focus, simplify, and customize the perception each user has of the database Example: CREATE FUNCTION Sales.ufn_SalesByStore (@storeidint) RETURNS TABLE AS RETURN ( SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'YTD Total‘ FROM Production.Product AS P JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID WHERE SH.CustomerID = @storeid GROUP BY P.ProductID, P.Name ); SELECT * FROM Sales.ufn_SalesByStore (29825) Result Set: Product ID Name YTD Total ------------------------------------------------ 707 Sport-100 Helmet, Red 620.250910 708 Sport-100 Helmet, Black 657.636610

16. Demonstration: Joining Tables In this demonstration, you will see how to: • Join Three or More Tables • Join a Table to Itself • Join a Table using a Non-Equi Join

17. Lesson 3: Combining and Limiting Result Sets • Combining Result Sets by Using the UNION Operator • Limiting Result Sets by Using the EXCEPT and INTERSECT Operators • Identifying the Order of Precedence of UNION, EXCEPT, and INTERSECT • Limiting Result Sets by Using the TOP and TABLESAMPLE Operators • Categorizing Statements that Limit Result Sets

18. Combining Result Sets by Using the UNION Operator • UNION combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union Example: SELECT * FROM testa UNION ALL SELECT * FROM testb; Result Set: columna columnb ------------------ 100 test 100 test ... (8 row(s) affected) The number and order of columns must be the same in all queries and all data types must be compatible

19. Limiting Result Sets by Using the EXCEPT and INTERSECT Operators • EXCEPT returns any distinct values from the query to the left of the EXCEPT operand that are not also returned from the right query • INTERSECT returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand Result Sets EXCEPT Example: ProductID ------------------ 429 ... (266 row(s) affected) SELECT ProductID FROM Production.Product EXCEPT SELECT ProductID FROM Production.WorkOrder INTERSECT Example: ProductID ------------------ 3 ... (238 row(s) affected) SELECT ProductID FROM Production.Product INTERSECT SELECT ProductID FROM Production.WorkOrder

20. Identifying the Order of Precedence of UNION, EXCEPT, and INTERSECT EXCEPT, INTERSECT, and UNION are evaluated in the context of the following precedence: Expressions in parentheses 1 The INTERSECT operand 2 EXCEPT and UNION evaluated from Left to Right based on their position in the expression 3

21. Limiting Result Sets by Using the TOP and TABLESAMPLE Operators • TOP and TABLESAMPLE limit the number of rows returned in a result set Result Sets FirstName LastName -------------------- Syed Abbas Catherine Abel ... (15 row(s) affected) TOP Example: SELECT TOP (15) FirstName, LastName FROM Person.Person TABLESAMPLE Example: FirstName LastName -------------------- Eduardo Barnes Edward Barnes ... (199 row(s) affected) SELECT FirstName, LastName FROM Person.Person TABLESAMPLE (1 PERCENT)

22. Categorizing Statements That Limit Result Sets • UNION • Combines the results of two or more SELECT statements into a single result set • EXCEPT and INTERSECT • Compares the results of two or more SELECT statements and return distinct values • TOP • Specifies that only the first set of rows will be returned from the query result • TABLESAMPLE • Limits the number of rows returned from a table in the FROM clause to a sample number or PERCENT of rows

23. Demonstration: Combining and Limiting Result Sets In this demonstration, you will see how to: • Combine Result Sets • Limit Result Sets using TABLESAMPLE • Limit Result Sets using TOP

24. Lab: Joining Data from Multiple Tables • Exercise 1: Querying Multiple Tables by Using Joins • Exercise 2: Applying Joins for Typical Reporting Needs • Exercise 3: Combining and Limiting Result Sets Logon information Estimated time: 60 minutes

25. Lab Scenario • You are a database developer at Adventure Works. You have been asked by the various managers to prepare several reports for use in the quarterly financial statements being produced by the company. To create these reports you will use several different joins and join operators.

26. Lab Review • What results did the Inner Join in Exercise 1 return? • What results did the Left Outer Join and Right Outer Join in Exercise 1 return? • Why was the ProductVendor table given two different table aliases in the FROM clause of Exercise 2? • What would happen if we added an ORDER BY clause to the TOP select statement in Exercise 3?

27. Module Review and Takeaways • Review Questions • Best Practices