Table Joins

A Table JOIN allows you to place a number of tables' data side by side in the same answer table. This convenience allows comparisons between corresponding rows in related tables, and the connection of parts of a related fact.

There are many types of join - the CROSS join (or un-constrained join) being the most primitive.

select *
from Albums, Tracks

produces a truly huge answer table, with 8 columns. The query places every row in the Albums table beside every row in the Tracks table, whether it is related or not

A Join Condition ensures that the rows from joined tables relate to each other (eg. 'where Albums.SerNum = Tracks.SerNum'). The effect of a join condition is often to remove the junk from the answer table

This differs from a filter clause (eg. where PDate=1990) which removes meaningful or valid rows that are not wanted.

EquiJoins (Natural Inner Joins)

When joining tables that share a common column, an equijoin is one alternative.

select AlbName, Song from Albums, Tracks where Albums.SerNum = Tracks.Sernum and Artist = 'Manilow, Barry'

The diagram above, and the corresponding SQL demonstrate the connecting of related columns with an equality constraint. The join condition for this query is "where Albums.SerNum = Tracks.Sernum" - this ensures that tracks are attached to their containing album. The condition "and Artist = 'Manilow, Barry'" is a filter clause, and ensures we only see a subset of correctly joined rows.

In microSQL, there are a number of syntactic variations on this theme, all of which do much the same job:

select AlbName, Song
from Albums inner join Tracks on albums.sernum = tracks.sernum
where Artist = "Manilow, Barry"

is functionally identical to the query beside the diagram and requires less typing. If you are really keen, you can replace the microSQL keyword join with natural inner join, but there appears to be little incentive to do this, save a little typing practice.

The natural inner join operator becomes syntactically 'tricky' when the columns being joined do not have matching names, or share many column names that are the same. This complication can be solved using the ON and USING modifiers, where the actual columns you want to be the basis of the join are listed. In such situations, I generally revert back to the explicit join, with table qualifiers on column names. I think students find this approach less confusing. Joins are generally not confusing when diagrammed however as the join conditions are explicitly indicated.

Left Outer Joins

Conventional inner joins connect rows from one table only if there are corresponding or related rows in the paired table. A left outer join allows you to pair rows from one table (the left one) with either nulls (where there are no related rows) or related data. The table on the left of the operator has ALL it's population represented in the answer table.

Q: List all of the album names of cassettes together with the songs that are featured on the album if they are recorded songs

select AlbName, song from Albums Left Outer Join Tracks where media = 'CA'

We use a similar notation when constructing a right outer join, although I see little benefit in using this operator when, by re-writing the from clause in a different order, a right outer join can become a left outer join.

Unless otherwise instructed, students are encouraged to solve queries using whatever tool they understand - subquery or join. Relational Database Management Systems are either optimised to perform one or the other (rarely both). MS Access, for example, is optimised to perform joins, performing subqueries noticably slower. MicroSQL on the other hand is optimised to perform subqueries.

It is generally correct that intersection queries can be re-written as join queries by replacing the sub-query arrow with an equijoin. This is not the case with a difference query however.