Data Manipulation Joins Objectives To learn how to

Data Manipulation - Joins • Objectives – To learn how to join several tables together to produce output • Contents – Extending a Select to retrieve data from 2 tables via the SQL 92 Join syntax – What is an INNER JOIN? Is there a default JOIN? – CROSS Joins (SQL 92) – SQL 92 & SQL 89 comparison and key differences – Oracle considerations – Using table aliases – Joining more than two Tables – Composite Joins – Practical 5 -1 – Outer Joins – Practical 5 -2 JOI/1

Retrieving data from two tables (SQL 92) SELECT FROM salesperson. dept_no, dept_name, lname salesperson JOIN dept ON salesperson. dept_no = dept_no ** NOTE ** Not ORACLE! • Both tables appear in FROM clause, sequence irrelevant • Link the tables in the FROM clause with the keyword JOIN • The ‘ON’ sub-clause relates a row of one table to a row of the other table, via one or more columns – Note the tablename. columnname syntax to avoid ambiguity • No ability to predefine or store this join relationship in DBMS JOI/2

Joining – the one-to-many salesperson (‘many’) emp_no 10 20 30 fname lname fred bob sue smith james brown SELECT FROM result set emp_no 10 20 30 Foreign Key Primary Key dept_no dept (‘one’) dept_no dept_name 1 2 marketing sales 1 1 2 * salesperson JOIN dept ON salesperson. dept_no = dept_no fname lname dept_no fred bob sue smith james brown 1 1 2 ** NOTE ** Not ORACLE! dept_name marketing sales JOI/3

Joining as De-Normalisation • In chapter 2 we considered why we ‘normalise’ data into many tables – To avoid INSERT/UPDATE/DELETE anomalies and data duplication • Downside of ‘normalisation’ is we have to do JOINS – JOINS are ‘realtime denormalisation’ – The JOIN effectively puts all the ‘dept’ data back – produces information that is useful and meaningful to the business – (refer back to chapter 2 page 6 and page 5!) JOI/4

The INNER JOIN (SQL 92) SELECT FROM salesperson. dept_no, dept_name, lname salesperson INNER JOIN dept ON salesperson. dept_no = dept_no ** NOTE ** Not ORACLE! • Some RDBMS’s require the word INNER (or an alternative!) • If a DBMS allows omission of ‘INNER’ it will default to ‘INNER’ • Meaning: SELECT only the rows where there is a match between the two columns referred to in the ON clause • Sometimes referred to as “equi-joins” JOI/5

The CROSS JOIN (SQL 92) SELECT FROM columnlist -- which column(s) are irrelevant salesperson CROSS JOIN dept ** NOTE ** Not ORACLE! • CROSS Joins – No ON sub-clause – Will simply join every row of one table with every row of the other – 4 depts * 6 salespeople = 24 combinations – 400 depts * 60000 people = ‘a long running query’ = embarrassment! – The answer set is known as a Cartesian Product – Rarely used as the answer set is normally meaningless – Could generate test data quickly via INSERT INTO table. X SELECT column_list FROM table. Y CROSS JOIN table. Z JOI/6

SQL 92 and SQL 89 JOINS compared • These two queries produce exactly the same results: – SQL 92 SELECT FROM salesperson. dept_no, dept_name, lname salesperson INNER JOIN dept ON salesperson. dept_no = dept_no WHERE county = ‘Surrey’ Nearly all DBMS’s but not Oracle – SQL 89 SELECT FROM WHERE AND salesperson. dept_no, dept_name, lname salesperson, dept salesperson. dept_no = dept_no county = ‘Surrey’ Nearly all DBMS’s and Oracle • Note that Oracle only supports the ‘ 89 version! – virtually all DBMS’s that support ‘ 92 still continue to support ’ 89 – much existing code uses ’ 89 syntax JOI/7

SQL 92 and SQL 89 JOINS - Key differences • FROM clause – SQL 92 uses [INNER] JOIN; SQL 89 uses a comma to separate table names • ON sub-clause – SQL 92 must have one (assuming it is not a CROSS JOIN); SQL 89 never has one • WHERE clause – SQL 92 doesn’t need it for the JOIN logic as it is coded in the ON clause – SQL 89, required to hold the ‘join’ logic; omit it and you get a Cartesian Product! SQL 92 SQL 89 SELECT salesperson. dept_no, dept_name, lname FROM salesperson INNER JOIN dept ON salesperson. dept_no = dept_no WHERE county = ‘Surrey’ SELECT salesperson. dept_no, dept_name, lname FROM salesperson, dept WHERE salesperson. dept_no = dept_no AND county = ‘Surrey’ JOI/8

