#7 JOINs (Left, Right, Inner & Full)

SQL Crash Course #7

All the course material is stored in the SQL Crash Course repository.

Hi everyone! Josep and Cornellius Yudha Wijaya from Non-Brand Data here 👋🏻

As promised, today we are publishing the next two issues of our SQL Crash Course – From Zero to Hero! 🚀

I am sure you are here to continue our SQL Crash Course Journey!📚

If this is your first time or you’ve forgotten what we’ll cover, we will examine seven key SQL topics, each divided into multiple posts across Non-Brand Data and DataBites.

📚 Previously in SQL Basics…

Remember last Thursday we already saw

📌 #5 Filtering and Sorting by me in DataBites
📌 #6 Aggregate Functions by Cornellius Yudha Wijaya in Non-Brand Data.

Today, two fresh issues just dropped:

📌 #7 – JOINS (INNER, LEFT, RIGHT, FULL) – the one you're reading right now, all about combining tables to unlock richer insights
📌 #8 – UNION & UNION ALL – where Cornellius explains how to stack query results efficiently

This marks the beginning of a four-part Intermediate SQL journey:

1. JOINS (INNER, LEFT, RIGHT, FULL)

2. UNION & UNION ALL

3. CASE Expressions

4. Functions (String, Date, Numeric)

So don’t miss out—let’s keep the SQL momentum going!

🔗 SQL JOINS – Unlocking Relationships Between Tables

In relational databases, data often lives across multiple tables. JOINS are the bridge that connects these tables, allowing us to extract insights that would otherwise remain hidden.

But let’s be honest — JOINS can be confusing at first.

Which one should you use? What happens to unmatched rows?

No worries — we’ve got your back. We’ll break it down using:

• Two generic tables (TableA, TableB) for structure

• Real examples using three concrete tables: POSTS, INTERACTIONS, and NEWSLETTERS

There are seven main types of JOIN we can perform using SQL.

Now let’s break them down 👇🏻

✅ LEFT JOIN

🔹 What it does:
Returns all records from TableA (left table) and the matching records from TableB (right table). If no match exists, you still get the row from A with NULL values from B.

🔹 Use case:
List all posts — even those that received no interaction.

Structure:

SELECT *  
FROM A  
LEFT JOIN B  
ON A.KEY = B.KEY  

Real Example:

SELECT 
    P.name AS post_name, 
    I.type_of_interaction 
FROM POSTS P 
LEFT JOIN INTERACTIONS I 
  ON P.id = I.post_id;

🧠 Posts with no interactions will still appear — with NULLs for the interaction fields.

---

✅ RIGHT JOIN

🔹 What it does:
Returns all records from TableB, and any matching rows from TableA. If there’s no match, A’s fields will be NULL.

🔹 Use case:
Show all interactions, even if the referenced post is missing.

Structure:

SELECT *  
FROM A  
RIGHT JOIN B  
   ON A.KEY = B.KEY  

Example with our newsletter’s data:

SELECT 
    I.id AS interaction_id,
    P.name AS post_name,
    I.type_of_interaction
FROM POSTS P
RIGHT JOIN INTERACTIONS I
    ON P.id = I.post_id;

🧠 Helpful for identifying orphaned interactions — though in this dataset, all are valid.

---

✅ LEFT JOIN (Without Overlapping)

🔹 What it does:
Returns rows from A that don’t have a match in B.

🔹 Use case:
Find posts that have received no interactions.

Structure:

SELECT *  
FROM A  
LEFT JOIN B  
  ON A.KEY = B.KEY  
WHERE B.KEY IS NULL  

Example with our newsletter’s data:

SELECT 
    P.name
FROM POSTS P
LEFT JOIN INTERACTIONS I
    ON P.id = I.post_id
WHERE I.id IS NULL;

🧠 Great to identify content that might need promotion or a second look.

---

✅ RIGHT JOIN (Without Overlapping)

🔹 What it does:
Returns rows from B that don’t match anything in A.

🔹 Use case:
Spot inconsistent data — e.g., interactions without matching posts.

Structure:

SELECT *  
FROM A  
RIGHT JOIN B  
  ON A.KEY = B.KEY  
WHERE A.KEY IS NULL  

Example with our newsletter’s data:

SELECT 
    I.id,
    I.post_id
FROM POSTS P
RIGHT JOIN INTERACTIONS I
    ON P.id = I.post_id
WHERE P.id IS NULL;

🧠 Useful for quality assurance — even if the current data has no such issues.

---

✅ FULL OUTER JOIN (Simulated via UNION)

🔹 What it does:
Returns all records from both tables, matching where possible. Rows with no match in either table will have NULLs on one side.

