Understanding Table Joins in SQL

Exploring various types of table joins in SQL, including Cross Join, Inner Join, Outer Join, and Self Join. Learn how these joins work, when to use them, and see practical examples for better comprehension.

• SQL Joins
• Database
• Cross Join
• Inner Join
• Self Join

giuliet Follow

Uploaded on Sep 15, 2024 | 0 Views

Download Presentation

Please find below an Image/Link to download the presentation.

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.

Presentation Transcript

1. Module 4: Joining Tables Lecture 1: Table Joins 9/15/2024 Pierce College CIS261 SQL 1

2. Joins Cross Join Cartesian product. Simply merges two tables. Inner Join Cross join with a condition. Used to find matching records in the two tables Outer Join Used to find un matched rows in the two tables Self Join Joining a table with itself https://technet.microsoft.com/en-us/library/ms191472(v=sql.105).aspx, 05/26/2015 9/15/2024 Pierce College CIS261 SQL 2

3. Cross Join A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The following example shows a Transact-SQL cross join. USE AdventureWorks2008R2; GO SELECT p.BusinessEntityID, t.Name AS Territory FROM Sales.SalesPerson p CROSS JOIN Sales.SalesTerritory t ORDER BY p.BusinessEntityID; The result set contains 170 rows (SalesPerson has 17 rows and SalesTerritory has 10; 17 multiplied by 10 equals 170). http://technet.microsoft.com/en-us/library/ms190690(v=sql.105).aspx, 05/26/2015 9/15/2024 Pierce College CIS261 SQL 3

4. CROSS JOIN/Cartesian Product 170 (17*10) rows returned 9/15/2024 Pierce College CIS261 SQL 4

5. Self Join The following example uses a self-join to find the products that are supplied by more than one vendor. Because this query involves a join of the ProductVendor table with itself, the ProductVendor table appears in two roles. To distinguish these roles, you must give the ProductVendor table two different aliases (pv1 and pv2) in the FROM clause. These aliases are used to qualify the column names in the rest of the query. This is an example of the self-join Transact-SQL statement: 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 http://technet.microsoft.com/en-us/library/ms177490(v=sql.105).aspx, 05/26/2015 9/15/2024 Pierce College CIS261 SQL 5

6. SELF JOIN 9/15/2024 Pierce College CIS261 SQL 6

7. Inner Join An inner join is a join in which the values in the columns being joined are compared using a comparison operator. In the ISO standard, inner joins can be specified in either the FROM or WHERE clause. This is the only type of join that ISO supports in the WHERE clause. Inner joins specified in the WHERE clause are known as old-style inner joins. The following Transact-SQL query is an example of an inner join: SELECT * FROM HumanResources.Employee AS e INNER JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID ORDER BY p.LastName http://technet.microsoft.com/en-us/library/ms177490(v=sql.105).aspx, 05/26/2105 9/15/2024 Pierce College CIS261 SQL 7

8. INNER JOIN 9/15/2024 Pierce College CIS261 SQL 8

9. Inner Join You can also join values in two columns that are not equal. The same operators and predicates used for inner joins can be used for not-equal joins. For more information about the available operators and predicates that can be used in joins, see Using Operators in Expressions and WHERE (Transact-SQL). The following example uses a less-than (<) join to find sales prices of product 718 that are less than the list price recommended for that product. SELECT DISTINCT p.ProductID, p.Name, p.ListPrice, sd.UnitPrice AS 'Selling Price' FROM Sales.SalesOrderDetail AS sd JOIN Production.Product AS p ON sd.ProductID = p.ProductID AND sd.UnitPrice < p.ListPrice WHERE p.ProductID = 718; http://technet.microsoft.com/en-us/library/ms177490(v=sql.105).aspx, 05/26/2015 9/15/2024 Pierce College CIS261 SQL 9

10. INNER JOIN 9/15/2024 Pierce College CIS261 SQL 10

11. Inner Join with Three or More Tables Although each join specification joins only two tables, FROM clauses can contain multiple join specifications. This allows many tables to be joined for a single query. The ProductVendor table of the AdventureWorks2012 database offers a good example of a situation in which joining more than two tables is helpful. The following Transact-SQL query finds the names of all products of a particular subcategory and the names of their vendors: SELECT p.Name, v.Name FROM Production.Product p JOIN Purchasing.ProductVendor pv ON p.ProductID = pv.ProductID JOIN Purchasing.Vendor v ON pv.BusinessEntityID = v.BusinessEntityID WHERE ProductSubcategoryID = 15 ORDER BY v.Name; http://technet.microsoft.com/en-us/library/ms191430(v=sql.105).aspx, 05/26/2015 9/15/2024 Pierce College CIS261 SQL 11

