SQL JOINs Explained: The Two Questions That Settle All Four Types

You've written a query that pulls every customer in your database. It runs fine. Then someone asks a simple-sounding question: which customers placed an order last month, and what did they spend? Suddenly one table isn't enough. The names live in one place, the orders live in another, and you need to stitch them together. That stitching is what a JOIN does — and it's the single skill that separates people who can store data from people who can actually answer questions with it.

The trouble is that JOINs are usually taught as four cryptic keywords — INNER, LEFT, RIGHT, FULL — dropped on you all at once, as if the differences were obvious. They aren't. But underneath the jargon there are really only two decisions you ever make, and once you can name them, every JOIN you'll ever write falls into place.

The one idea behind every JOIN

A JOIN takes two tables and lines up their rows based on a rule you give it — usually "this column here equals that column there." Picture two spreadsheets side by side. In the left one, each customer has an id. In the right one, each order has a customer_id pointing back at a customer. A JOIN walks through the rows and matches them up wherever those values agree.

Let's make that concrete with two small tables. Here's customers:

SELECT * FROM customers;

 id | name
----+---------
  1 | Ada
  2 | Grace
  3 | Katherine

And here's orders (notice Katherine has no order, and there's an order with a customer_id of 9 that points at nobody):

SELECT * FROM orders;

 id | customer_id | amount
----+-------------+--------
 10 |           1 |     50
 11 |           1 |     20
 12 |           2 |     99
 13 |           9 |     15

Every JOIN example below uses exactly these two tables. Keep them in mind — the whole point is to see how the same data produces different results depending on which JOIN you pick.

INNER JOIN: only the matches

An INNER JOIN is the strict one. It returns a row only when there's a match on both sides. No match, no row — from either table.

SELECT c.name, o.amount
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

 name  | amount
-------+--------
 Ada   |     50
 Ada   |     20
 Grace |     99

Look at what disappeared. Katherine is gone because she never placed an order. The order with customer_id = 9 is gone because no customer has that id. INNER JOIN quietly drops anything that doesn't line up on both sides, which is exactly what you want when the question is "show me customers and their orders." It's the most common JOIN by far, and if you only ever learned one, this would be it.

The rule of thumb: reach for INNER JOIN when a row is only meaningful if it exists in both tables.

One thing beginners trip on: notice Ada appears twice. She has two orders, so the join produces two rows. A JOIN doesn't magically collapse duplicates — it produces one row per matching pair. If you want one row per customer with a total, that's a job for GROUP BY, which we'll get to.

LEFT JOIN: keep everyone on the left

Now the second question: which customers have placed no orders at all? An INNER JOIN can never answer that, because it threw Katherine away. This is where LEFT JOIN earns its keep. It returns every row from the left table, and fills in the right table's columns where a match exists — or NULL where it doesn't.

SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

 name      | amount
-----------+--------
 Ada       |     50
 Ada       |     20
 Grace     |     99
 Katherine |   NULL

Katherine is back, with a NULL where her order amount would be. That NULL is the signal. It's not a bug — it's the database telling you "this customer exists, but there was nothing to match on the right." To find non-buyers, you simply filter for it:

SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.customer_id IS NULL;

 name
-----------
 Katherine

This "LEFT JOIN then check for NULL" pattern is one of the most useful in all of SQL. It answers a whole family of questions: customers who never bought, articles with no comments, employees with no assigned project. Any time you need "things that exist here but have nothing over there," this is the shape.

RIGHT and FULL: the same idea, mirrored

A RIGHT JOIN is just a LEFT JOIN looking in the mirror — it keeps every row from the right table instead of the left. In practice almost nobody writes RIGHT JOINs, because you can always swap the table order and use a LEFT JOIN, which reads more naturally. Mention it in an interview, then happily never use it.

A FULL OUTER JOIN keeps everything from both sides, matching where it can and padding with NULL where it can't. With our tables it surfaces both the lonely customer and the orphaned order at once:

SELECT c.name, o.id AS order_id, o.customer_id
FROM customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id;

 name      | order_id | customer_id
-----------+----------+-------------
 Ada       |    10    |      1
 Ada       |    11    |      1
 Grace     |    12    |      2
 Katherine |   NULL   |    NULL
 NULL      |    13    |      9

The bottom two rows are the interesting ones: Katherine (a customer with no order) and order 13 (an order pointing at a customer id that doesn't exist). FULL OUTER JOIN is the go-to when you're reconciling two datasets and need to see mismatches on either side — say, comparing what's in your system against an export from someone else's. Note that MySQL historically doesn't support FULL OUTER JOIN directly; you emulate it by UNION-ing a LEFT and a RIGHT join. PostgreSQL, SQL Server, and others support it natively.

The two questions that settle every JOIN

Here's the mental model promised at the top. Whenever you're about to write a JOIN, ask yourself two things:

1. Which table's rows do I want to keep, even when there's no match? If the answer is "only rows that match both sides," you want INNER. If it's "keep everything from my main table regardless," you want LEFT (put the main table on the left). If it's "keep everything from both," you want FULL.

2. What am I matching on? This is your ON condition. Nine times out of ten it's a key relationship — parent.id = child.parent_id. Get this wrong and you'll either get zero rows or a runaway explosion of them.

That's genuinely it. The keywords stop being a memorization exercise once you realize they're just answers to question one. Here's a compact reference you can keep nearby:

Putting it to work: from rows to answers

Let's return to the question we opened with — which customers spent what last month? — because it combines a JOIN with the piece people forget. A raw JOIN gives you one row per order. To get one row per customer with a total, you aggregate:

SELECT c.name, COUNT(o.id) AS order_count, COALESCE(SUM(o.amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.name
ORDER BY total_spent DESC;

 name      | order_count | total_spent
-----------+-------------+-------------
 Grace     |      1      |      99
 Ada       |      2      |      70
 Katherine |      0      |       0

Three details worth pausing on. I used a LEFT JOIN so Katherine still appears, with zero orders — an INNER JOIN would have hidden your non-buyers, which is often the exact segment you care about. COUNT(o.id) counts orders, so it correctly reads 0 for Katherine (counting a column ignores NULLs), whereas COUNT(*) would have wrongly said 1. And COALESCE(SUM(...), 0) converts her NULL total into a clean 0, because summing nothing yields NULL, not zero. Small choices, but they're the difference between a report that's right and one that quietly lies.

If your JOINs feel slow as tables grow, the usual fix is an index on the columns in your ON clause — here, orders.customer_id. Foreign key columns are matched on constantly, and an index turns a full-table scan into a quick lookup. It's the highest-leverage performance tweak most people never make.

Wrapping up

JOINs look intimidating because they're taught as four disconnected keywords, but they collapse into two questions: which unmatched rows do I keep, and what do I match on. INNER keeps only the matches and covers most day-to-day work. LEFT keeps your main table intact and, paired with an IS NULL filter, answers "what's missing." RIGHT is LEFT in a mirror, and FULL keeps both sides for when you're reconciling data.

Open a database, build those two tiny tables, and run each query yourself — watching Katherine appear and disappear as you switch keywords will teach you more in ten minutes than any diagram. Once the model clicks, you stop guessing and start asking your data real questions. That's the whole game.