SQL Training Join Statements

1. SQL Training Join Statements

2. Joining Tables

3. Relationships (Cardinalities) Most of the time the data we want comes from several different tables. Tables are joined using common fields - usually the primary and foreign keys. A fuelsource has multiple products and each products can be associated with several manifests.

4. Simple Join SELECT Country.CountryID, Country.CountryName, Country.CurrencyName, Region.RegionName FROM Country, Region WHERE Country.CountryID = Region.CountryID; tablename.fieldname

5. Inner Join (SQL ANSI Standard) • Another way to solve the same problem is using the Inner Join syntax. SELECT Country.CountryID, Country.CountryName, Country.CurrencyName, Region.RegionName FROM Country INNER JOIN Region ON Country.CountryID = Region.CountryID;

6. Multi Table Join Problem: Retrieve a list of components – and their descriptions - used to make HomeGen 3000 - Natural Gas, 110v 50Hz (ProductID = 10). We need to go through Manifest but will not display anything from Manifest.

7. Multi Table Join Problem: Retrieve a list of components – and their descriptions - used to make HomeGen 3000 - Natural Gas, 110v 50Hz (ProductID = 10). SELECT Product.ProductID, ProductCode, ProductDescription, Component.ComponentID, ComponentDescription, RequiredQTY FROM Product, Manifest, Component WHERE Product.ProductID = Manifest.ProductID and Manifest.ComponentID = Component.ComponentIDand Product.ProductID = 10 Order By ComponentDescription; } Table Joins 29 Rows

8. Join - Using Correlation Names • In an SQL statement, Tables can be given nicknames (correlation names). • You can then use the nickname instead of fully qualifying the Column’s name. SELECT p.productID, p.productCode, p.ProductDescription, c.componentID, c.ComponentDescription, m.RequiredQTY FROM Product p, Manifest m, Component c WHERE p.ProductID = m.ProductID and m.ComponentID = c.ComponentID and p.ProductID = 10 Order By c.ComponentDescription;

9. Bad Select Statement 84 rows 898 rows 2436 rows SELECT Product.ProductID, Component.ComponentID FROM Product, Manifest, Component What is wrong with this query?

10. Bad Select Statement – Cartesian Product 84 rows 898 rows 2436 rows SELECT Product.ProductID, Component.ComponentID FROM Product, Manifest, Component This query will produce 183, 752, 352 rows!

11. Bad Select Statement - Corrected 84 rows 898 rows 2436 rows SELECT Product.ProductID, Component.ComponentID FROM Product, Manifest, Component WHERE Product.ProductID = Manifest.ProductID and Manifest.ComponentID = Component.ComponentID

12. Outer (Left) Joins

13. Inner and Outer Joins Problem: How many users are there in each role? Outer (Left) Join Normal (Inner) Join SELECT Roles.roleName, count(Users.userID) as userCount From Roles LEFT JOINUsersONRoles.roleID = Users.roleID GROUP BY Roles.roleName; SELECT r.roleName, count(u.userID) as userCount FROM Roles r, Users u WHERE r.roleID = u.roleID GROUP BY r.roleName; There are 5 roles, why does the inner join only return data for 4 roles?

14. Inner and Outer Joins StudentID StudentName 1 Michaela Chu 2 Katharine Jones 3 Kerry Anderson StudentID Grade 1 A+ 2 A Inner Join:Returns only records where there is a match in both tables StudentID StudentName Grade 1 Michaela Chu A+ 2 Danielle Farinella A Outer Join:Returns ALL records even if there is not a match in both tables StudentID StudentName Grade 1 Michaela Chu A+ 2 Danielle Farinella A 3 Kerry Anderson ___

15. Outer (Left) Join SELECTroleName, count(distinct subscribedproductid) as nbrProducts FROMRoles LEFT JOINUsersONRoles.roleID = Users.roleID LEFT JOINSubscribedProductUsersONUsers.userID = SubscribedProductUsers.userID GROUP BYroleName;

16. Outer Join (or Left Join) Create a report showing: Country, Region, Province, VendorName. Show all the provinces even if there isn’t a vendor in the province. SELECT COUNTRY.COUNTRYNAME, REGION.REGIONNAME, PROVINCE.PROVINCENAME, VENDOR.VENDORNAME FROM COUNTRY INNER JOIN REGION ON COUNTRY.COUNTRYID = REGION.COUNTRYID INNER JOIN PROVINCE ON REGION.REGIONID = PROVINCE.REGIONID LEFT JOIN VENDOR ON PROVINCE.PROVINCEID = VENDOR.PROVINCEID ORDER BY PROVINCENAME; • The Left Join option causes all Rows in the Province table to be selected even if matching rows cannot be located In the Vendor table.

17. An ORACLE Outer Join Create a report showing: Country, Region, Province, VendorName. Show all the provinces even if there isn’t a vendor in the province. SELECT COUNTRY.COUNTRYNAME, REGION.REGIONNAME, PROVINCE.PROVINCENAME, VENDOR.VENDORNAME FROM COUNTRY, REGION, PROVINCE, VENDOR WHERE COUNTRY.COUNTRYID = REGION.COUNTRYID and REGION.REGIONID = PROVINCE.REGIONID and PROVINCE.PROVINCEID = VENDOR.PROVINCEID (+) ORDER BY PROVINCENAME; • The (+) option causes all Rows in the Province table to be selected even if matching rows cannot be located In the Vendor table.

18. Workshop

19. Select using a multi-table join Problem: Produce a report that displays Vendor Name, Province Name, Vendor Part Number and VendorPrice. Only include rows who have a VendorPrice >= 1000. Do not worry about formatting the Price column. 12 rows selected.

20. Select using a multi-table outer join Problem: List all countries and their regions. Order by Country name. Which country does not have any regions in the database 34 rows will be returned.