12. INNER JOIN WITH THREE OR MORE TABLES 9/15/2024 Pierce College CIS261 SQL 12

13. Outer Join Left outer Join Returns all the records in the first table with null values for missing records in the second table Right outer Join Returns all the records in the second table with null values for missing records in the first table Full outer Join Returns all records in both the table with null values for missing records in both tables https://technet.microsoft.com/en-us/library/ms187518(v=sql.105).aspx, 05/26/2015 9/15/2024 Pierce College CIS261 SQL 13

14. LEFT OUTER JOIN Consider a join of the Product table and the ProductReview table on their ProductID columns. The results show only the products for which reviews have been written. To include all products, regardless of whether a review has been written for one, use an ISO left outer join. The following is the query: USE AdventureWorks2012; GO SELECT p.Name, pr.ProductReviewID FROM Production.Product p LEFT OUTER JOIN Production.ProductReview pr ON p.ProductID = pr.ProductID The LEFT OUTER JOIN includes all rows in the Product table in the results, whether or not there is a match on the ProductID column in the ProductReview table. Notice that in the results where there is no matching product review ID for a product, the row contains a null value in the ProductReviewID column. https://technet.microsoft.com/en-us/library/ms187518(v=sql.105).aspx, 05/26/2015 9/15/2024 Pierce College CIS261 SQL 14

15. LEFT OUTER JOIN 9/15/2024 Pierce College CIS261 SQL 15

16. Right Outer Join Consider a join SalesTerritory table and the SalesPerson table on their TerritoryID columns. The results show any territory that has been assigned to a sales person. The ISO right outer join operator, RIGHT OUTER JOIN, indicates all rows in the second table are to be included in the results, regardless of whether there is matching data in the first table. To include all sales persons in the results, regardless of whether they are assigned a territory, use an ISO right outer join. Here is the Transact-SQL query and results of the right outer join: USE AdventureWorks2012; GO SELECT st.Name AS Territory, sp.BusinessEntityID FROM Sales.SalesTerritory st RIGHT OUTER JOIN Sales.SalesPerson sp ON st.TerritoryID = sp.TerritoryID ; https://technet.microsoft.com/en-us/library/ms187518(v=sql.105).aspx, 05/26/2015 9/15/2024 Pierce College CIS261 SQL 16

17. RIGHT OUTER JOIN 9/15/2024 Pierce College CIS261 SQL 17

18. Full Outer Join To retain the nonmatching information by including nonmatching rows in the results of a join, use a full outer join. SQL Server provides the full outer join operator, FULL OUTER JOIN, which includes all rows from both tables, regardless of whether or not the other table has a matching value. You can include a WHERE clause with a full outer join to return only the rows where there is no matching data between the tables. The following query returns only those products that have no matching sales orders, as well as those sales orders that are not matched to a product (although all sales orders, in this case, are matched to a product). https://technet.microsoft.com/en-us/library/ms187518(v=sql.105).aspx --The OUTER keyword following the FULL keyword is optional. SELECT p.Name, sod.SalesOrderID FROM Production.Product p FULL OUTER JOIN Sales.SalesOrderDetail sod ON p.ProductID = sod.ProductID WHERE p.ProductID IS NULL OR sod.ProductID IS NULL ORDER BY p.Name ; 9/15/2024 Pierce College CIS261 SQL 18

19. FULL OUTER JOIN 9/15/2024 Pierce College CIS261 SQL 19

20. Table Joins 9/15/2024 Pierce College CIS261 SQL 20

21. Join with Where Clause (OBSOLETE) 9/15/2024 Pierce College CIS261 SQL 21

22. This work is licensed under a Creative Commons Attribution 4.0 International License Led by Bellevue College, the Health eWorkforce Consortium was formed to elevate Health Information Technology workforce development locally and nationally and provide career paths into this promising field for veterans and others. The nine-college consortium includes Bellevue College, Bellingham Technical College, Clark College, Clover Park Technical College, Northern Virginia Community College, Pierce College, Renton Technical College, Spokane Community College, and Whatcom Community College. The Health Information and Management Systems Society (HIMSS) is also a primary partner. This workforce solution was 100% funded by an $11.7m grant awarded by the U.S. Department of Labor's Employment and Training Administration, Grant #TC-23745-12-60-A-53. The solution was created by the grantee and does not necessarily reflect the official position of the U.S. Department of Labor. The Department of Labor makes no guarantees, warranties, or assurances of any kind, express or implied, with respect to such information, including any information on linked sites and including, but not limited to, accuracy of the information or its completeness, timeliness, usefulness, adequacy, continued availability or ownership. 9/15/2024 Pierce College CIS261 SQL 22