Understanding JOIN in SQL Query for Multiple Table Relationships

JOIN in SQL is a crucial concept that helps combine data from multiple tables based on specified conditions. This process allows for retrieving relevant information by linking tables together. In the provided scenario, we explore how to retrieve specific data by joining the Product and Company tables and executing queries to fetch Japanese products under $150 and USA companies manufacturing gadget products.

• SQL Query
• JOIN
• Table Relationships
• Database Management
• Data Retrieval

nmcla Follow

Uploaded on Oct 07, 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. CSE 344 JANUARY 12TH JOINS

2. JOIN: INTRO The JOIN is the way we indicate in a query how multiple tables are related. Example, if we want all of the products and their relevant company information, we need to join those two tables. The result of the join is all of the relevant information from both tables Join occurs based on the join condition. This allows us to access information that comes from multiple tables

3. Product(pname, price, category, manufacturer) Company(cname, country) JOINS IN SQL pname MultiTouch SingleTouch Gizom SuperGizmo price 199.99 49.99 50 250.00 category gadget photography gadget gadget manufacturer Canon Canon GizmoWorks GizmoWorks cname GizmoWorks Canon Hitachi country USA Japan Japan Retrieve all Japanese products that cost < $150

4. Product(pname, price, category, manufacturer) Company(cname, country) JOINS IN SQL pname MultiTouch SingleTouch Gizom SuperGizmo price 199.99 49.99 50 250.00 category gadget photography gadget gadget manufacturer Canon Canon GizmoWorks GizmoWorks cname GizmoWorks Canon Hitachi country USA Japan Japan Retrieve all Japanese products that cost < $150 SELECT pname, price FROM Product, Company WHERE ...

5. Product(pname, price, category, manufacturer) Company(cname, country) JOINS IN SQL pname MultiTouch SingleTouch Gizom SuperGizmo price 199.99 49.99 50 250.00 category gadget photography gadget gadget manufacturer Canon Canon GizmoWorks GizmoWorks cname GizmoWorks Canon Hitachi country USA Japan Japan Retrieve all Japanese products that cost < $150 SELECT pname, price FROM Product, Company WHERE manufacturer=cname AND country='Japan' AND price < 150

6. Product(pname, price, category, manufacturer) Company(cname, country) JOINS IN SQL pname MultiTouch SingleTouch Gizom SuperGizmo price 199.99 49.99 50 250.00 category gadget photography gadget gadget manufacturer Canon Canon GizmoWorks GizmoWorks cname GizmoWorks Canon Hitachi country USA Japan Japan Retrieve all USA companies that manufacture gadget products

7. Product(pname, price, category, manufacturer) Company(cname, country) JOINS IN SQL pname MultiTouch SingleTouch Gizom SuperGizmo price 199.99 49.99 50 250.00 category gadget photography gadget gadget manufacturer Canon Canon GizmoWorks GizmoWorks cname GizmoWorks Canon Hitachi country USA Japan Japan Retrieve all USA companies that manufacture gadget products Why DISTINCT? SELECT DISTINCT cname FROM Product, Company WHERE country='USA' AND category = 'gadget' AND manufacturer = cname

8. JOINS IN SQL The standard join in SQL is sometimes called an inner join Each row in the result must come from both tables in the join Sometimes we want to include rows from only one of the two table: outer join

9. Employee(id, name) Sales(employeeID, productID) INNER JOIN Employee id 1 2 3 Sales employeeID 1 1 2 name Joe Jack Jill productID 344 355 544 Retrieve employees and their sales

10. Employee(id, name) Sales(employeeID, productID) INNER JOIN Employee id 1 2 3 Sales employeeID 1 1 2 name Joe Jack Jill productID 344 355 544 Retrieve employees and their sales SELECT * FROM Employee E, Sales S WHERE E.id = S.employeeID

11. Employee(id, name) Sales(employeeID, productID) INNER JOIN Employee id 1 2 3 Sales employeeID 1 1 2 name Joe Jack Jill productID 344 355 544 Retrieve employees and their sales id 1 1 2 name Joe Joe Jack empolyeeID 1 1 2 productID 344 355 544 SELECT * FROM Employee E, Sales S WHERE E.id = S.employeeID

12. Employee(id, name) Sales(employeeID, productID) INNER JOIN Employee id 1 2 3 Sales employeeID 1 1 2 name Joe Jack Jill productID 344 355 544 Jill is missing Retrieve employees and their sales id 1 1 2 name Joe Joe Jack empolyeeID 1 1 2 productID 344 355 544 SELECT * FROM Employee E, Sales S WHERE E.id = S.employeeID

