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. Join Query Syntax (ANSI 1986) SELECT Column1, Column2, … FROM Table1, Table2 WHERE Table1.JoinColumn = Table2.JoinColumn AND SearchCondition(s) Join condition

8. Join Query Example (ANSI 1986) Notice the join is specified in the WHERE clause, not in the FROM clause

9. Advantages of 1992 Syntax • Separates the join conditions and search conditions • Makes it impossible to omit a join condition • Required for this class 

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

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

12. 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…

13. 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)

14. 3 Table Inner Join Example

15. 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

16. 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…

17. 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'

18. 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

19. 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