DEV Community

Stackcraft Studio
Stackcraft Studio

Posted on

SQL JOINs finally made sense when I stopped memorizing them

I spent an embarrassing amount of my first year writing SQL by vibes. JOIN especially. INNER JOIN? LEFT JOIN? I had memorized the Venn diagrams like they were multiplication tables, and I still froze every time a query needed more than one table.

What fixed it was stopping memorization entirely and thinking about one question instead: which table keeps all its rows?

The only question that matters

Every JOIN is just: match rows from table A with rows from table B on some key. The join type answers one thing - who survives when there is no match?

  • INNER JOIN - nobody survives without a match. Only pairs that exist in both.
  • LEFT JOIN - everyone from the LEFT table survives. Matches from the right come along, gaps become NULL.
  • RIGHT JOIN - same thing, mirror image. (I have never once needed this. Just swap the table order and write LEFT.)
  • FULL OUTER JOIN - everyone survives, NULLs on both sides where there is no match.

That's it. That's the whole mental model.

The example that made it stick

Two tiny tables:

users
id | name
1  | Ana
2  | Dev
3  | Kim

orders
id | user_id | total
1  | 1       | 40
2  | 1       | 15
3  | 3       | 99
Enter fullscreen mode Exit fullscreen mode

INNER JOIN gives you Ana's two orders and Kim's one. Dev vanishes - no orders, no match, no row.

LEFT JOIN users -> orders keeps Dev, with NULL for total. This is the one you want for "show me ALL users and their orders if any".

The trap that got me repeatedly: putting a WHERE on the right table after a LEFT JOIN silently turns it back into an INNER JOIN.

-- Dev disappears here, even with LEFT JOIN
SELECT * FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.total > 10;
Enter fullscreen mode Exit fullscreen mode

Dev's o.total is NULL, and NULL fails every comparison. If you want the filter without losing rows, move it into the ON clause:

SELECT * FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.total > 10;
Enter fullscreen mode Exit fullscreen mode

Why I stopped using RIGHT JOIN

Every RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the tables, and reading A LEFT JOIN B top to bottom is just easier on the brain. Consistency beats variety here.

Practice that actually works

Take any two tables you own and ask out loud: "which side keeps all its rows?" Say the answer before you type. After about ten queries it becomes automatic, and the Venn diagrams can finally retire.

What concept took you embarrassingly long to click? Mine was this.

Top comments (0)