🔹 Use case:
Get a complete overview of all posts and interactions.

Structure:

SELECT *  
FROM A  
FULL OUTER JOIN B  
  ON A.KEY = B.KEY  

Query Example: Combines all rows from both tables — matching where possible, keeping everything else.

SELECT 
    P.name AS post_name,
    I.type_of_interaction
FROM POSTS P
OUTER JOIN INTERACTIONS I
    ON P.id = I.post_id

🧠 You’ll get everything — connected or not.

🔹 Important for MySQL users:
❗ MySQL does not support FULL OUTER JOIN directly. To achieve this behavior, you'll need to combine a LEFT JOIN and a RIGHT JOIN using UNION.

-- Simulated FULL OUTER JOIN in MySQL using UNION
SELECT 
    P.name AS post_name,
    I.type_of_interaction
FROM posts P
LEFT JOIN interactions I
    ON P.id = I.post_id

UNION

SELECT 
    P.name AS post_name,
    I.type_of_interaction
FROM posts P
RIGHT JOIN interactions I
    ON P.id = I.post_id;

---

✅ FULL JOIN (Without Overlapping)

🔹 What it does:
Shows only the non-matching records from both tables — highlighting differences.

🔹 Use case:
Identify discrepancies between two datasets.

Structure:

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,
    B.B_KEY,
    B.DEPARTMENT
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;

🧠 This query isolates what’s not connected — a powerful tool for data validation.

❗ FULL OUTER JOIN is not available in MySQL, so you'll need to simulate this logic using a UNION between a LEFT JOIN ... WHERE IS NULL and a RIGHT JOIN ... WHERE IS NULL.

-- Simulated FULL OUTER JOIN (no match only) in MySQL using UNION
SELECT 
    P.name AS post_name,
    I.type_of_interaction
FROM posts P
LEFT JOIN interactions I
    ON P.id = I.post_id
WHERE I.post_id IS NULL

UNION

SELECT 
    P.name AS post_name,
    I.type_of_interaction
FROM posts P
RIGHT JOIN interactions I
    ON P.id = I.post_id
WHERE P.id IS NULL;

---

✅ INNER JOIN

🔹 What it does:
Returns only matching rows from both tables.

🔹 Use case:
See which posts have at least one interaction.

Structure:

SELECT *  
FROM A  
INNER JOIN B  
  ON A.KEY = B.KEY  

Real Example:

SELECT 
    P.name AS post_name, 
    I.type_of_interaction 
FROM POSTS P 
INNER JOIN INTERACTIONS I 
   ON P.id = I.post_id;

🧠 Great when you’re only interested in records that exist in both tables.

---

💡 BONUS: Joining 3 Tables

🔹 What it does:
Connects NEWSLETTERS, POSTS, and INTERACTIONS for full context.

🔹 Use case:
List the newsletter name, post title, and interaction type — all in one view.

SELECT 
    N.name AS newsletter_name,
    P.name AS post_title,
    I.type_of_interaction
FROM NEWSLETTERS N
JOIN POSTS P
    ON N.id = P.newsletter_id
JOIN INTERACTIONS I
    ON P.id = I.post_id;

🧠 This is how you create powerful multi-table reports and dashboards.

---

🧠 Summary Table – How to Read JOIN Results

• LEFT JOIN → All from left table + matches from right

• RIGHT JOIN → All from right table + matches from left

• LEFT JOIN (no match) → Rows from left with no match in right

• RIGHT JOIN (no match) → Rows from right with no match in left

• FULL OUTER JOIN (via UNION) → All records from both tables, matched or not

• FULL JOIN (no match) → Only non-matching records from both tables

• INNER JOIN → Only matching records from both tables

And now? It is your turn to play in our SQL playground with these two clauses.

👉🏻 SQL playground with all the different types of JOINs

How to Get Started 🚀

Over the coming weeks, we’ll guide you through:

✅ SQL Fundamentals
✅ Intermediate SQL
✅ Advanced SQL
✅ Database Operations
✅ Writing Efficient Queries

Once you grasp the basics, practice is key! Start working on real-world projects and solving hands-on data problems.

What’s Next? ➡️

This is the first of many posts about the upcoming SQL Courses. It will only explain what SQL is in its crude form.

To get the full experience and fully immersed in the learning:

👉 Subscribe to Databites.tech (By Josep)

👉 Subscribe to Non-Brand Data (By Cornellius)

👉 Check out the SQL Crash Course GitHub repo

👉 Share with your friend and whoever needs it!

🗓️ Every Thursday, you will have two new issues in your inbox!

Let’s dive in and make SQL less scary, more fun, and way more useful! 🚀

Josep & Cornellius