Merging tables is at the heart of SQL, yet understanding how different JOINs work can be tricky.
Whether you're dealing with LEFT, RIGHT, INNER, or FULL JOINs, knowing how they interact with your data is crucial for extracting meaningful insights.
And this leads us to today’s main focus…
SQL JOINs
Before starting, here you have the full-resolution cheatsheet 👇🏻
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.
Before you go, tap the 💚 button at the bottom of this email to show your support—it really helps and means a lot!
Any doubt? Let’s start a conversation! 👇🏻
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! 🙌🏻