JOI/1

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 SQL92 Join

syntax

– What is an INNER JOIN? Is there a default JOIN?

– CROSS Joins (SQL92)

– SQL92 & SQL89 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/2

• 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

Retrieving data from two tables (SQL92)

SELECT salesperson.dept_no, dept_name, lnameFROM salesperson JOIN dept

ON salesperson.dept_no = dept.dept_no

** NOTE **Not ORACLE!

JOI/3

Joining – the one-to-many

emp_no fname lname dept_no

10 fred smith 1 20 bob james 1 30 sue brown 2

dept_no dept_name

1 marketing 2 sales

salesperson (‘many’) dept (‘one’)Foreign KeyForeign Key Primary KeyPrimary Key

SELECT *FROM salesperson JOIN dept

ON salesperson.dept_no = dept.dept_no

emp_no fname lname dept_no dept_no dept_name

10 fred smith 1 1 marketing 20 bob james 1 1 marketing 30 sue brown 2 2 sales

result set

** NOTE **Not ORACLE!

JOI/4

• 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!)

Joining as De-Normalisation

JOI/5

• 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”

The INNER JOIN (SQL92)

SELECT salesperson.dept_no, dept_name, lnameFROM salesperson INNER JOIN dept

ON salesperson.dept_no = dept.dept_no

** NOTE **Not ORACLE!

JOI/6

• 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 tableX

SELECT column_list FROM tableY CROSS JOIN tableZ

The CROSS JOIN (SQL92)

SELECT columnlist -- which column(s) are irrelevantFROM salesperson CROSS JOIN dept

** NOTE **Not ORACLE!

JOI/7

SQL92 and SQL89 JOINS compared• These two queries produce exactly the same results:

– SQL92

– SQL89

• 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

SELECT salesperson.dept_no, dept_name, lnameFROM salesperson INNER JOIN dept

ON salesperson.dept_no = dept.dept_noWHERE county = ‘Surrey’

SELECT salesperson.dept_no, dept_name, lnameFROM salesperson, deptWHERE salesperson.dept_no = dept.dept_noAND county = ‘Surrey’

Nearly all DBMS’s but not Oracle

Nearly all DBMS’s and Oracle

JOI/8

SQL92 and SQL89 JOINS - Key differences• FROM clause

– SQL92 uses [INNER] JOIN; SQL89 uses a comma to separate table names

• ON sub-clause– SQL92 must have one (assuming it is not a CROSS JOIN); SQL89 never has

one

• WHERE clause– SQL92 doesn’t need it for the JOIN logic as it is coded in the ON clause– SQL89, required to hold the ‘join’ logic; omit it and you get a Cartesian

Product!

SELECT salesperson.dept_no, dept_name, lname

FROM salesperson, deptWHERE salesperson.dept_no

= dept.dept_noAND county = ‘Surrey’

SQL 89SELECT salesperson.dept_no,

dept_name, lnameFROM salesperson INNER JOIN deptON salesperson.dept_no

= dept.dept_noWHERE county = ‘Surrey’

SQL 92

JOI/9

• Can be coded as

• Alias is (usually) optional, but saves some typing• Renames table throughout the query

– ‘AS’ keyword is optional– Same in SQL92 and SQL89– 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!)

SELECT salesperson.dept_no, dept_name, lnameFROM salesperson JOIN dept

ON salesperson.dept_no = dept.dept_no

SELECT SP.dept_no, dept_name, lnameFROM salesperson as SP JOIN dept as D

ON SP.dept_no = D.dept_no

Table Aliases

JOI/10

• Aliasing and ambiguity considerations are identical • Using ‘92 syntax, the 3rd 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!

SELECT SP.dept_no, dept_name, lnameFROM sale S JOIN salesperson SP

ON S.emp_no = SP.emp_noJOIN dept DON SP.dept_no = D.dept_no

SELECT SP.dept_no, dept_name, lnameFROM sale S, salesperson SP, dept DWHERE S.emp_no = SP.emp_noAND SP.dept_no = D.dept_no

Joining more than two tables (‘92 & ‘89)

SQL92

SQL89

JOI/11

• The contact table has a composite primary key:

SQL92

SELECT C.name, S.* -- meaning all the columns of saleFROM sale S JOIN contact C

ON S.company_no = C.company_noAND S.contact_code = C.contact_code

SQL-89

SELECT C.name, S.*FROM sale S, contact CWHERE S.company_no = C.company_noAND 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.

Composite Joins

All parts of the key must be specifiedAll parts of the key must be specified

JOI/12

Ch9Practical1 - 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/13

• The INNER JOIN selects all salespersons, but not managers of depts with no people

• The OUTER JOIN also includes rows that have no match– Left– Right– Full– With Null in the ‘many’ field

SELECT manager, lnameFROM dept D INNER JOIN salesperson SP

ON D.dept_no = SP.dept_no

Outer Joins (SQL92)

SELECT manager, lnameFROM dept D LEFT OUTER JOIN salesperson SP

ON D.dept_no = SP.dept_no

dept LEFT JOIN salesperson equals salesperson RIGHT JOIN dept Note

JOI/14

• Consider:

i.e ‘one’ RIGHT JOIN ‘many.’

– Would it mean anything? Produce any extra output?

Outer Joins (General)

dept RIGHT JOIN salesperson

JOI/15

Outer Joins (General)

– If you add

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!)

SELECT *FROM company C LEFT JOIN sale SON C.company_no = S.company_no

WHERE order_no is NULL

JOI/16

Outer Joins (Pre SQL92)• MS SQL Server V6.0 (still supported as of V7):

• Oracle equivalent:

SELECT manager, lnameFROM dept D , salesperson SPWHERE D.dept_no *= SP.dept_no

SELECT manager, lnameFROM dept D, salesperson SPWHERE D.dept_no = SP.dept_no(+)

Note: The table with the ‘extra’ rows is often referred to as the OUTER table.

Both Left Outer Joins

JOI/17

Ch9Practical2 - Outer Joins

• Outer Joins

• You will need to code these solutions using the SQL92 Outer Join syntax or the MS SQL Server preSQL92 proprietary method

JOI/18

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 SQL92

• 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