13. Employee(id, name) Sales(employeeID, productID) INNER JOIN Employee id 1 2 3 Sales employeeID 1 1 2 name Joe Jack Jill productID 344 355 544 Jill is missing Retrieve employees and their sales Alternative syntax id 1 1 2 name Joe Joe Jack empolyeeID 1 1 2 productID 344 355 544 SELECT * FROM Employee E INNER JOIN Sales S ON E.id = S.employeeID

14. Employee(id, name) Sales(employeeID, productID) OUTER JOIN Employee id 1 2 3 Sales employeeID 1 1 2 name Joe Jack Jill productID 344 355 544 Jill is present Retrieve employees and their sales id 1 1 2 3 name Joe Joe Jack Jill empolyeeID 1 1 2 NULL productID 344 355 544 NULL SELECT * FROM Employee E LEFT OUTER JOIN Sales S ON E.id = S.employeeID

15. (INNER) JOINS Product(pname, price, category, manufacturer) Company(cname, country) -- manufacturer is foreign key to Company SELECT DISTINCT cname FROM Product, Company WHERE country='USA' AND category = 'gadget' AND manufacturer = cname

16. (INNER) JOINS SELECT DISTINCT cname FROM Product, Company WHERE country='USA' AND category = 'gadget' AND manufacturer = cname Product Company cname country pname category manufacturer GizmoWorks USA Gizmo gadget GizmoWorks Canon Japan Camera Photo Hitachi Hitachi Japan OneClick Photo Hitachi

17. (INNER) JOINS SELECT DISTINCT cname FROM Product, Company WHERE country='USA' AND category = 'gadget' AND manufacturer = cname Product Company cname country pname category manufacturer GizmoWorks USA Gizmo gadget GizmoWorks Canon Japan Camera Photo Hitachi Hitachi Japan OneClick Photo Hitachi

18. (INNER) JOINS SELECT DISTINCT cname FROM Product, Company WHERE country='USA' AND category = 'gadget' AND manufacturer = cname Product Company cname country pname category manufacturer GizmoWorks USA Gizmo gadget GizmoWorks Canon Japan Camera Photo Hitachi Hitachi Japan OneClick Photo Hitachi

19. (INNER) JOINS SELECT DISTINCT cname FROM Product, Company WHERE country='USA' AND category = 'gadget' AND manufacturer = cname Product Company cname country pname category manufacturer GizmoWorks USA Gizmo gadget GizmoWorks Canon Japan Camera Photo Hitachi Hitachi Japan OneClick Photo Hitachi pname category manufacturer cname country Gizmo gadget GizmoWorks GizmoWorks USA

20. (INNER) JOINS SELECT DISTINCT cname FROM Product, Company WHERE country='USA' AND category = 'gadget' AND manufacturer = cname Product Company cname country pname category manufacturer GizmoWorks USA Gizmo gadget GizmoWorks Canon Japan Camera Photo Hitachi Hitachi Japan OneClick Photo Hitachi

21. (INNER) JOINS SELECT DISTINCT cname FROM Product, Company WHERE country='USA' AND category = 'gadget' AND manufacturer = cname Product Company cname country pname category manufacturer GizmoWorks USA Gizmo gadget GizmoWorks Canon Japan Camera Photo Hitachi Hitachi Japan OneClick Photo Hitachi

22. (INNER) JOINS SELECT DISTINCT cname FROM Product, Company WHERE country='USA' AND category = 'gadget' AND manufacturer = cname AND manufacturer = cname SELECT DISTINCT cname FROM Product, Company WHERE country='USA' AND category = 'gadget' SELECT DISTINCT cname FROM Product JOIN Company ON country = 'USA' AND category = 'gadget' AND manufacturer = cname

23. (INNER) JOINS SELECT x1.a1, x2.a2, xm.am FROM R1 as x1, R2 as x2, Rm as xm WHERE Cond for x1 in R1: for x2 in R2: ... for xm in Rm: if Cond(x1, x2 ): output(x1.a1, x2.a2, xm.am) This is called nested loop semantics since we are interpreting what a join means using a nested loop

24. ANOTHER EXAMPLE Product(pname, price, category, manufacturer) Company(cname, country) -- manufacturer is foreign key to Company Retrieve all USA companies that manufacture products in both gadget and photography categories

25. ANOTHER EXAMPLE Product(pname, price, category, manufacturer) Company(cname, country) -- manufacturer is foreign key to Company Retrieve all USA companies that manufacture products in both gadget and photography categories SELECT DISTINCT z.cname FROM Product x, Company z WHERE z.country = USA AND x.manufacturer = z.cname AND x.category = 'gadget AND x.category = 'photography; Does this work?

