Understanding SQL Joins Once and for All

A join combines rows from two tables based on a condition. The different join types — INNER, LEFT, RIGHT, FULL — control what happens when a row on one side has no matching row on the other. That's the only real difference between them. Everything else follows from that rule applied consistently.

To make this concrete, the examples below use two tables: users with three rows (Alice, Bob, Charlie) and orders where Alice has two orders, Bob has one, Charlie has none, and one order references a user_id that doesn't exist in the users table.

An INNER JOIN returns a row only when both sides have a match. If Alice has two orders, you get two rows — one per order. If Charlie has no orders, he doesn't appear at all. If an order has a user_id that doesn't exist in the users table, that order also disappears.

SELECT u.name, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

This is the join to reach for when you only care about records where both sides have data — reporting on activity, for example, where users who haven't done anything yet aren't relevant.

A LEFT JOIN returns all rows from the left table regardless of whether there's a match on the right. Where there's no match, the right-side columns come back as NULL. Charlie appears in the results with a NULL amount because he's in the users table but has no orders.

SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

This is the join most people need most often. It's how you build lists of "all users and their orders" where users without orders still show up. It's also how you find users without orders at all — filter for rows where the right side came back NULL.

SELECT u.name FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

A RIGHT JOIN is the mirror of LEFT JOIN — it keeps all rows from the right table and NULLs out the left side where there's no match. In practice it's rarely used directly, because you can always rewrite it as a LEFT JOIN by swapping the table order. Most teams pick LEFT JOIN as the standard and just swap which table sits in the FROM clause to control which side is preserved.

A FULL JOIN returns all rows from both tables. Where there's a match, you get the combined row. Where there's no match on either side, you get the row with NULLs filling in the missing columns. Charlie appears with NULL amount. The orphaned order appears with NULL name.

SELECT u.name, o.amount
FROM users u
FULL JOIN orders o ON u.id = o.user_id;

One practical note: MySQL doesn't support FULL JOIN. You get the same result by taking a LEFT JOIN and UNION-ing it with a RIGHT JOIN, which is more verbose but equivalent. PostgreSQL, SQL Server, and most other databases support it directly.

INNER JOIN when you only want records with data on both sides. LEFT JOIN when you want everything from the primary table and optional matching data from another. FULL JOIN when you're auditing data integrity and need to see what's unmatched on either side — it's more useful in data migration work than in application queries.

The majority of day-to-day queries use INNER JOIN and LEFT JOIN. If you understand those two clearly, the others follow from the same logic. Once you have a working query, running it through the SQL Formatter helps make the JOIN structure readable before you commit it or share it for review.