Inner Join vs Left Join vs Right Join vs Full Join

You might’ve heard terms like inner join, left join, or full outer join thrown around like confetti at an SQL data party.

And if you’re anything like me when I first heard them…

You nodded along while secretly wondering if these were moves from a ballroom dance class.

So let’s break them down — clearly, visually, and with as little jargon as possible!

We’ll focus on the Big Four: INNER, LEFT, RIGHT, and FULL joins.

By the end of this post, you'll know exactly which one to reach for and when.

And we’ll even sprinkle in the fancy joins for bonus street cred.

But first… What’s a Join?

Imagine two tables at a party.

Table A: A list of customers.

Table B: A list of orders.

Each table knows its stuff—but they’re socially awkward and don’t talk to each other unless you explicitly tell them to.

That’s what a join does: it introduces one table to another using a common key (usually an ID).

So, SQL joins let you combine rows from two (or more) tables based on a related column.

Sounds simple? Good. Let’s get messy.

Inner Join

If SQL joins were a Venn diagram, the INNER JOIN would be the part where the two circles overlap.

SELECT *
FROM customers
INNER JOIN orders
ON customers.id = orders.customer_id;

This says: “Hey database, give me everyone who exists in both the customers and orders table.” So, if someone placed an order, they show up. If they haven’t? Tough luck—they’re not invited to this query party.

It’s clean. It’s efficient. It’s what most people use by default because it filters out the data that doesn’t match on both sides.

Use it when:

✅ You only care about matches in both tables.

Left Join

Now, the LEFT JOIN is like a teacher who wants to make sure every student in their class gets called on—even if some don’t have answers.

SELECT *
FROM customers
LEFT JOIN orders
ON customers.id = orders.customer_id;

This says: “Give me all customers, and show their orders if they have any.” If a customer exists but hasn’t placed an order, they’ll still be in the results—with NULL in the columns from the orders table.

Use it when:

✅ You care about everyone in the left table (usually your main dataset).

✅ You want to see who’s missing data on the other side.

Right Join

RIGHT JOIN is the LEFT JOIN’s mirror twin. It’s clingy to the right table instead.

SELECT *
FROM customers
RIGHT JOIN orders
ON customers.id = orders.customer_id;

This says: “Give me all orders, and show the customer info if it exists.” This can be helpful in rare cases where the right table is the primary one you care about.

Use it when:

✅ You care more about the second table than the first.

Honestly? You can do everything RIGHT JOIN does with a LEFT JOIN if you just flip the table order. So use it if you must—but only if you enjoy chaos.

Full Join

The FULL OUTER JOIN is the SQL equivalent of Oprah giving out participation medals.

SELECT *
FROM customers
FULL OUTER JOIN orders
ON customers.id = orders.customer_id;

This says: “Give me all customers AND all orders—even if there’s no match between them.”

It includes:

• Customers with orders ✅
• Orders with customers ✅
• Customers without orders ✅
• Orders without customers ✅

The unmatched rows will have NULL values on whichever side didn’t find a match.

Use it when:

✅ You don’t want to miss anything.

✅ You’re doing audits, reconciliations, or just being overly inclusive.

Here’s how it looks in diagram brain terms:

• INNER JOIN = Middle only
• LEFT JOIN = Left + middle
• RIGHT JOIN = Right + middle
• FULL OUTER JOIN = Everything

And if you ever forget which is which, imagine two overlapping pizza slices and ask: “How much pizza do I want?” The answer determines the join, I guess…

Now, if you’ve mastered the basics and want to look smarter at your SQL database parties, here are a few fancier Joins to casually drop into conversation:

The Self Join

When a table joins itself—great for hierarchical data like org charts.

SELECT a.name, b.name AS manager
FROM employees a
JOIN employees b
ON a.manager_id = b.id;

The Cross Join

Every row from one table joins with every row from the other. Usually results in a Cartesian explosion. This is great for permutations, terrible for performance.

SELECT *
FROM sizes
CROSS JOIN colors;

The Natural Join

SQL tries to guess the join condition for you. Which sounds nice... until it guesses wrong!

And always test your joins with real data to make sure you're not accidentally excluding or duplicating anything.