26. ANOTHER EXAMPLE Product(pname, price, category, manufacturer) Company(cname, country) -- manufacturer is foreign key to Company Retrieve all USA companies that manufacture products in both gadget and photography categories SELECT DISTINCT z.cname FROM Product x, Company z WHERE z.country = USA AND x.manufacturer = z.cname AND (x.category = 'gadget OR x.category = 'photography); What about this?

27. ANOTHER EXAMPLE Product(pname, price, category, manufacturer) Company(cname, country) -- manufacturer is foreign key to Company Retrieve all USA companies that manufacture products in both gadget and photography categories SELECT DISTINCT z.cname FROM Product x, Product y, Company z WHERE z.country = USA AND x.manufacturer = z.cname AND y.manufacturer = z.cname AND x.category = 'gadget AND y.category = 'photography; Need to include Product twice!

28. SELF-JOINS AND TUPLE VARIABLES Find USA companies that manufacture both products in the gadgets and photo category Joining Product with Company is insufficient: need to join Product, with Product, and with Company When a relation occurs twice in the FROM clause we call it a self-join; in that case we must use tuple variables (why?)

29. SELF-JOINS SELECT DISTINCT z.cname FROM Product x, Product y, Company z WHERE z.country = USA AND x.category = gadget AND y.category = photo AND x.manufacturer = z.cname AND y.manufacturer = z.cname; Product Company pname category manufacturer cname country Gizmo gadget GizmoWorks GizmoWorks USA SingleTouch photo Hitachi Hitachi Japan MultiTouch Photo GizmoWorks

30. SELF-JOINS SELECT DISTINCT z.cname FROM Product x, Product y, Company z WHERE z.country = USA AND x.category = gadget AND y.category = photo AND x.manufacturer = z.cname AND y.manufacturer = z.cname; Product Company x pname category manufacturer cname country Gizmo gadget GizmoWorks GizmoWorks USA SingleTouch photo Hitachi Hitachi Japan MultiTouch Photo GizmoWorks

31. SELF-JOINS SELECT DISTINCT z.cname FROM Product x, Product y, Company z WHERE z.country = USA AND x.category = gadget AND y.category = photo AND x.manufacturer = z.cname AND y.manufacturer = z.cname; Product Company x pname category manufacturer cname country y Gizmo gadget GizmoWorks GizmoWorks USA SingleTouch photo Hitachi Hitachi Japan MultiTouch Photo GizmoWorks

32. SELF-JOINS SELECT DISTINCT z.cname FROM Product x, Product y, Company z WHERE z.country = USA AND x.category = gadget AND y.category = photo AND x.manufacturer = z.cname AND y.manufacturer = z.cname; z Product Company x pname category manufacturer cname country y Gizmo gadget GizmoWorks GizmoWorks USA SingleTouch photo Hitachi Hitachi Japan MultiTouch Photo GizmoWorks

33. SELF-JOINS SELECT DISTINCT z.cname FROM Product x, Product y, Company z WHERE z.country = USA AND x.category = gadget AND y.category = photo AND x.manufacturer = z.cname AND y.manufacturer = z.cname; z Product Company x pname category manufacturer cname country y Gizmo gadget GizmoWorks GizmoWorks USA SingleTouch photo Hitachi Hitachi Japan MultiTouch Photo GizmoWorks

34. SELF-JOINS SELECT DISTINCT z.cname FROM Product x, Product y, Company z WHERE z.country = USA AND x.category = gadget AND y.category = photo AND x.manufacturer = z.cname AND y.manufacturer = z.cname; z Product Company x pname category manufacturer cname country Gizmo gadget GizmoWorks GizmoWorks USA y SingleTouch photo Hitachi Hitachi Japan MultiTouch Photo GizmoWorks

35. SELF-JOINS SELECT DISTINCT z.cname FROM Product x, Product y, Company z WHERE z.country = USA AND x.category = gadget AND y.category = photo AND x.manufacturer = z.cname AND y.manufacturer = z.cname; z Product Company x pname category manufacturer cname country Gizmo gadget GizmoWorks GizmoWorks USA y SingleTouch photo Hitachi Hitachi Japan MultiTouch Photo GizmoWorks

36. SELF-JOINS SELECT DISTINCT z.cname FROM Product x, Product y, Company z WHERE z.country = USA AND x.category = gadget AND y.category = photo AND x.manufacturer = z.cname AND y.manufacturer = z.cname; z Product Company x pname category manufacturer cname country Gizmo gadget GizmoWorks GizmoWorks USA SingleTouch photo Hitachi Hitachi Japan y MultiTouch Photo GizmoWorks

