Week 04 Notes 02 – Outer Joins & Unions

Inner join review

(Inner) Joins are a multiplicative operation. The number of records in the result of a join operation is the number of matching records in table 1 * the number of matching records in table 2.

Consider the following tables:

Plays:

Watches:

SELECT *

From Plays As P INNER JOIN Watches as W ON

P.player = W.name

Using the algorithm shown in the last notes yields:

Notice the number of records having player = name in the result is the number of records with this name in plays * the number of records with this name in watches:

1. There are 2 records in Plays where player = Jimbob and 2 records in Watches where name = Jimbob. 2 * 2 = 4 records in the query where both player and name = Jimbob.

2. There are no EmmaJean records in the result. (2 records in plays * 0 records in watches = 0 records in the result)

Outer Joins

Can include records from one side of the join that do not have a matching record by using keyword LEFT or RIGHT.

All records in the LEFT or RIGHT table, depending on which kind of outer join you use, are included in the result.

Records that do not have matches have null values in some of the result fields.

You use an outer join when at least one of every record in one side of the join needs to be included in the result.

Left Join Example:

Player0

Plays

SELECT *

From Player0 Left Join Plays on

Player0.name = Plays.player

Yields:

Notice:

1 Player Jimbob record * 2 Plays Jimbob records = 2 result Jimbob records

BillyJoeBob and BillieJean are records present in the Left (Player) table, but not in the Right (Plays) table, so they are included in the result set, but have null values for the player and sport fields.

Right Join Example:

Same input tables (Player and Plays) as the last example:

SELECT *

From Player0 Right Join Plays on

Player0.name = Plays.player

Yields:

Notice all records in Plays (Right) are included, and all of these records have matches in Player (Left), so all records have values for all fields.

Union

A Union is an additive operation rather than the multiplicative join operation.

If I Union two tables, I get the records from both tables, but I don’t get ‘extra’ records, as I do in the join operation.

A union is usually used when you have data with the same ‘meaning’ coming from different parts of the DB and you want to combine these results.

Unions can eliminate duplicate records (UNION) or preserve duplicates (UNION ALL)

UNION Example

Consider the plays and watches tables:

Plays:

Watches:

If I want to know the name of anyone who either plays or watches a sport, I could do:

SELECT *

From Plays

UNION

Select *

From Watches

order by player

Yields:

Notice:

0. Only 2 fields, not 4 as there would be in a join. Fields in the result are named by fields in the first table.

1. Jimbob has only 3 records, though there are 4 Jimbob records total in the two tables, but Jimbob tennis is duplicated, and the dup is removed.

2. Wilt has 2 records: 1 play + 1 watches.

SELECT *

From Plays

UNION ALL

Select *

From Watches

order by player

Yields:

SELECT *

From Plays LEFT JOIN Watches ON

Plays.player = Watches.name

Yields:

All 4 (2 + 2) Jimbob records present

2 (1 + 1) Wilt records present

4 (2 * 2) Jimbob records

1 (1 * 1) Wilt records