Hey you all! 👋🏻
It’s Josep here, bringing you another week of data-packed knowledge!
This time, we’re tackling one of the trickiest yet most fundamental concepts in SQL: Joins.
Whether you're a SQL newbie or a seasoned pro, there’s always room to sharpen your understanding of how tables connect.
Stick around for 7 minutes—I promise it’ll be worth your time! 🚀
The most important news of the week?
I’ve been on the road for work these past two weeks, so there’s not much to report just yet.
But stay tuned—some major announcements are on the way! 💥
Now that we’ve caught up, let's dive into the important stuff 👨🏻💻.
Before we dive into today’s topic, I highly recommend checking out my earlier SQL-focused issues:
Today, let's understand how to merge tables with SQL 💥
And this leads us to today’s main focus…
SQL JOINs
But before starting, I want to share the Cheatsheet of the week 👇🏻
The Magic of SQL Joins
In the world of databases, data often resides in separate tables. SQL Joins allow you to combine these tables in meaningful ways, giving you the power to extract insights that would otherwise remain buried.
But let’s face it—Joins can be confusing.
Which one do you use? What happens to rows that don’t match?
This week, we break it all down with the SQL Joins Cheatsheet to clarify these concepts. To do so, we will use two dummy tables as example:
#1. Left Join
What it does: Returns all records from table A and the matching records from table B. Rows in A without matches in B will still appear, with NULL values for B.
Use case: When you want all data from the left table, regardless of matches in the right table.
SELECT *
FROM A
LEFT JOIN B
ON A.KEY = B.KEY
Query Example:
SELECT
A.A_KEY,
A.A_NAME,
A.CITY,
B.B_KEY,
B.DEPARTMENT,
B.EMPLOYMENT_STATUS
FROM TableA A
LEFT JOIN TableB B
ON A.A_KEY = B.B_KEY;
What it does:
Returns all rows from TableA, along with the matching rows from TableB. Rows in A that have no matching B_KEY will have NULL for columns from B.
#2. Right Join
What it does: Similar to Left Join but focuses on the right table (B). All rows from B are included, and unmatched rows from A appear as NULL.
Use case: When your primary focus is on the right table.
SELECT *
FROM A
RIGHT JOIN B
ON A.KEY = B.KEY
Query Example:
SELECT
A.A_KEY,
A.A_NAME,
A.CITY,
B.B_KEY,
B.DEPARTMENT,
B.EMPLOYMENT_STATUS
FROM TableA A
RIGHT JOIN TableB B
ON A.A_KEY = B.B_KEY;
What it does:
Returns all rows from TableB, plus the matching rows from TableA. Rows in B with no matching A_KEY will have NULL for columns from A.
We can replicate the same JOINs as before, but this time without overlapping!
#3. Left Join (Without Overlapping)
What it does: Retrieves rows from table A that don’t have a match in table B.
Use case: Finding records in A that are missing corresponding data in B.
SELECT *
FROM A
LEFT JOIN B
ON A.KEY = B.KEY
WHERE B.KEY IS NULL
Query Example
SELECT
A.A_KEY,
A.A_NAME,
A.CITY,
B.B_KEY,
B.DEPARTMENT,
B.EMPLOYMENT_STATUS
FROM TableA A
LEFT JOIN TableB B
ON A.A_KEY = B.B_KEY
WHERE B.B_KEY IS NULL;
What it does:
Fetches only the rows from TableA that do not have a match in TableB.
#4. Right Join (Without Overlapping)
What it does: The reverse of the above—returns rows in table B without matches in table A.
Use case: Finding records in B that don’t correspond to A.
SELECT *
FROM A
RIGHT JOIN B
ON A.KEY = B.KEY
WHERE A.KEY IS NULL
Query Example:
SELECT
A.A_KEY,
A.A_NAME,
A.CITY,
B.B_KEY,
B.DEPARTMENT,
B.EMPLOYMENT_STATUS
FROM TableA A
RIGHT JOIN TableB B
ON A.A_KEY = B.B_KEY
WHERE A.A_KEY IS NULL;
What it does:
Fetches only the rows from TableB that do not have a match in TableA.
#5. Full Join
What it does: Combines all records from both tables. Rows with no match in either table appear with NULLs.
Use case: When you need a complete overview of data in both tables.
SELECT *
FROM A
FULL OUTER JOIN B
ON A.KEY = B.KEY
Query Example:
SELECT
A.A_KEY,
A.A_NAME,
A.CITY,
B.B_KEY,
B.DEPARTMENT,
B.EMPLOYMENT_STATUS
FROM TableA A
FULL OUTER JOIN TableB B
ON A.A_KEY = B.B_KEY;
What it does:
Returns all rows from both TableA and TableB. Rows without matches in the other table will show NULL in those table’s columns.
#6. Inner Join
What it does: Returns only the rows with matching keys in both tables.
Use case: Perfect for extracting overlapping data.
SELECT *
FROM A
INNER JOIN B
ON A.KEY = B.KEY
Query Example:
SELECT
A.A_KEY,
A.A_NAME,
A.CITY,
B.B_KEY,
B.DEPARTMENT,
B.EMPLOYMENT_STATUS
FROM TableA A
INNER JOIN TableB B
ON A.A_KEY = B.B_KEY;
What it does:
Returns only rows where TableA and TableB share the same key. Rows that don’t overlap on A_KEY = B_KEY are excluded entirely.
#7. Full Join (Without Overlapping)
What it does: Shows all records from both tables, excluding matches. Think of it as highlighting the differences between the two tables.
Use case: Identifying discrepancies between datasets.
SELECT *
FROM A
FULL OUTER JOIN B
ON A.KEY = B.KEY
WHERE A.KEY IS NULL OR B.KEY IS NULL
Query Example:
SELECT
A.A_KEY,
A.A_NAME,
A.CITY,
B.B_KEY,
B.DEPARTMENT,
B.EMPLOYMENT_STATUS
FROM TableA A
FULL OUTER JOIN TableB B
ON A.A_KEY = B.B_KEY
WHERE A.A_KEY IS NULL
OR B.B_KEY IS NULL;
What it does:
Returns only rows that do not match (show the differences) across TableA and TableB.
How to Read the Results 🤔
Left Join: Everything from A plus matches from B. Unmatched rows in A still appear.
Right Join: Everything from B plus matches from A. Unmatched rows in B still appear.
Left/Right Join (Without Overlapping): Only the unmatched rows from A or B, respectively.
Full Join: All rows from both tables, showing NULLs where no match is found.
Inner Join: Only overlapping rows (keys present in both tables).
Full Join (Without Overlapping): All rows that don’t overlap—so we can check the discrepancies between the tables.
Still with me? 🧐
As fellow data enthusiast, I’m sure you’d be eager to help me shape some impactful KPIs and take this newsletter to the next level!
So here’s how you can help:
👉🏻 I want this newsletter to be truly valuable for you, so please share your feedback!
Additionally, you can let me know any preference for future content or any idea you think might add more value to you!
Before you go, tap the 💚 button at the bottom of this email to show your support—it really helps and means a lot!
Remember DataBites is a newsletter fully funded by its users, so if you like my content, consider upgrading to a paid plan.
My latest articles 📝
Unlocking the Power of Geospatial Data 🌍 in DataBites.
A Practical Guide to the Claude API in MLMastery.
6 Language Model Concepts Explained for Beginners in MLMastery.
5 Free APIs for Building AI Applications in MLMastery.
My Favorite Resources! 👨🏻💻
I strongly recommend following AIgents roadmaps if you want to become a full Data Scientist, ML Engineer, or Data Engineer,
You can follow the Data Engineer Zoomcap on GitHub to become a fully proficient Data Engineer.
Want to get more of my content? 🙋🏻♂️
Reach me on:
LinkedIn, X (Twitter), or Threads to get daily posts about Data Science.
My Medium Blog to learn more about Data Science, Machine Learning, and AI.
Just email me at rfeers@gmail.com for any inquiries or to ask for help! 🤓
Remember now that DataBites has an official X (Twitter) account and LinkedIn page. Follow us there to stay updated and help spread the word! 🙌🏻