Join QueriesIntroduction: Join QueriesMain Types of Join QueriesExample Inner JoinJoin Query Syntax (ANSI 1992)Join Query Example (ANSI 1992)Qualifying Field NamesSlide 8Table AliasesInner Join of 3 Tables3 Table Inner Join ExampleJoining N TablesSlide 13ExampleDesigning Complex Join QueriesQuery Design DiagramsJoin QueriesCS 146Introduction: Join QueriesSo far, our SELECT queries have retrieved data from a single tableUsually queries combine data from multiple tables:List how much (pounds) of each product that was purchased todayList the customer name and product name for a specific purchaseQueries that retrieve data from multiple tables require joining the tables through primary key/foreign key relationshipsMain Types of Join QueriesInner JoinRetrieves all matching fields in joined tablesAlso called equijoin or natural joinOuter JoinRetrieves all fields in one table, and matching fields in second table if they existExample Inner JoinCANDY_CUSTOMERCANDY_PURCHASECANDY_PRODUCTJoin Query Syntax (ANSI 1992)The word "INNER" is optionalSELECT Column1, Column2, …FROM Table1 INNER JOIN Table2ON Table1.JoinColumn = Table2.JoinColumnWHERE SearchCondition(s)Join conditionJoin Query Example (ANSI 1992)Note:Order of tables in FROM clause doesn’t matterOrder of tables in ON condition doesn’t matterQualifying Field NamesWhat if a join query retrieves a field that exists in both tables?Qualifying Field NamesYou qualify the field name in the SELECT clausePreface the field name with the name of either tableShorthand way to write queries by abbreviating table namesPros & cons?Table AliasesNOTE:Once you createa table alias, youhave to use it everywhere…Inner Join of 3 TablesGeneral syntax:Note:Placing each INNER JOIN and ON clause on a separate line makes the query easier to read and understandSELECT Column1, Column2, …FROM Table1 INNER JOIN Table2ON Table1.JoinColumn = Table2.JoinColumnINNER JOIN Table3ON Table2.JoinColumn = Table3.JoinColumnWHERE SearchCondition(s)3 Table Inner Join ExampleJoining N TablesYou can join any number of tables, provided primary key/foreign key relationships existChallenge: Including all necessary tables in the queryYou can join any number of tables, provided primary key/foreign key relationships existChallenge: you need to include table in join queries to provide needed links even if you don't include fields in the SELECT clause…Joining N TablesExampleCANDY_PRODUCTprod_desc (D)prod_id (J)CANDY_CUSTOMERcust_name (S)cust_id (J)CANDY_PURCHASEprod_id (J)cust_id (J)SELECT prod_descFROM candy_product INNER JOIN candy_purchaseON candy_product.prod_id = candy_purchase.prod_idINNER JOIN candy_customerON candy_purchase.cust_id = candy_customer.cust_idWHERE cust_name = 'Bobby Bon Bons'Designing Complex Join QueriesTerminology:Display field: Retrieved data field Appears in the SELECT clauseJoin field Primary or foreign key used to join tablesAppears in a join conditionSearch fieldUsed in a search conditionAppears in the WHERE clauseJoin queries must include all tables that contain display, join, or search fieldsQuery Design DiagramsVisual way to identify display, join, and search fieldsProcess:1. Identify every table in the query2. Identify every involved field in each tableLabel whether it is a display, search, or join field3. Create join condition
View Full Document