37. SELF-JOINS SELECT DISTINCT z.cname FROM Product x, Product y, Company z WHERE z.country = USA AND x.category = gadget AND y.category = photo AND x.manufacturer = z.cname AND y.manufacturer = z.cname; z Product Company x pname category manufacturer cname country Gizmo gadget GizmoWorks GizmoWorks USA SingleTouch photo Hitachi Hitachi Japan y MultiTouch Photo GizmoWorks x.pname x.category x.manufacturer y.pname y.category y.manufacturer z.cname z.country Gizmo gadget GizmoWorks MultiTouch Photo GizmoWorks GizmoWorks USA

38. SELF-JOINS SELECT DISTINCT z.cname FROM Product x, Product y, Company z WHERE z.country = USA AND x.category = gadget AND y.category = photo AND x.manufacturer = z.cname AND y.manufacturer = z.cname; z Product Company x pname category manufacturer cname country Gizmo gadget GizmoWorks GizmoWorks USA SingleTouch photo Hitachi Hitachi Japan y MultiTouch Photo GizmoWorks x.pname x.category x.manufacturer y.pname y.category y.manufacturer z.cname z.country Gizmo gadget GizmoWorks MultiTouch Photo GizmoWorks GizmoWorks USA

39. OUTER JOINS Product(name, category) Purchase(prodName, store) -- prodName is foreign key SELECT Product.name, Purchase.store FROM Product, Purchase WHERE Product.name = Purchase.prodName We want to include products that are never sold, but some are not listed! Why?

40. OUTER JOINS Product(name, category) Purchase(prodName, store) -- prodName is foreign key SELECT Product.name, Purchase.store FROM Product LEFT OUTER JOIN Purchase ON Product.name = Purchase.prodName

41. SELECT Product.name, Purchase.store FROM Product JOIN Purchase ON Product.name = Purchase.prodName Product Purchase Name Category ProdName Store Gizmo gadget Gizmo Wiz Camera Photo Camera Ritz OneClick Photo Camera Wiz

42. SELECT Product.name, Purchase.store FROM Product JOIN Purchase ON Product.name = Purchase.prodName Product Purchase Name Category ProdName Store Gizmo gadget Gizmo Wiz Camera Photo Camera Ritz OneClick Photo Camera Wiz

43. SELECT Product.name, Purchase.store FROM Product JOIN Purchase ON Product.name = Purchase.prodName Product Purchase Name Category ProdName Store Gizmo gadget Gizmo Wiz Camera Photo Camera Ritz OneClick Photo Camera Wiz Name Store Gizmo Wiz Output

44. SELECT Product.name, Purchase.store FROM Product JOIN Purchase ON Product.name = Purchase.prodName Product Purchase Name Category ProdName Store Gizmo gadget Gizmo Wiz Camera Photo Camera Ritz OneClick Photo Camera Wiz Name Store Gizmo Wiz Output

45. SELECT Product.name, Purchase.store FROM Product JOIN Purchase ON Product.name = Purchase.prodName Product Purchase Name Category ProdName Store Gizmo gadget Gizmo Wiz Camera Photo Camera Ritz OneClick Photo Camera Wiz Name Store Gizmo Wiz Output

46. SELECT Product.name, Purchase.store FROM Product JOIN Purchase ON Product.name = Purchase.prodName Product Purchase Name Category ProdName Store Gizmo gadget Gizmo Wiz Camera Photo Camera Ritz OneClick Photo Camera Wiz Name Store Gizmo Wiz Output

47. SELECT Product.name, Purchase.store FROM Product JOIN Purchase ON Product.name = Purchase.prodName Product Purchase Name Category ProdName Store Gizmo gadget Gizmo Wiz Camera Photo Camera Ritz OneClick Photo Camera Wiz Name Store Gizmo Wiz Output Camera Ritz

48. SELECT Product.name, Purchase.store FROM Product JOIN Purchase ON Product.name = Purchase.prodName Product Purchase Name Category ProdName Store Gizmo gadget Gizmo Wiz Camera Photo Camera Ritz OneClick Photo Camera Wiz Name Store Gizmo Wiz Output Camera Ritz Camera Wiz

49. SELECT Product.name, Purchase.store FROM Product JOIN Purchase ON Product.name = Purchase.prodName Product Purchase Name Category ProdName Store Gizmo gadget Gizmo Wiz Camera Photo Camera Ritz OneClick Photo Camera Wiz Name Store Gizmo Wiz Output Camera Ritz Camera Wiz

50. SELECT Product.name, Purchase.store FROM Product LEFT OUTER JOIN Purchase ON Product.name = Purchase.prodName Product Purchase Name Category ProdName Store Gizmo gadget Gizmo Wiz Camera Photo Camera Ritz OneClick Photo Camera Wiz Name Store Gizmo Wiz Output Camera Ritz Camera Wiz

Load More ...