Understanding PostgreSQL JOIN Types with Practical Examples

PostgreSQL JOIN Types

If you’ve ever worked with relational databases, you’ve probably encountered SQL JOINs — and if you’re anything like most developers, you’ve probably misunderstood or misused one at some point.

In this guide, we’ll walk through PostgreSQL JOIN types using the same dataset throughout, so you can clearly see how each JOIN behaves and what result it returns.

Why JOINs Matter

JOINs let you combine rows from two or more tables based on related columns. They’re crucial when your data is normalized across tables and you want to query meaningful relationships between them.

Sample Data

We’ll use two simple tables: employees and departments.

Table: employees

| id | name     | department_id |
|----|----------|----------------|
| 1  | Alice    | 10             |
| 2  | Bob      | 20             |
| 3  | Charlie  | 30             |
| 4  | Diana    | NULL           |

Table: departments

| id | name         |
|----|--------------|
| 10 | Engineering  |
| 20 | HR           |
| 40 | Marketing    |

1. INNER JOIN

An INNER JOIN returns rows only where there is a match in both tables.

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

| employee | department  |
|----------|-------------|
| Alice    | Engineering |
| Bob      | HR          |

2. LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table, and the matched rows from the right table. If there’s no match, you get NULL.

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

| employee | department  |
|----------|-------------|
| Alice    | Engineering |
| Bob      | HR          |
| Charlie  | NULL        |
| Diana    | NULL        |

3. RIGHT JOIN (RIGHT OUTER JOIN)

Returns all rows from the right table, and the matched rows from the left table. Unmatched left-side rows appear as NULL.

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

| employee | department  |
|----------|-------------|
| Alice    | Engineering |
| Bob      | HR          |
| NULL     | Marketing   |

4. FULL JOIN (FULL OUTER JOIN)

Returns all rows from both tables, whether there’s a match or not.

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

| employee | department  |
|----------|-------------|
| Alice    | Engineering |
| Bob      | HR          |
| Charlie  | NULL        |
| Diana    | NULL        |
| NULL     | Marketing   |

5. CROSS JOIN

A CROSS JOIN returns the Cartesian product — all possible combinations of rows.

SELECT e.name AS employee, d.name AS department
FROM employees e
CROSS JOIN departments d;

| employee | department  |
|----------|-------------|
| Alice    | Engineering |
| Alice    | HR          |
| Alice    | Marketing   |
| Bob      | Engineering |
| Bob      | HR          |
| Bob      | Marketing   |
| Charlie  | Engineering |
| Charlie  | HR          |
| Charlie  | Marketing   |
| Diana    | Engineering |
| Diana    | HR          |
| Diana    | Marketing   |

6. SELF JOIN

A SELF JOIN is when you join a table to itself. Useful for hierarchical data, like manager relationships.

Let’s extend our employees table:

| id | name     | manager_id |
|----|----------|------------|
| 1  | Alice    | NULL       |
| 2  | Bob      | 1          |
| 3  | Charlie  | 1          |
| 4  | Diana    | 2          |

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

| employee | manager |
|----------|---------|
| Alice    | NULL    |
| Bob      | Alice   |
| Charlie  | Alice   |
| Diana    | Bob     |

7. NATURAL JOIN

NATURAL JOIN automatically joins tables using columns with the same name.

In our case, employees has department_id, while departments has id — so NATURAL JOIN would not work by default because the column names differ.

-- ❌ THIS WON'T WORK, in our case.
SELECT * FROM employees NATURAL JOIN departments;

To fix this, we alias departments.id as department_id in a subquery. Now both tables have a column named department_id, and NATURAL JOIN can match correctly.

-- ✅ THIS WORKS! Because now we have employees.department_id and d.department_id
SELECT *
FROM employees
NATURAL JOIN (
  SELECT id AS department_id, name AS dept_name
  FROM departments
) d;

| id | name     | department_id | dept_name   |
|----|----------|----------------|-------------|
| 1  | Alice    | 10             | Engineering |
| 2  | Bob      | 20             | HR          |

8. JOIN … USING

USING is a clean way to join on shared column names.

SELECT e.name AS employee, d.name AS department
FROM employees e
JOIN departments_renamed d USING (department_id);

| employee | department  |
|----------|-------------|
| Alice    | Engineering |
| Bob      | HR          |

9. Anti JOIN

Finds rows in one table that do not match any in another. Implemented using LEFT JOIN ... WHERE ... IS NULL.

SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE d.id IS NULL;

| name    |
|---------|
| Charlie |
| Diana   |

Summary Table

| JOIN Type      | Matches?| Unmatched Left| Unmatched Right| Use Case                         |
|----------------|---------|---------------|----------------|----------------------------------|
| INNER JOIN     | ✅      | ❌           | ❌             | Matched data only               |
| LEFT JOIN      | ✅      | ✅           | ❌             | All from left + matches         |
| RIGHT JOIN     | ✅      | ❌           | ✅             | All from right + matches        |
| FULL JOIN      | ✅      | ✅           | ✅             | All rows from both              |
| CROSS JOIN     | ❌      | ❌           | ❌             | Cartesian product (every combo) |
| SELF JOIN      | ✅      | ✅           | ✅             | Hierarchies or same-table logic |
| NATURAL JOIN   | ✅      | ❌           | ❌             | Auto join on same-named cols    |
| JOIN ... USING | ✅      | ❌           | ❌             | Cleaner syntax on common cols   |
| Anti JOIN      | ❌      | ✅           | ❌             | Filter for non-matching rows    |

Final Thoughts

Understanding JOINs is a must-have skill for every backend developer, data engineer, or analyst. Keep this guide bookmarked, and the next time you see a JOIN clause, you’ll know exactly what’s going on — and why.

Want to dig deeper or see example projects? Leave a comment or follow for more database deep-dives!