Ever wondered how to get two separate tables in your data to talk to each other? Or maybe you're prepping your dataset and thinking, “Why doesn’t this table have what I need?”
Well, let me introduce you to Joins!
What’s a Join?
A join is basically the act of combining two tables based on a shared field (like ID, email, or product name). Think of it as being a matchmaker, pairing up rows from one table (the Left Table) with rows from another (the Right Table) based on something they both have in common.
But not all joins are the same so let's break them down and see which one is best for what you want to achieve.
1. Inner Join (Most Common)
This is the most common type of join. It keeps only the rows that have matching values in both tables.
How it works:
Imagine you have:
- Table A: A list of customers
- Table B: A list of orders
If you inner join them on Customer ID, you’ll only see customers who actually made an order. Everyone else gets filtered out.
Example:
You have a list of users and a list of purchases. Use an inner join to see who made a purchase.
2. Left (Outer) Join
This keeps all the rows from the left table and adds in data from the right table if there’s a match. If not? Then it just fills the gaps with nulls.
How it works:
Using the same customer/order example:
If the left table is your customer list, and you left join it with orders, you’ll still see every customer, whether they’ve ordered or not.
Example:
You have a list of all users and want to see if any of them made a purchase. But even if they didn’t, you still want them on your report.
3. Right (Outer) Join
This is the mirror image of the left join. It keeps all the rows from the right table matching in the left where possible.
How it works:
Say your orders are in the right table, and customers are in the left. A right join would show you every order, even if you can’t link it to a customer. Maybe they checked out as a guest.
Example:
You’re analysing purchase data and want to see if those customers exist in your user list. If they don’t, you still want to see them.
4. Full (Outer)
This one’s the all-inclusive party. It keeps everything from both tables whether there’s a match or not. Where no match exists, you’ll get nulls.
How it works:
It combines both tables and shows everything. Matches and non-matches. If there’s no match, it just leaves blank spaces.
Example:
You want to see all users and all purchases, even if some users didn’t buy or some purchases weren’t linked to a known user.
Joining it all up!
Joins are a powerful part of your data toolbox and knowing when to use each type is key to building clean, insightful datasets.
So next time your tables are sitting awkwardly across the room from each other, you know exactly how to match them up.
Happy joining 😊