Joining the Charts: Exploring SQL JOINs

Introduction and Setting the Stage

Hello there! I am really excited to have you as a part of our exhilarating learning journey - Learning SQL Joins with Taylor Swift . This course merges the charm of Taylor's music with the thrill of data analysis, creating an engaging learning experience for you. We are about to dive deep into the world of SQL JOINs, connecting tables in complex but meaningful ways, much like notes are bound together to form a melody. Our tool of choice for this course is MySQL, a popular database management system used globally. However, if you are planning on using a different SQL-based system, don't worry - the concept of JOINs remains the same across all platforms.

Brief Dataset Description

Without any further ado, let's introduce our dataset, which is inspired by the discography of Taylor Swift and contains three main tables. Below are some sample rows to give you an idea of the structure starting from the simplest table: Albums Table AlbumID AlbumName ReleaseDate 22 Speak Now (Deluxe Edition) 2010-10-25 23 Speak Now 2010-10-25 24 Fearless Platinum Edition 2008-11-11 25 Fearless 2008-11-11 Albums table contains all of Taylor's albums including release dates.

AlbumID	AlbumName	ReleaseDate
22	Speak Now (Deluxe Edition)	2010-10-25
23	Speak Now	2010-10-25
24	Fearless Platinum Edition	2008-11-11
25	Fearless	2008-11-11

Songs Table

SongID AlbumID Name TrackNumber URI Popularity DurationMS 0vqI4ZIMuifeKeItGiWbPj 19 Starlight 15 spotify:track:0vqI4ZIMuifeKeItGiWbPj 40217826 0vvt4IZOMkRug195S4MUq0 22 If This Was A Movie 16 spotify:track:0vvt4IZOMkRug195S4MUq0 46234546 0wavGRldH0AWyu2zvTz8zb 6 Sweet Nothing 12 spotify:track:0wavGRldH0AWyu2zvTz8zb 73188496 0XfOV7qY3834QpFVwOb6CC 19 Treacherous 3 spotify:track:0XfOV7qY3834QpFVwOb6CC 41240773 This table lists Taylor Swift's songs, linking them to their albums, and includes details about track numbers, Spotify URIs, popularity ratings, and song lengths in milliseconds.

SongID	AlbumID	Name	TrackNumber	URI	Popularity	DurationMS
0vqI4ZIMuifeKeItGiWbPj	19	Starlight	15	spotify:track:0vqI4ZIMuifeKeItGiWbPj	40	217826
0vvt4IZOMkRug195S4MUq0	22	If This Was A Movie	16	spotify:track:0vvt4IZOMkRug195S4MUq0	46	234546
0wavGRldH0AWyu2zvTz8zb	6	Sweet Nothing	12	spotify:track:0wavGRldH0AWyu2zvTz8zb	73	188496
0XfOV7qY3834QpFVwOb6CC	19	Treacherous	3	spotify:track:0XfOV7qY3834QpFVwOb6CC	41	240773

Join the 1M+ learners on CodeSignal

Be a part of our community of 1M+ users who develop and demonstrate their skills on CodeSignal

Understanding Different Types of SQL JOINs

SongID	Acousticness	Danceability	Energy	Instrumentalness	Liveness	Loudness	Speechiness	Tempo	Valence
4WUepByoeqcedHoYhSNHRt	0.00942	0.757	0.61	0.0000366	0.367	-4.84	0.0327	116.998	0.685
0108kcWLnn2HlH2kedi1gn	0.0885	0.733	0.733	0	0.168	-5.376	0.067	96.057	0.701
3Vpk1hfMAQme8VJ0SNRSkd	0.000421	0.511	0.822	0.0197	0.0899	-4.785	0.0397	94.868	0.305
1OcSfkeCg9hRC2sFKB4IMJ	0.000537	0.545	0.885	0.0000559	0.385	-5.968	0.0447	92.021	0.206

Before diving deeper into the world of SQL JOINs, it's essential to understand the different types of joins available at our disposal. Each type serves a unique purpose, enabling us to tailor our queries to fetch the exact data we need. Let's explore the primary types of JOINs you'll encounter:

INNER JOIN

An INNER JOIN, often just referred to as JOIN, is the most common type of join. It returns records that have matching values in both tables involved in the join. If there’s no match, the rows won’t appear in the result. It’s the default type of JOIN if no specific JOIN type is specified. In MySQL, INNER JOIN and JOIN are used interchangeably, which might lead to some confusion for beginners. However, they effectively perform the same operation.

Example:

SQL

SELECT Songs.Name, Albums.AlbumName
FROM Songs
INNER JOIN Albums ON Songs.AlbumID = Albums.AlbumID;

SELECT Songs.Name, Albums.AlbumName
FROM Songs
INNER JOIN Albums ON Songs.AlbumID = Albums.AlbumID;

LEFT JOIN (or LEFT OUTER JOIN)

A LEFT JOIN (also known as a LEFT OUTER JOIN) returns all records from the left table and the matched records from the right table. If there is no match, the result set will include NULL values for columns of the right table. This type of join is useful when you want to include all records of one table (the left one) regardless of whether they have corresponding matches in the other table.

Example:

SQL

SELECT Songs.Name, Albums.AlbumName
FROM Songs
LEFT JOIN Albums ON Songs.AlbumID = Albums.AlbumID;

SELECT Songs.Name, Albums.AlbumName
FROM Songs
LEFT JOIN Albums ON Songs.AlbumID = Albums.AlbumID;

RIGHT JOIN (or RIGHT OUTER JOIN)

Conversely, a RIGHT JOIN (or RIGHT OUTER JOIN) returns all records from the right table and the matched records from the left table. Similar to the LEFT JOIN, if there's no match, the result set will include NULL values for the unmatched columns of the left table.

Example:

SQL

SELECT Songs.Name, Albums.AlbumName
FROM Songs
RIGHT JOIN Albums ON Songs.AlbumID = Albums.AlbumID;

SELECT Songs.Name, Albums.AlbumName
FROM Songs
RIGHT JOIN Albums ON Songs.AlbumID = Albums.AlbumID;

FULL JOIN (or FULL OUTER JOIN)

A FULL OUTER JOIN returns all records when there’s a match in either left or right table. This join combines the effects of both LEFT JOIN and RIGHT JOIN. If there’s no match, the result set will have NULL values for every column of the table that lacks a matching row. Please note, FULL JOIN is not directly supported in MySQL, but can be simulated with a combination of LEFT JOIN, RIGHT JOIN, and UNION. We'll see how later in this course.

Introduction and Setting the Stage

Brief Dataset Description

Songs Table

Join the 1M+ learners on CodeSignal

SongFeatures Table

What are SQL JOINs?

Understanding Different Types of SQL JOINs

Wrapping Up and Gearing Up for Practices