SQL Joins – Lesson 3

Connecting the dots: how SQL joins bring your data to life

👋🏽 Hey, it’s Ismail. Welcome to data nomads lab newsletter on learning data analytics, career growth, networking, building portfolios, and interview skills to break into tech role as a high-performer.

In Lesson 2, we explored SQL basics—how to retrieve, filter, and sort data using simple queries. Now, we’re stepping into the real power of SQL: Joins.

A database is rarely just one table. In most cases, data is spread across multiple tables that are connected through relationships. Joins allow you to combine these tables and analyze data holistically.

Today, we will cover:
✅ Understanding how tables relate in the Chinook Database
✅ The four main types of SQL joins (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN)
✅ Real-world examples of each

Let’s dive in!

Understanding SQL Joins

Think of a database like a music streaming app:

• Artists release albums.

• Each album contains multiple tracks.

• Customers purchase those tracks.

These entities are stored in different tables, but they share relationships.

Example: Chinook Database Structure

The Chinook Database follows this structure:

• Artists (ArtistId, Name)

• Albums (AlbumId, Title, ArtistId)

• Tracks (TrackId, Name, AlbumId)

• Invoices (InvoiceId, CustomerId, Total)

Our goal is to connect these tables using SQL joins.

Understanding joins: Why do we join certain tables first?

When writing a JOIN query, the first table we pick is often the one we want to keep as the base. Every row from this table will appear in the result, as long as it has a match in the second table.

• The primary key of one table typically joins with the foreign key in another table.

• If we’re looking at albums, it makes sense to start from the Album table and join it with the Artist table on ArtistId.

• If we’re tracking sales, we start with Invoice because it contains purchase records, and then join it to details like tracks or customers.

Single join: Linking albums and artists

Question:
How can we list all albums alongside their artist names?

Try writing a query before looking at the answer.

SELECT Album.Title, Artist.Name 
FROM Album 
JOIN 
Artist ON Album.ArtistId = Artist.ArtistId;

How it works:

• We start with Album because we want to see all albums.

• We join Artist using ArtistId, the primary key in Artist and foreign key in Album.

• The result shows albums with their respective artist names.

Joining three tables: Finding who purchased which tracks

Question:
How can we find the first name, last name, and tracks purchased by each customer?

SELECT Customer.FirstName, Customer.LastName, Track.Name AS TrackPurchased  
FROM Customer  
JOIN Invoice ON Customer.CustomerId = Invoice.CustomerId  
JOIN InvoiceLine ON Invoice.InvoiceId = InvoiceLine.InvoiceId  
JOIN Track ON InvoiceLine.TrackId = Track.TrackId  
ORDER BY Customer.LastName;

Why this works:

1. Start with Customer because we want to list all customers.

2. Join Invoice to connect customers to their purchase records.

3. Join InvoiceLine to get the individual tracks purchased.

4. Join Track to display track names instead of just IDs.

This is a multi-table join—a common real-world SQL query pattern.

If you are new to SQL or need a refresher, check out “Lesson 2: SQL Basics” here:

Basic English simplifies SQL concepts - Lesson 2

Different types of joins

Now that we’ve covered basic joins, let’s explore the four main types of SQL joins.

Inner join: Returning only matching records

An INNER JOIN returns only the rows where there is a match in both tables.

Question:
How can we list only albums that have a matching artist?

SELECT Album.Title, Artist.Name 
FROM Album INNER 
JOIN 
Artist ON Album.ArtistId = Artist.ArtistId;

✔ If an artist has no album, they won’t appear in the result.

---

Left Join: Keeping all records from the left table

A LEFT JOIN keeps all rows from the first table and matches data from the second table. If there’s no match, it fills the missing values with NULL.

Question:
How can we list all artists, even if they don’t have an album?

SELECT Artist.Name, Album.Title 
FROM Artist LEFT 
JOIN 
Album ON Artist.ArtistId = Album.ArtistId;

✔ If an artist has no album, the album column will show NULL.

---

Right Join: Keeping all records from the right table

A RIGHT JOIN is the opposite of a LEFT JOIN. It keeps all rows from the second table and matches data from the first table.

Question:
How can we list all albums, even if their artist is missing?

SELECT Artist.Name, Album.Title 
FROM Artist RIGHT 
JOIN 
Album ON Artist.ArtistId = Album.ArtistId;

✔ If an album has no artist, the artist column will show NULL.

---

Full Outer Join: Including everything

A FULL OUTER JOIN includes all records from both tables. If a match exists, it appears. If not, NULL fills the gaps.

Question:
How can we see all artists and albums, even if there’s no direct match?

SELECT Artist.Name, Album.Title 
FROM Artist FULL OUTER 
JOIN 
Album ON Artist.ArtistId = Album.ArtistId;

✔ This ensures no data is left out, even if no relationship exists.

---

Practical applications of joins

SQL joins are used everywhere:
🔹 E-commerce: Joining orders with customer data to track purchases.
🔹 Healthcare: Connecting patients, doctors, and prescriptions.
🔹 Finance: Merging transaction logs with account details.

Try writing your own joins with different tables in the Chinook Database!

Next Steps

The next lesson will introduce SQL Aggregations, where we’ll summarize data using functions like COUNT, SUM, and AVG.

📢 For paid subscribers, stay tuned for a video breakdown of these joins!

If this lesson was helpful, please share and support Data Nomads Lab on Substack. Your support keeps this content coming.

Until next time—happy querying!

If this newsletter inspired you, share it with someone who is starting their data journey. Your support helps me keep writing and creating helpful content for aspiring data professionals. Let us grow together!