SQL Joins Explained: INNER, LEFT, RIGHT, FULL

A join is how you pull columns from two tables in one query. Everything else about joins is surface decoration on that idea. You pick two tables, say which column matches which, and choose how strict you want the matching to be. The strictness knob is the only thing that separates INNER, LEFT, RIGHT, and FULL OUTER.

The four joins in one sentence

INNER JOIN: keep only rows that match on both sides.
LEFT JOIN: keep every row from the left table; fill the right side with NULL if no match.
RIGHT JOIN: same as LEFT but mirrored. You almost never need it. Flip your FROM order instead.
FULL OUTER JOIN: keep every row from both sides; fill the missing side with NULL.

A concrete example

Two tables, users and orders. Every order has a user_id. Not every user has placed an order.

users
 id | name
----+--------
  1 | Ada
  2 | Grace
  3 | Maya      -- no orders

orders
 id | user_id | total
----+---------+------
 10 |       1 | 29.90
 11 |       1 | 12.00
 12 |       2 | 45.50
 13 |       9 | 7.99  -- user_id 9 doesn't exist

INNER JOIN

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

Returns only rows where the join condition is true on both sides. Ada and Grace appear. Maya is dropped because she has no order. The orphan order with user_id = 9 is also dropped because no user matches.

LEFT JOIN

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

Every user shows up. Maya appears with NULL totals. Typical use: list every customer and attach their orders, including customers who have never ordered.

FULL OUTER JOIN

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

Every user plus every order. Maya has NULL totals; the orphan order has a NULL name. Useful for auditing referential integrity: NULLs on either side point at broken data.

USING vs ON

USING (user_id) is a shorthand when the join column has the same name on both sides. It also hides the duplicate column in the result:

SELECT *
FROM orders
INNER JOIN users USING (user_id);

Prefer ON when column names differ or when you want both columns in the output. USING reads cleaner in interview answers but is less portable across databases (SQLite supports it, most others do).

The cardinality trap

The classic bug: you join on a column that is not unique on one side, and your row count explodes. If a user has three orders and you join users to orders, every user row appears three times. If you then aggregate, you double-count.

-- WRONG: counts order lines, not users
SELECT COUNT(*) FROM users u JOIN orders o USING (user_id);

-- RIGHT: counts distinct users with an order
SELECT COUNT(DISTINCT u.id) FROM users u JOIN orders o USING (user_id);

Rule of thumb: before you join, know the grain of each table. If you are joining a 1:N, the result has the grain of the N side. If you are joining N:M, the result is a product and you probably wanted a subquery or CTE first.

RIGHT JOIN exists but you should not use it

A RIGHT JOIN is just a LEFT JOIN mirrored. Any query that uses RIGHT can be rewritten with LEFT by swapping the table order, and it reads more naturally. Teams generally standardize on LEFT and flip the source order when they need the other direction.

Self-joins

Nothing stops you from joining a table to itself. A common example: an employees table where each row has amanager_id that points back into the same table.

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

Practice this live

The Network chapter in SQL Protocol runs you through every join type against a live database. Interview Mode Floor 2 has timed join puzzles that look exactly like what onsite interviewers ask.

SQL Joins Explained with Worked Examples