Unformatted text preview:

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 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 relationshipsMain 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 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 matterOrder of tables in ON condition doesn’t matterQualifying Field NamesWhat if a join query retrieves a field that exists in both tables?Qualifying Field NamesYou qualify the field name in the SELECT clausePreface the field name with the name of either tableShorthand way to write queries by abbreviating table namesPros & cons?Table AliasesNOTE:Once you createa table alias, youhave to use it everywhere…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 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 TablesYou can join any number of tables, provided primary key/foreign key relationships existChallenge: Including all necessary tables in the query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…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 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 fieldsQuery Design DiagramsVisual way to identify display, join, and search fieldsProcess:1. Identify every table in the query2. Identify every involved field in each tableLabel whether it is a display, search, or join field3. Create join condition


View Full Document

UWEC CS 146 - Join Queries

Download Join Queries
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Join Queries and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Join Queries 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?