1 / 16

Join Queries

Join Queries. CS 146. Introduction: Join Queries. So far, our SELECT queries have retrieved data from a single table Usually queries combine data from multiple tables: List how much (pounds) of each product that was purchased today

maitland
Download Presentation

Join Queries

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. Join Queries CS 146

  2. Introduction: Join Queries • So far, our SELECT queries have retrieved data from a single table • Usually queries combine data from multiple tables: • List how much (pounds) of each product that was purchased today • List the customer name and product name for a specific purchase • Queries that retrieve data from multiple tables require joining the tables through primary key/foreign key relationships

  3. Main Types of Join Queries • Inner Join • Retrieves all matching fields in joined tables • Also called equijoin or natural join • Outer Join • Retrieves all fields in one table, and matching fields in second table if they exist

  4. Example Inner Join CANDY_CUSTOMER CANDY_PURCHASE CANDY_PRODUCT

  5. Join Query Syntax (ANSI 1992) • The word "INNER" is optional SELECT Column1, Column2, … FROM Table1 INNER JOIN Table2 ON Table1.JoinColumn = Table2.JoinColumn WHERE SearchCondition(s) Join condition

  6. Join Query Example (ANSI 1992) • Note: • Order of tables in FROM clause doesn’t matter • Order of tables in ON condition doesn’t matter

  7. Qualifying Field Names • What if a join query retrieves a field that exists in both tables?

  8. Qualifying Field Names • You qualify the field name in the SELECT clause • Preface the field name with the name of either table

  9. Table Aliases • Shorthand way to write queries by abbreviating table names • Pros & cons? NOTE: Once you create a table alias, you have to use it everywhere…

  10. Inner Join of 3 Tables • General syntax: • Note: • Placing each INNER JOIN and ON clause on a separate line makes the query easier to read and understand SELECT Column1, Column2, … FROM Table1 INNER JOIN Table2 ON Table1.JoinColumn = Table2.JoinColumn INNER JOIN Table3 ON Table2.JoinColumn = Table3.JoinColumn WHERE SearchCondition(s)

  11. 3 Table Inner Join Example

  12. Joining N Tables • You can join any number of tables, provided primary key/foreign key relationships exist • Challenge: • Including all necessary tables in the query

  13. Joining N Tables • You can join any number of tables, provided primary key/foreign key relationships exist • Challenge: you need to include table in join queries to provide needed links even if you don't include fields in the SELECT clause…

  14. Example CANDY_PURCHASE prod_id (J) cust_id (J) CANDY_CUSTOMER cust_name (S) cust_id (J) CANDY_PRODUCT prod_desc (D) prod_id (J) SELECT prod_desc FROM candy_product INNER JOIN candy_purchase ON candy_product.prod_id = candy_purchase.prod_id INNER JOIN candy_customer ON candy_purchase.cust_id = candy_customer.cust_id WHERE cust_name = 'Bobby Bon Bons'

  15. Designing Complex Join Queries • Terminology: • Display field: • Retrieved data field • Appears in the SELECT clause • Join field • Primary or foreign key used to join tables • Appears in a join condition • Search field • Used in a search condition • Appears in the WHERE clause • Join queries must include all tables that contain display, join, or search fields

  16. Query Design Diagrams • Visual way to identify display, join, and search fields • Process: • Identify every table in the query • Identify every involved field in each table • Label whether it is a display, search, or join field • Create join condition links

More Related