Hello everybody,
My name is Michael, and today’s post will be a continuation of the previous post’s lesson on JOINs. As I mentioned in the previous post, JOINs are queries that join columns from different tables together based on a common column in each table (or in the case of SELF-JOINs, connected columns from the same table-more on that later in this post).
The first type of JOIN I will cover in this post is FULL JOIN, which returns all records from both the left-linked and right-linked tables and fills in NULL for missing matches from either table. Here’s an example:
I selected the fields Name and Release Date from the Album table and the fields Ceremony and Won/Nominated from the Awards table (which I FULL JOINed to the Album table). I then linked the two tables using the idAlbum/Album_idAlbum primary/foreign key. Had there been an album that wasn’t in consideration for an award (for instance Grace VanderWaal’s Just The Beginning), then the Ceremony and Won/Nominated fields corresponding to that album would NULL (Name and Release Dat will still be displayed).
With FULL JOINs, keep in mind that if you don’t filter out results, you could end up with a large result set (this query returned 216 rows of data). Here’s the same FULL JOIN query except with a WHERE clause to filter out results:
This query is similar to the previous one, except that it only shows the rap albums that were considered for awards (and as a result, show only 41 rows of data as opposed to the 216 rows returned from the previous query).
The next type of join I will cover is the SELF JOIN, which basically joins a table to itself. Here’s an example:
I selected the fields Singer Name, Age, and Member of from the Singer table, but there’s a catch. See, because I’m joining a table to itself, I need to give aliases to each of the instances of the table in this query. I went with S* for the Singer table before the JOIN clause and B** for the Singer table after the JOIN clause. As you can see, I had to include the aliases before each field name (S for Singer Name and Age and B for Member of). In my WHERE clause, notice how I mention Member of twice in the line WHERE B.Member of IS NOT NULL AND S.Member of IS NOT NULL. I did this so that only the members of a band are listed alongside the corresponding band/group; when I only included WHERE B.Member of IS NOT NULL, the band name would display alongside itself (e.g. Imagine Dragons was listed as a member of Imagine Dragons).
*for Singer
**for Band (or Group)
Now let’s filter the results further:
It’s the same query as above, except with the addition of the AND S.Death IS NOT NULL, which asks the query to display any deceased singers part of groups. As you can see, only 1 singer in the database meets this criteria (Linkin Park’s Chester Bennington, who died on July 20, 2017).
Let’s try a different filter to the query:
This is similar to the first SELF JOIN query I did, except the filter asks the query to only display band singers under 40 years old (hence the S.Age < 40 line), of which there are 10.
The last JOIN I will cover in this post is the UNION query, which, even though it does not have JOIN in its name, still serves a function similar to JOINs (except entire queries are being joined together, not just tables).
Here’s an example of a UNION query:
In each individual query, I selected the fields Track Name and Track Duration from the Tracks table and joined the two queries together with a UNION clause. The one difference between the two queries is their WHERE clauses; the top query retrieves the name of the longest song (Empire of the Clouds by Iron Maiden at a whopping 18 minutes and 1 second) and the bottom query retrieves the name of the shortest song (Intro by 50 Cent at a mere 6 seconds).
Some things to keep in mind with UNION queries include:
- UNIONs only work with the same number of columns in both queries (I selected two columns in both the top and bottom queries)
- They work best when the columns selected in each query-along with any aliases- and the order in which the columns/aliases are listed are identical. Though there should be at least one difference between the two queries, as I have in my query (the WHERE clause has MAX for the top query and MIN for the bottom one).
Here’s another slightly more complex UNION query
In each query, I selected the foreign key Album_idAlbum and the aggregate function COUNT(Album_idAlbum)-using the alias Awards Considerations-from the Awards table. I also remember to GROUP BY the same thing in each table (the Album_idAlbum foreign key). The one difference between each query is the WHERE clause, as I selected Album_idAlbum 34 for the top query and Album_idAlbum 48 for the bottom one (both are rock albums, with 34 being Mylo Xyloto by Coldplay and 48 being Smoke+Mirrors by Imagine Dragons). As you can see, Mylo Xyloto was considered for 8 awards while Smoke+Mirrors was only considered for 1 award.
Before I go, here’s a spreadsheet with the modified Singer table (couldn’t save SQL files on WordPress)-Singer table modified.
Thanks for reading,
Michael