SQL JOIN types explained with examples

Let me explain with a practical example!

Sample Tables

-- employees table
| id | name    | dept_id |
|----|---------|---------|
| 1  | Alice   | 1       |
| 2  | Bob     | 2       |
| 3  | Charlie | NULL    |

-- departments table
| id | name        |
|----|-------------|
| 1  | Engineering |
| 2  | Marketing   |
| 3  | HR          |

INNER JOIN

Returns only matching rows from both tables.

SELECT e.name, d.name as dept
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

Result:

(Charlie excluded - no matching dept)

LEFT JOIN

Returns ALL rows from left table + matching from right.

SELECT e.name, d.name as dept
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

Result:

(Charlie included with NULL dept)

RIGHT JOIN

Returns ALL rows from right table + matching from left.

SELECT e.name, d.name as dept
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

Result:

(HR included even though no employees)

FULL OUTER JOIN

Returns ALL rows from BOTH tables.

SELECT e.name, d.name as dept
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id;

Result:

CROSS JOIN

Returns Cartesian product (every combination).

SELECT e.name, d.name as dept
FROM employees e
CROSS JOIN departments d;

Result: 3 employees × 3 departments = 9 rows!

Quick Reference

Pro tip: Start with LEFT JOIN if unsure - it's the most commonly needed!