SQL JOINs Explained with Examples

SQL JOINs combine rows from two or more tables based on a related column. They’re essential for working with relational databases.

The Core JOIN Types

-- Sample tables
employees                    departments
| id | name    | dept_id |   | id | name       |
|----|---------|---------|   |----|------------|
| 1  | Alice   | 1       |   | 1  | Engineering|
| 2  | Bob     | 2       |   | 2  | Sales      |
| 3  | Charlie | 1       |   | 3  | Marketing  |
| 4  | Diana   | NULL    |   |    |            |

1. INNER JOIN

Returns only matching rows from both tables.

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

Diana is excluded (no matching department).

2. LEFT JOIN

Returns all rows from the left table, with matches from the right. Non-matches show NULL.

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

3. RIGHT JOIN

Returns all rows from the right table, with matches from the left.

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

4. FULL OUTER JOIN

Returns all rows from both tables. Non-matches show NULL on the opposite side.

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

Quick Decision Guide

Performance Tips

1. Always join on indexed columns — foreign keys should be indexed
2. Use table aliases — FROM employees e is cleaner than repeating employees
3. Filter early — use WHERE before JOINs when possible
4. Avoid SELECT * — name only the columns you need
5. Prefer INNER JOIN over LEFT JOIN when you don’t need unmatched rows (LEFT JOIN is slower)

Related: Check our PostgreSQL vs MySQL guide.