1 / 20

SQL Training Join Statements

SQL Training Join Statements. Joining Tables. 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.

millerrobert
Télécharger la présentation

SQL Training Join Statements

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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.

More Related