1 / 21

Multi-table queries (JOIN) 2 tables

Multi-table queries (JOIN) 2 tables. Retrieve the part name for all parts shipped in quantities equal to 200. SELECT DISTINCT PART.PartName FROM PART, SHIPMENT WHERE PART.PartNumber = SHIPMENT.PartNumber AND SHIPMENT.quantity = 200;. Conjunct #1 is a PK FK comparison.

delano
Download Presentation

Multi-table queries (JOIN) 2 tables

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. Multi-table queries(JOIN) 2 tables Retrieve the part name for all parts shipped in quantities equal to 200 SELECT DISTINCT PART.PartName FROM PART, SHIPMENT WHERE PART.PartNumber = SHIPMENT.PartNumber AND SHIPMENT.quantity = 200;

  2. Conjunct #1 is a PK FK comparison Conjunct #2 qualifies retrieval Multi-table queries(JOIN) 2 tables So how does this work? SELECT DISTINCT PART.PartName FROM PART, SHIPMENT WHERE PART.PartNumber = SHIPMENT.PartNumber AND SHIPMENT.quantity = 200;

  3. Multi-table queries(JOIN) 2 tables SELECT DISTINCT PART.PartName FROM PART, SHIPMENT WHERE PART.PartNumber = SHIPMENT.PartNumber AND SHIPMENT.quantity = 200; Conceptually, we can understand the execution of the query in terms of row scans. A Scan is a sequential inspection of many rows for the purpose of returning rows that meet a criteria. The following demonstrations are a simplified version of the Nested Loop technique (as opposed to “merge sort” and “hashed join”)

  4. PK/FK comparison requires one scan of the FK set for each PK in the parent table. Row is included in the view where PK=FK and Qty = 200 Multi-table queries(JOIN) 2 tables SELECT DISTINCT PART.PartName FROM PART, SHIPMENT WHERE PART.PartNumber = SHIPMENT.PartNumber AND SHIPMENT.quantity = 200;

  5. Multi-table queries(JOIN) 2 tables SELECT DISTINCT PART.PartName FROM PART, SHIPMENT WHERE PART.PartNumber = SHIPMENT.PartNumber AND SHIPMENT.quantity = 200; (Scan 1) (No Matches: TT) (P1=P1  300=200) (P1=P2  200=200) . . . (FK Scan) (P1=P5  100=200)

  6. This removes duplicates in results Multi-table queries(JOIN) 2 tables SELECT DISTINCT PART.PartName FROM PART, SHIPMENT WHERE PART.PartNumber = SHIPMENT.PartNumber AND SHIPMENT.quantity = 200; (Scan 2) (Two Matches: TT) (P2=P1  300=200) (P2=P2  200=200) . . . (FK Scan) (P2=P5  100=200)

  7. Multi-table queries(JOIN) 2 tables SELECT DISTINCT PART.PartName FROM PART, SHIPMENT WHERE PART.PartNumber = SHIPMENT.PartNumber AND SHIPMENT.quantity = 200; (Scan 3) ( No Matches: TT) (P3=P1  300=200) (P3=P2  200=200) . . . (FK Scan) (P3=P5  100=200)

  8. Multi-table queries(JOIN) 2 tables SELECT DISTINCT PART.PartName FROM PART, SHIPMENT WHERE PART.PartNumber = SHIPMENT.PartNumber AND SHIPMENT.quantity = 200; (Scan 4) (And So On...) ( Two Matches: TT) (P4=P1  300=200) (P4=P2  200=200) . . . (FK Scan) (P4=P5  100=200)

  9. Multi-table queries(JOIN) 2 tables SELECT DISTINCT PART.PartName FROM PART, SHIPMENT WHERE PART.PartNumber = SHIPMENT.PartNumber AND SHIPMENT.quantity = 200; (Scan 6) How many row comparisons? rows(PK-table) X rows(FK-table) 6 X 12 = 72

  10. Multi-table queriesJOIN Evaluation Techniques • Three common methods used by DBMS optimizers to evaluate Joins • Nested Loop • Merge Scan • Hash Join

  11. Multi-table queriesJOIN Evaluation Techniques • Nested Loop • Essentially the preceding demo • One table defined as external and one table defined as internal • (External:Internal) • (1:M) • (Parent:Child) • If there isn’t an index on the FK, the internal table has to be opened for a scan for every row of the external table

  12. Multi-table queries(JOIN) 3 tables Retrieve supplier name and part name for parts shipped in quantities less than 400. SELECT DISTINCT SUPPLIER.SupplierName, PART.PartName FROM SUPPLIER, PART, SHIPMENT WHERE PART.PartNumber = SHIPMENT.PartNumber AND SUPPLIER.SupplierNumber = SHIPMENT.SupplierNumber AND SHIPMENT.Quantity < 400;

  13. Multi-table queries(JOIN) 3 tables SELECT DISTINCT SUPPLIER.SupplierName, PART.PartName FROM SUPPLIER, PART, SHIPMENT WHERE PART.PartNumber = SHIPMENT.PartNumber AND SUPPLIER.SupplierNumber = SHIPMENT.SupplierNumber AND SHIPMENT.Quantity < 400; (Scan 1) (One Match: TT T) (P1=P1  S1=S1  400>300) (P1=P2  S1=S1  400>200) . . . (FK Scan) (P1=P5  S1=S4  400>100)

  14. Multi-table queries(JOIN) 3 tables SELECT DISTINCT SUPPLIER.SupplierName, PART.PartName FROM SUPPLIER, PART, SHIPMENT WHERE PART.PartNumber = SHIPMENT.PartNumber AND SUPPLIER.SupplierNumber = SHIPMENT.SupplierNumber AND SHIPMENT.Quantity < 400; (Scan 2) (No Matches: TT T) (P1=P1  S2=S1  400>300) (P1=P2  S2=S1  400>200) . . . (FK Scan) (P1=P5  S2=S4  400>100)

  15. So we finish out the “P1” Supplier scan with no matches Multi-table queries(JOIN) 3 tables SELECT DISTINCT SUPPLIER.SupplierName, PART.PartName FROM SUPPLIER, PART, SHIPMENT WHERE PART.PartNumber = SHIPMENT.PartNumber AND SUPPLIER.SupplierNumber = SHIPMENT.SupplierNumber AND SHIPMENT.Quantity < 400; (Scan 2-5) (No Matches: TT T) . . .

  16. Multi-table queries(JOIN) 3 tables SELECT DISTINCT SUPPLIER.SupplierName, PART.PartName FROM SUPPLIER, PART, SHIPMENT WHERE PART.PartNumber = SHIPMENT.PartNumber AND SUPPLIER.SupplierNumber = SHIPMENT.SupplierNumber AND SHIPMENT.Quantity < 400; (Scan 6) (One Match: TT T) (P2=P1  S1=S1  400>300) (P2=P2  S1=S1  400>200) . . . (FK Scan) (P2=P5  S1=S4  400>100)

  17. Multi-table queries(JOIN) 3 tables SELECT DISTINCT SUPPLIER.SupplierName, PART.PartName FROM SUPPLIER, PART, SHIPMENT WHERE PART.PartNumber = SHIPMENT.PartNumber AND SUPPLIER.SupplierNumber = SHIPMENT.SupplierNumber AND SHIPMENT.Quantity < 400; (Scan 7) (One Match: TT T) (P2=P1  S2=S1  400>300) (P2=P2  S2=S1  400>200) . . . (FK Scan) (P1=P5  S2=S4  400>100)

  18. Multi-table queries(JOIN) 3 tables SELECT DISTINCT SUPPLIER.SupplierName, PART.PartName FROM SUPPLIER, PART, SHIPMENT WHERE PART.PartNumber = SHIPMENT.PartNumber AND SUPPLIER.SupplierNumber = SHIPMENT.SupplierNumber AND SHIPMENT.Quantity < 400; (Scan 8) (And so on...) (One Match: TT T) (P2=P1  S3=S1  400>300) (P2=P2  S3=S1  400>200) . . . (FK Scan) (P1=P5  S3=S4  400>100)

  19. Multi-table queries(JOIN) 3 tables SELECT DISTINCT SUPPLIER.SupplierName, PART.PartName FROM SUPPLIER, PART, SHIPMENT WHERE PART.PartNumber = SHIPMENT.PartNumber AND SUPPLIER.SupplierNumber = SHIPMENT.SupplierNumber AND SHIPMENT.Quantity < 400; How many row comparisons? 6x5x12 = 360

  20. Multi-table queries(JOIN) 3 tables Retrieve the supplier name and part name for all shipments with quantity greater than or equal to 200 for which the warehouse is located in the same city as the supplier SELECT DISTINCT SUPPLIER.SupplierName, PART.PartName FROM SUPPLIER, PART, SHIPMENT WHERE (PART.PartNumber = SHIPMENT.PartNumber AND SUPPLIER.SupplierNumber = SHIPMENT.SupplierNumber) AND (SHIPMENT.Quantity >= 200 AND SUPPLIER.SupplierCity = PART.PartCity);

  21. (Inner Table) (Outer Table) Data FK Value PK Value Data Data FK Value PK Value Data (PK=FK Scan) Data FK Value PK Value Data Data FK Value Data FK Value Outer Table Inner Table Multi-table queries(JOIN) Let’s recap... For each row in the outer table (Parent), every row in the inner table (Child) is scanned for a PK/FK match and any qualifying predicate. In your WHERE clause, you must have a PK=FK statement for every Parent/Child relationship involved in the query.

More Related