Lesson 2
Linking Albums to Songs with INNER JOIN
Introduction and Review

Hello and welcome back! It's great to see you ready to delve further into the world of SQL JOINs with our musical guide, Taylor Swift's discography. In our first unit, we spent time understanding different kinds of JOINs and exploring all the tables in our database separately. Now it's time to talk more about the INNER JOIN.

Understanding INNER JOIN

In this lesson, our focus narrows to one specific type of JOIN: the INNER JOIN. Essentially, the INNER JOIN is a SQL clause that matches rows from two tables based on a common field between them. Only the rows that satisfy the match condition are returned in the result set.

Instead of diving straight into a specific example, let's look at the general syntax for performing an INNER JOIN:

SQL
1SELECT column1, column2, ...
2FROM table1
3INNER JOIN table2
4ON table1.column_name = table2.column_name;

This syntax serves as the backbone for our queries when we want to combine data from two related tables. We'll delve into how this structure applies to linking Albums and Songs later in this lesson.

By the end of this lesson, we hope you will be equipped to fetch album and song data from the Songs and Albums tables using INNER JOIN. We will use the common key, AlbumID, between these two tables to relate them.

Example of INNER JOIN

Let's kick off with a simple example:

Assume we have a Songs table that contains the song Love Story. This song is present in the Albums table under the album name Fearless. We can use an INNER JOIN to link these two tables:

SQL
1SELECT Albums.AlbumName, Songs.Name 
2FROM Albums 
3INNER JOIN Songs 
4ON Albums.AlbumID = Songs.AlbumID;

In this SQL statement:

  • SELECT Albums.AlbumName, Songs.Name specifies the data we seek — AlbumName from the Albums table and Name from the Songs table.
  • FROM Albums designates the table from which we start our query.
  • INNER JOIN Songs indicates our desire to link the Albums table with the Songs table.
  • ON Albums.AlbumID = Songs.AlbumID is the key condition that connects these tables together. It specifies that the join should happen on the AlbumID columns, which must exist in both tables. This condition ensures that rows from Albums and Songs are matched correctly based on their AlbumID.

When we run the INNER JOIN query on the Albums and Songs tables, the result will be a comprehensive list that pairs each song with its corresponding album This output demonstrates INNER JOIN's capability to efficiently merge related data from two tables, providing a clear and organized view of the relationship between albums and songs. Such insights are invaluable for database exploration and analysis, offering a structured way to navigate through complex data sets.

Changing the Order

Now let's take it one step further and attempt to retrieve all the songs' names and their respective album names in descending order of TrackNumber. Here is what the query would look like.

SQL
1SELECT Albums.AlbumName, Songs.Name 
2FROM Albums 
3INNER JOIN Songs 
4ON Albums.AlbumID = Songs.AlbumID 
5ORDER BY Songs.TrackNumber DESC;

In this case, the ORDER BY Songs.TrackNumber DESC clause sorts the result in descending order by the track numbers.

Conclusion

Well done! You've covered much ground in this lesson. You've learned how to use the INNER JOIN statement to retrieve specific data from two interconnected tables. This SQL tool is indispensable in explorations of the intricate relationships hidden within databases. Keep practicing, and before you know it, you'll be churning out SQL statements as Taylor Swift churns out hits!

Enjoy this lesson? Now it's time to practice with Cosmo!
Practice is how you turn knowledge into actual skills.