SQL JOIN Types Explained: INNER, LEFT, RIGHT, FULL, CROSS (With Diagrams)

Every JOIN type answers the same question differently: "when a row on one side has no match on the other side, do I keep it, drop it, or fill it with NULL?" Rather than explain each JOIN in isolation, this guide runs the same two tables through every JOIN type — INNER, LEFT, RIGHT, FULL, CROSS, and SELF — so you can compare the exact result set side by side. Each section has a diagram and a result table you can verify by hand.

The Two Tables Used Throughout

Three customers, four orders. Notice Cara has no orders, and order 104 belongs to customer_id 9, which does not exist in the customers table — these two "orphan" rows are what make LEFT, RIGHT, and FULL JOIN behave differently from INNER JOIN.

■ INNER JOIN

Returns only rows that have a match on both sides. Cara (no orders) and order 104 (no matching customer) are both dropped.

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

■ LEFT JOIN (LEFT OUTER JOIN)

Keeps every row from the left table (customers), even without a match. Cara now appears with NULL order fields. Order 104 is still dropped — it isn't in the left table.

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

■ RIGHT JOIN (RIGHT OUTER JOIN)

The mirror image of LEFT JOIN: keeps every row from the right table (orders). Order 104 now appears with a NULL customer name. Cara is dropped — she isn't in the right table.

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

Since a RIGHT JOIN can always be rewritten as a LEFT JOIN by swapping the table order (FROM orders o LEFT JOIN customers c ON ...), most style guides — including this site's — recommend sticking to LEFT JOIN everywhere for consistency and dropping RIGHT JOIN entirely.

■ FULL JOIN (FULL OUTER JOIN)

Keeps every row from both tables. Both Cara and order 104 appear, each with NULLs on the side that has no match.

-- PostgreSQL, SQL Server, Oracle:
SELECT c.name, o.id AS order_id, o.amount
FROM customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id;

MySQL has no FULL OUTER JOIN keyword. Simulate it with a LEFT JOIN and a RIGHT JOIN combined by UNION, which de-duplicates the matched rows both sides agree on:

-- MySQL workaround:
SELECT c.name, o.id AS order_id, o.amount
FROM customers c LEFT JOIN orders o ON c.id = o.customer_id
UNION
SELECT c.name, o.id AS order_id, o.amount
FROM customers c RIGHT JOIN orders o ON c.id = o.customer_id;

CROSS JOIN

No ON condition — every row in the first table is paired with every row in the second. 3 customers × 4 orders = 12 rows, almost all of them meaningless pairings. CROSS JOIN is rarely written on purpose; it's far more often an accident from a missing or mistyped ON clause (see the FAQ below).

SELECT c.name, o.id AS order_id
FROM customers c
CROSS JOIN orders o;
-- 3 customers x 4 orders = 12 rows

SELF JOIN

Not a distinct JOIN type — it's an INNER or LEFT JOIN where a table is joined to itself, usually to compare rows within the same table. The classic example: finding each employee's manager, where both are rows in the same employees table.

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

JOIN Types at a Glance

Frequently Asked Questions