How to Learn SQL Joins (and Stop Mixing Up LEFT and INNER)

For about two years I wrote SQL joins by trial and error. I'd type JOIN, run it, count the rows, feel uneasy, swap in LEFT JOIN, count again, and keep whichever number looked less wrong. It worked often enough that I never sat down and learned the thing properly.

Then a report I'd written quietly dropped every customer who hadn't placed an order. Nobody noticed for a month. That was the week I finally figured out joins, and the approach that stuck is pretty different from the Venn diagrams most tutorials open with.

Forget the Venn diagram for a minute

The circles are fine as a rough picture, but they hide the part that actually trips people up: joins don't combine sets of values, they match rows. One customer with three orders becomes three rows. A customer with zero orders becomes either zero rows or one row full of NULLs, depending on the join type.

So the question I ask now is always the same. For each row on the left, how many rows on the right match it, and what happens when the answer is zero?

That one question covers almost everything:

• INNER JOIN keeps only rows with at least one match. Zero matches means the row disappears.
• LEFT JOIN keeps every left row. Zero matches means you get one row with NULLs on the right side.
• RIGHT JOIN is the same thing mirrored. I almost never use it; I flip the table order and write a LEFT JOIN so I'm always reading in one direction.
• FULL OUTER JOIN keeps unmatched rows from both sides.
• CROSS JOIN matches every row with every row, which is what you get by accident when you forget the join condition in old comma-style syntax.

Build two tiny tables and break them

Reading about joins taught me very little. Running them on data small enough to check by eye taught me a lot. Open SQLite, DuckDB, or the free DB Fiddle and make this:

CREATE TABLE customers (id INT, name TEXT);
CREATE TABLE orders (id INT, customer_id INT, total INT);

INSERT INTO customers VALUES (1, 'Asha'), (2, 'Ben'), (3, 'Chen');
INSERT INTO orders VALUES (10, 1, 50), (11, 1, 20), (12, 2, 35), (13, 9, 99);

Asha has two orders, Ben has one, Chen has none, and order 13 points at a customer who doesn't exist. Before you run each query, write down how many rows you expect. Then run it.

SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;   -- 3 rows, no Chen

SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;    -- 4 rows, Chen with NULL

Guessing first is the important bit. When your guess is wrong, you've found the exact gap in your understanding, and that sticks far better than a correct answer you read off a page.

The two bugs worth memorizing

Most join bugs I've shipped or reviewed come down to two mistakes.

The first is filtering a LEFT JOIN in the WHERE clause:

SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.total > 30;

That looks like "all customers, plus their orders over 30." It isn't. Chen's row has o.total = NULL, NULL > 30 isn't true, so Chen gets filtered out and you've rebuilt an inner join without meaning to. If you want to keep every customer, move the condition into the ON:

LEFT JOIN orders o ON o.customer_id = c.id AND o.total > 30

The second is row multiplication. Join customers to orders, then join to a table of support tickets, and a customer with 3 orders and 4 tickets turns into 12 rows. Run SUM(o.total) over that and the number is quietly four times too big. The fix is usually to aggregate each side in a subquery or CTE first and join the summaries.

Turn every "wait, why?" into a flashcard

Here's the habit that made it stick for me. Any time a query surprised me, I wrote a card with the situation on the front and the reason on the back.

I LEFT JOINed and then filtered on the right table in WHERE. Why did rows vanish?
NULL fails the comparison, so unmatched rows are dropped. Put the filter in ON.

My SUM doubled after adding a second join. Why?
One-to-many on two sides multiplies rows. Aggregate before joining.

How do I find customers with no orders?
LEFT JOIN orders, then WHERE o.id IS NULL.

I review them on a growing schedule: the next day, a few days later, a week, two weeks. That's spaced repetition, and it's the reason I can still write an anti-join from memory instead of searching for it. If you're curious how the intervals get calculated, I wrote up how to implement spaced repetition (SM-2) in JavaScript.

Keep the cards about situations you'll actually hit at work. "What is a semi-join?" is trivia. "How do I check if a customer has any order without duplicating rows?" (answer: EXISTS) is something you'll need on a Tuesday.

Don't lose the good explanations

Plenty of what finally made joins click came from videos and blog posts, and I used to watch one, nod, and forget it by the weekend. That's part of why I built Recl. You share a video, reel, podcast or article to it from your phone or the Chrome extension, and it summarizes the key points, files it under a topic like "SQL", and brings it back on a spaced schedule so you see it again before it fades.

Read execution plans once you're comfortable

When the row counts make sense, run EXPLAIN (or EXPLAIN ANALYZE in Postgres) on a join and look at whether it picked a hash join, a nested loop, or a merge join. You don't need to tune anything yet. Seeing that the database is literally looping over rows and looking up matches makes the "how many matches per row" model feel real.

I still check row counts on every non-trivial query. The difference is that now I know what number to expect before I hit run, and that report from a few years ago would have been caught in about ten seconds.

---

I build Recl, an AI learning app that saves your videos, reels, podcasts and articles, summarizes them, sorts them into topics, and uses spaced repetition so you remember what you learned.