Hello everybody,
It’s Michael, and this post (and the next) will be covering joins in MySQL. Joins are essentially queries that are meant to retrieve data from several tables rather than just a single table. I’ll take two posts to cover joins because there are several different types of joins.
But first, here’s a slight modification to the database, which I felt would be appropriate when learning about joins:
The two new fields included are:
- Singer_idSinger-Ok, this is more of a foreign key, but it indicates the band/group the singer belongs to (e.g. Dan Reynolds is a part of the band Imagine Dragons). For individual singers, the value in this field will be the same as the value in the idSinger primary key.
- Member of-What band (if any) a singer is part of. If the singer isn’t part of a band, then this field will be NULL.
The arrow on top of the singer table indicates a unary one-to-many relationship. Unary basically means the table relates to itself (which is perfectly fine by MySQL standards); the one-to-many in this case means that a band must have MANY (at least 2) singers while each singer must belong to only ONE band.
This type of relationship works best for SELF JOIN queries, which are queries that join a table to itself. I’ll cover them more in the next post.
The first type of join I will focus on is an INNER JOIN. Here’s a query using INNER JOIN:
INNER JOIN queries basically join columns from different tables using values common to both tables (which are usually foreign/primary keys); results are returned only if a row in both tables matches the JOIN condition. In this case; the common column is Singer_idSinger-the Singer and Album tables both have this column.
- You don’t have to display the common column in the query results; just remember to include it in the ON clause. This goes for all JOIN queries.
I first selected the field Singer Name from the Singer table and the fields Name and Release Date from the Album table (which is INNER JOINed to the Singer table). I then linked both tables through the Singer_idSinger primary/foreign key (which is the common column among both tables). I finally narrowed down the criteria to only include albums WHERE Release Date > Death so that only posthumous albums (along with corresponding singer name and release dates) are displayed.
- INNER JOIN queries will work the same without the word INNER, as seen below:
The next type of JOIN I will cover is a LEFT JOIN, which basically goes:
- SELECT (columns) FROM (table A) LEFT JOIN (table B) ON (table A.column) = (table B.column)
Like INNER JOIN queries, LEFT JOIN queries link tables based on common columns. The difference between these two types of queries are that INNER JOIN queries retrieve results that have matching values in both tables, while LEFT JOIN queries retrieve all results from the left-linked table (table A) and any matching results from the right-linked table (table B). If there are no matching results from the right-linked table, NULL will be displayed on any rows connected to the right-linked table (this is not the case with INNER JOINS).
Here’s an example of a LEFT JOIN query:
In this query, I am selecting the fields Track Name from the Tracks table and Featured Artist Name from the Featured Artist table (which is LEFT JOINed to the Tracks table). I filtered out the results so that only tracks WHERE Album_idAlbum = 44 (44 is the primary key for Linkin Park’s The Hunting Party if you were wondering) are displayed. The query will display all the records from the left-linked table (in other words, every track from The Hunting Party) along with matching records from the right-linked table (meaning if there are any guest artists on a track). For tracks without guest artists, NULL is displayed on the right side rows.
The last of the joins I will cover is the RIGHT JOIN, which is basically the opposite of LEFT JOIN because the query retrieves all results from the right-linked table (table B) and any matching results from the left-linked table (table A). Should there be no matching results in the left-linked table, NULL will be displayed on any rows connected to the left-linked table (so basically the opposite of what happens in a LEFT JOIN query).
Here’s an example of a RIGHT JOIN query:
In this query, I selected the field Name from the Album table and the field Won/Nominated from the Awards table (which is RIGHT JOINed to the Album table). I then used the idAlbum/Album_idAlbum (they’re the same thing) primary/foreign keys to link the two tables. I also narrowed down the criteria so as to only display albums WHERE Won/Nominated = "Won" AND Ceremony = "Grammys" (referring to album that won Grammys). The query will display all results from the right-linked table (any Grammy wins) and any matching records from the left-linked table (the names of albums that won Grammys).
- For every result from the right-linked table (Awards), there are matching results from the left-linked table (Album). Had this not been the case, you would be seeing NULL in rows linked to the Name column.
Thanks for reading, and be sure to check out the next post with more JOINs.
Michael