IFS180.81 Intro. to Data Management

1. IFS180.81 Intro. to Data Management Chapter 8 Inner Joins

2. Joining Tables • Concept of linking together two or more tables, required if DB is 3rd normalized form • Create a result set of related information • Create a result set based on matching values • Returning columns in the result set • Generally linking Primary Key and Foreign Key. However, you can link any column.

3. Intersection Set Theory • Interested in finding commonality between two (or more sets) • Attributes describe the properties of each member • Set member is not simply the matching value (i.e. PK or FK values) but rather the entire row associated with the matching value

4. Intersections Attributes: Black Allister Seattle WA 166 Hallmark Joe Woodin WA 157 Ehrlich Zachary Seattle WA 143 Rojales Joe Tacoma WA 166 Patterson Rachel Auburn WA 157 Riser Brian Seattle WA 152 Hindman Rick Duvall WA 145 Manken Don Bellevue WA 157 Rojales Joe Tacoma WA 166 Jones Fred Seattle WA 158

5. Intersections Last Name First Name City St Avg Identification Black Allister Seattle WA 166 Hallmark Joe Woodin WA 157 Ehrlich Zachary Seattle WA 143 Rojales Joe Tacoma WA 166 Patterson Rachel Auburn WA 157 5 7 10 Primary Key Monday League 15 17 Riser Brian Seattle WA 152 Hindman Rick Duvall WA 145 Manken Don Bellevue WA 157 Rojales Joe Tacoma WA 166 Jones Fred Seattle WA 158 1 3 8 Primary Key Tuesday League 15 20

6. Intersections • Intersection use to find equality between one or more data sets Members Names Joe Average > 160 Members in Seattle Seattle, Joe, > 160 Seattle and Joe

7. Intersection • Set theory intersection implemented in SQL standard with the (INNER) JOIN operation • Join on specific columns • Creating a “Logical” table within the DB • Join on any field as long as data types are the same  Performance issue • Modify the from clause to contain two or more table names from the DB • Imperative to understand the relationships between the tables

8. SQL JOIN • From Clause (single table) From TABLENAME • From Clause (multiple table) From TABLENAME1 as T1 INNER JOIN TABLENAME2 as T2 ON Search Condition - OR - Using Column name  Limited Support • With multiple table reference, good practice dictates the use of fully qualified names (table references) or (as per the SQL standard) Correlation Names

9. Join on column examples • Show me a list of bowling team names and the members on each team • Some DB’s do not support the Join statement, however tables can still be linked (PK or FK only) and use WHERE clause instead of the Join search criteria • Show me a listing of bowling team names, team members where raw scores between 150 and 155  Embedded Join

10. Join with Embedded Select Statements • Substitute a complete select statement for a table name • Requires a correlation name assigned to the embedded select (for reference purposes) • Embedded select may include all clauses except order by clause • In essence, query within a query and you must return any column and linking values for joins

11. Embedded Select Examples • Create a list of Team captain ID’s and names where the captain lives in Redmond • Create a list of Bowlers that have the same average.

12. Relationships • To be successful when joining tables it is imperative to understand the table relationships. • Most DB vendors provide a graphical format for viewing table relations. • Typically, these relationships are called ERD’s or Entity Relationship Diagrams