SQL JOINs Explained Simply

JOINs combine data from multiple tables. They're the foundation of relational databases and the most important SQL concept after SELECT and WHERE. This guide covers every JOIN type with practical examples.

INNER JOIN

Returns only rows that have matching values in both tables. If there's no match, the row is excluded.

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

Use when: you only want records that exist in both tables. Most common JOIN type.

LEFT JOIN

Returns all rows from the left table, plus matched rows from the right table. Unmatched rows get NULL for the right table's columns.

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

Use when: you want all records from the primary table, even if they don't have related records. Example: all customers, including those with no orders.

RIGHT JOIN

The mirror of LEFT JOIN. Returns all rows from the right table, plus matches from the left.

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

Use when: rarely. Most people rewrite RIGHT JOINs as LEFT JOINs by swapping the table order. It's the same result and easier to read.

FULL OUTER JOIN

Returns all rows from both tables. Unmatched rows from either side get NULLs.

SELECT a.name, b.name
FROM table_a a
FULL OUTER JOIN table_b b ON a.id = b.a_id;

Use when: comparing two datasets to find what's in one but not the other. Common in data reconciliation.

Note: MySQL doesn't support FULL OUTER JOIN. Use a UNION of LEFT JOIN and RIGHT JOIN instead.

CROSS JOIN

Returns every combination of rows from both tables (Cartesian product). No ON clause needed.

SELECT sizes.name, colors.name
FROM sizes
CROSS JOIN colors;

Use when: you need all possible combinations — product variants, date scaffolding, test matrices.