SQL JOINs Explained: INNER, LEFT, RIGHT, and FULL

Posted by Kyle Hankinson May 5, 2021

---

JOINs combine rows from two or more tables based on a related column. Understanding which rows each join type returns is fundamental to writing correct queries.

Sample Data

-- customers          -- orders
-- id | name          -- id | customer_id | total
-- 1  | Alice         -- 1  | 1           | 50.00
-- 2  | Bob           -- 2  | 1           | 75.00
-- 3  | Carol         -- 3  | 3           | 30.00

Bob has no orders. Orders all belong to Alice or Carol.

INNER JOIN

Returns only rows that have a match in both tables:

SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

name	total
Alice	50.00
Alice	75.00
Carol	30.00

Bob is excluded because he has no matching order.

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table, plus matching rows from the right. Non-matching right-side columns are NULL:

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

name	total
Alice	50.00
Alice	75.00
Bob	NULL
Carol	30.00

Bob appears with NULL because he has no orders. This is the most common join for "show everything, even if there's no match."

RIGHT JOIN (RIGHT OUTER JOIN)

The mirror of LEFT JOIN — returns all rows from the right table:

SELECT c.name, o.total
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;

In practice, RIGHT JOIN is rarely used. You can always rewrite it as a LEFT JOIN by swapping the table order.

FULL OUTER JOIN

Returns all rows from both tables, with NULLs where there is no match:

SELECT c.name, o.total
FROM customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id;

This is useful when you need to find unmatched rows on both sides — for example, customers without orders AND orders without valid customers.

MySQL Does Not Support FULL OUTER JOIN

MySQL lacks native FULL OUTER JOIN support. The workaround is a UNION of LEFT and RIGHT joins:

SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
UNION
SELECT c.name, o.total
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;

CROSS JOIN

Returns every combination of rows from both tables (the Cartesian product):

SELECT c.name, p.product_name
FROM customers c
CROSS JOIN products p;

If customers has 3 rows and products has 10 rows, the result has 30 rows. Useful for generating combinations but dangerous on large tables.

Self-Join

A table joined to itself — commonly used for hierarchical data:

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

Common Mistakes

Forgetting the ON clause turns your join into a CROSS JOIN, producing a massive result set.

Filtering in WHERE vs ON matters for outer joins:

-- This filters AFTER the join, removing NULLs
SELECT * FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.total > 50;

-- This filters DURING the join, keeping all customers
SELECT * FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id AND o.total > 50;

The second query preserves all customers, showing NULL for those without qualifying orders.

---