The Equi-Join

[Audio Starts at 00:00:00]

Taylor: The problem with the cross join of two tables is that it indiscriminately joins every row from the first table with every row of the second. Generally what you want is for rows from the first table to be joined only with rows of the second table that satisfy some condition. There are a variety of possible conditions that might make sense but probably the most common is that some field in the first table has a value that is equal to the value of a corresponding field in the second table. The Equi-join is the operation that brings about this condition. A select statement with an Equi-join will only return rows where this equality condition is satisfied cutting down massively on the number of rows returned.

To see how an Equi-join can be useful let’s look again at the artist and albums tables in the chinook.db database. What I’m interested in is which artists has recorded each album. I am not interested in learning which artist did not record a given album. I can find what I want by starting with the cross join used in the previous segment supplemented by a where clause that adds a condition to the query. Select albums.title, artists.name from albums, artists where albums.artist’s ID equals artists.artist’s ID. Clicking on the execute symbol returns 347 rows. The exact number of albums Chinook has in inventory. Each one associated with the artist who recorded that album. This is useful information. The name Equi-join comes from the equality condition in the where clause.

What about the three table case? Suppose we want a list of albums, the artists who recorded those albums and the tracks contained on those albums? We can get that with select albums.title, artists.name, tracks.name from albums, artists, tracks where albums.artist’s ID equals artists.artist’s ID and albums.album ID equals tracks.album ID. Three thousand, five hundred and three rows are returned for 347 albums that’s about what I would expect for the number of tracks. A quick scan of the result set shows that the albums, artists and tracks are all correctly associated with each other. The where clause has a compound condition in which the and key word dictates that the artist ID field in the album’s table must match the artist ID field in the artist table and the album ID field in the album’s table must match the album ID field in the track’s table.