SQL JOINs Explained: INNER, LEFT, RIGHT & FULL

The most asked SQL topic in interviews — and the one most tutorials get wrong. A complete guide with real examples for every JOIN type.

JOINs are the backbone of relational databases. The moment you need data from more than one table, you need a JOIN. Yet they remain one of the most confused topics in SQL — even for developers who've been writing queries for years. They're also a guaranteed SQL interview question. Bookmark the SQL cheat sheet for a quick JOIN syntax reference you can keep open while you practice.

What is a JOIN?

A JOIN combines rows from two or more tables based on a related column. The most common pattern is matching a foreign key in one table to the primary key in another.

-- Basic syntax
SELECT columns
FROM table_a a
JOIN table_b b ON a.id = b.a_id;

INNER JOIN

INNER JOIN returns only the rows where there is a match in both tables. If a row in table A has no matching row in table B, it's excluded from the result.

INNER JOIN — Only the rows with a match in both tables — the overlap of A and B.

-- Get all orders with their customer names
SELECT u.name, o.total, o.status
FROM orders o
INNER JOIN users u ON o.user_id = u.id;

This returns only users who have at least one order. Users with zero orders are excluded. Orders without a valid user_id are also excluded.

LEFT JOIN

LEFT JOIN returns all rows from the left table and matching rows from the right. Where there's no match, the right table columns come back as NULL.

LEFT JOIN — Every row from the left table (A), plus matching data from the right (B) where it exists.

-- All users, even those with no orders
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.name;

Users with zero orders show up with order_count = 0. This is the crucial difference from INNER JOIN — you keep every row from the left side.

-- Find users who have NEVER placed an order
SELECT u.name
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

RIGHT JOIN

RIGHT JOIN is the mirror of LEFT JOIN — all rows from the right table, with matching rows from the left. NULLs fill the left columns where there's no match.

RIGHT JOIN — Every row from the right table (B), plus matching data from the left (A) where it exists.

-- All orders, even those without a valid user
SELECT u.name, o.id AS order_id, o.total
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

FULL OUTER JOIN

FULL OUTER JOIN returns all rows from both tables. Where there's no match on either side, NULLs fill the missing columns. You get everything — matched and unmatched.

FULL OUTER JOIN — Every row from both tables, matched where possible and NULL-filled where not.

-- All users + all orders, matched where possible
SELECT u.name, o.id AS order_id, o.total
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

PostgreSQL supports FULL OUTER JOIN natively. MySQL does not — you'd use a UNION of LEFT and RIGHT JOINs to replicate it.

Self JOIN

A self JOIN joins a table to itself. The classic use case is hierarchical data — finding an employee's manager when both exist in the same table.

-- Employees with their managers
SELECT
  e.name AS employee,
  m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Common Mistakes & Tips

• Forgetting the ON condition creates a cartesian product (every row × every row)
• Joining on the wrong key (user_id vs id) gives silent, wrong results
• Using WHERE on a nullable right-table column turns LEFT JOIN into INNER JOIN
• Not aliasing tables makes multi-join queries unreadable
• Joining on non-indexed columns kills performance on large tables

Once JOINs click, window functions are the natural next step — they let you rank and aggregate without collapsing your joined rows.