Table Aliases SELECT FROM salesperson. dept_no, dept_name, lname salesperson JOIN dept ON salesperson. dept_no = dept_no • Can be coded as SELECT FROM SP. dept_no, dept_name, lname salesperson as SP JOIN dept as D ON SP. dept_no = D. dept_no • Alias is (usually) optional, but saves some typing • Renames table throughout the query – ‘AS’ keyword is optional – Same in SQL 92 and SQL 89 – The alias appears to be used (in the SELECT clause) before you have defined it (in the FROM clause) but remember the DBMS must read the FROM first (and so should you!) JOI/9

Joining more than two tables (‘ 92 & ‘ 89) SELECT FROM SP. dept_no, dept_name, lname sale S JOIN salesperson SP ON S. emp_no = SP. emp_no JOIN dept D ON SP. dept_no = D. dept_no SELECT FROM WHERE AND SP. dept_no, dept_name, lname sale S, salesperson SP, dept D S. emp_no = SP. emp_no SP. dept_no = D. dept_no SQL 92 SQL 89 • Aliasing and ambiguity considerations are identical • Using ‘ 92 syntax, the 3 rd and subsequent tables are JOINed via the next ON sub-clause to a table that has already been referenced • In either syntax, ‘n’ JOINS require ‘n’ join conditions • In ‘ 89 syntax one could easily omit a condition (part of the WHERE clause) and get a partial cartesian product - be careful! JOI/10

Composite Joins • The contact table has a composite primary key: SQL 92 SELECT C. name, S. * -- meaning all the columns of sale FROM sale S JOIN contact C ON S. company_no = C. company_no AND S. contact_code = C. contact_code SQL-89 SELECT FROM WHERE AND All parts of the key must be specified C. name, S. * sale S, contact C S. company_no = C. company_no S. contact_code = C. contact_code If you miss out half the join it will still run and produce unwanted extra rows. Such errors are not always readily detected. JOI/11

Ch 9 Practical 1 - Inner Joins • Inner Joins on two or more tables • You may write your code using ‘ 92 or ‘ 89 syntax (or both!) – Supplied solutions will include both – We would suggest using the ‘ 92 syntax unless – Oracle is your primary RDBMS or – You know that the majority of the code you will support is ‘ 89 syntax JOI/12

Outer Joins (SQL 92) SELECT FROM manager, lname dept D INNER JOIN salesperson SP ON D. dept_no = SP. dept_no • The INNER JOIN selects all salespersons, but not managers of depts with no people SELECT FROM manager, lname dept D LEFT OUTER JOIN salesperson SP ON D. dept_no = SP. dept_no • The OUTER JOIN also includes rows that have no match – Left – Right – Full – With Null in the ‘many’ field Note dept LEFT JOIN salesperson equals salesperson RIGHT JOIN dept JOI/13

Outer Joins (General) • Consider: dept RIGHT JOIN salesperson i. e ‘one’ RIGHT JOIN ‘many. ’ – Would it mean anything? Produce any extra output? JOI/14

Outer Joins (General) SELECT FROM ON * company C LEFT JOIN sale S C. company_no = S. company_no – If you add WHERE order_no is NULL it reduces the output to just those companies who have not been sold to – It is therefore not always necessary to use – NOT EXISTS, NOT IN, < > (and we have been!) JOI/15

Outer Joins (Pre SQL 92) • MS SQL Server V 6. 0 (still supported as of V 7): SELECT FROM WHERE manager, lname dept D , salesperson SP D. dept_no *= SP. dept_no • Oracle equivalent: SELECT FROM WHERE Both Left Outer Joins manager, lname dept D, salesperson SP D. dept_no = SP. dept_no(+) Note: The table with the ‘extra’ rows is often referred to as the OUTER table. JOI/16

Ch 9 Practical 2 - Outer Joins • You will need to code these solutions using the SQL 92 Outer Join syntax or the MS SQL Server pre. SQL 92 proprietary method JOI/17

Summary • Joins – A mechanism to realtime denormalise tables to produce output • There are different join types – In this chapter we looked at Inner, Cross and Outer joins – The syntax for Outer Joins is new to 92 although some DBMS’s had ways of achieving same prior to SQL 92 • Composite key joins – Sometimes we need to include several columns to join tables correctly • Ambiguity is an issue to be addressed – Aliases help with ambiguity but are not essential JOI/18