PostgreSQL JOINs: INNER, LEFT, RIGHT, FULL with Real Examples

JOINs are how you combine data from multiple tables. Without JOINs, you’re limited to querying one table at a time. With JOINs, you can ask complex questions that span relationships.

What You’ll Learn

This guide covers:

INNER JOIN - rows that match in both tables
LEFT JOIN - keep all rows from the left table
RIGHT JOIN - keep all rows from the right table
FULL OUTER JOIN - all rows from both tables
CROSS JOIN - Cartesian product
Self-joins - joining a table to itself

Why JOINs Matter

Real-world data is relational. You store employees in one table, departments in another, projects in a third. To answer questions like “Which employees work in the Sales department?” or “Show me all projects assigned to the Engineering department,” you need JOINs.

Without JOINs, you’d have to:

Query employees table
Query departments table separately
Manually match them in your application code

JOINs let the database do the work efficiently.

Understanding Table Relationships

Before we explore JOINs, understand our employee database structure:

employees table has columns: emp_id, first_name, last_name, email, hire_date, dept_id, salary

departments table has columns: dept_id, dept_name, location, manager_id

The dept_id in employees references dept_id in departments. This is a one-to-many relationship: one department has many employees.

INNER JOIN: Only Matching Rows

INNER JOIN returns rows that exist in both tables.

INNER JOIN Syntax

SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

INNER JOIN Example

Get employees with their department information:

SELECT
    e.emp_id,
    e.first_name,
    e.last_name,
    d.dept_name,
    d.location
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
ORDER BY d.dept_name, e.last_name;

Breaking it down:

FROM employees e - left table (employees) with alias e
INNER JOIN departments d - right table (departments) with alias d
ON e.dept_id = d.dept_id - join condition: match department IDs
Results show employees with their department

Result (sample):

 emp_id | first_name | last_name |    dept_name    | location
--------+------------+-----------+-----------------+----------
      7 | David      | Miller    | Engineering     | San Francisco
      8 | Jennifer   | Martinez  | Engineering     | San Francisco
      9 | Richard    | Rodriguez | Engineering     | San Francisco
      1 | James      | Wilson    | Sales           | New York
      2 | Mary       | Johnson   | Sales           | New York

Only employees with valid dept_ids appear. If an employee had dept_id = NULL or a non-existent department, they’d be excluded.

INNER JOIN with WHERE

Filter results after joining:

SELECT e.first_name, e.salary, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_name = 'Engineering'
ORDER BY e.salary DESC;

Result:

 first_name | salary  |   dept_name
------------+---------+---------------
 David      | 105000  | Engineering
 Jennifer   | 102000  | Engineering
 Susan      | 99000   | Engineering

LEFT JOIN: Keep All Left Table Rows

LEFT JOIN returns all rows from the left table plus matching rows from the right table. If no match exists, right table columns are NULL.

LEFT JOIN Syntax

SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

LEFT JOIN Example

Get all departments and their employees (including departments with no employees):

SELECT
    d.dept_name,
    d.location,
    COUNT(e.emp_id) as employee_count
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name, d.location
ORDER BY d.dept_name;

Result:

   dept_name    | location      | employee_count
-----------------+---------------+---------------
 Advanced        | Seattle       |             0
 Engineering     | San Francisco |             8
 Marketing       | Los Angeles   |             6
 Operations      | Chicago       |            10
 Sales           | New York      |             6

Notice “Advanced” department exists but has 0 employees. A regular INNER JOIN would exclude it entirely.

LEFT JOIN with NULL Check

Find departments that have no employees:

SELECT d.dept_name, d.location
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
WHERE e.emp_id IS NULL
ORDER BY d.dept_name;

Result:

   dept_name    | location
-----------------+----------
 Advanced        | Seattle

The WHERE e.emp_id IS NULL finds rows from the left table that have NO match in the right table.

RIGHT JOIN: Keep All Right Table Rows

RIGHT JOIN is the opposite of LEFT JOIN. It returns all rows from the right table plus matching rows from the left table.

RIGHT JOIN Example

Get all employees and their departments (from the opposite perspective):

SELECT
    e.first_name,
    e.last_name,
    d.dept_name
FROM departments d
RIGHT JOIN employees e ON d.dept_id = e.dept_id
ORDER BY e.last_name;

This is equivalent to:

SELECT
    e.first_name,
    e.last_name,
    d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
ORDER BY e.last_name;

RIGHT JOIN is less common because you can always rewrite it as a LEFT JOIN with tables reversed.

FULL OUTER JOIN: All Rows from Both Tables

FULL OUTER JOIN returns all rows from both tables. Where there’s no match, columns from the other table are NULL.

FULL OUTER JOIN Example

Get all employees and all departments, showing which employees belong to which departments:

SELECT
    COALESCE(e.first_name || ' ' || e.last_name, 'N/A') as employee_name,
    COALESCE(d.dept_name, 'Unassigned') as dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.dept_id
ORDER BY d.dept_name, e.last_name;

Breaking it down:

COALESCE() replaces NULL with a default value
Returns all employees (matched with their departments)
Plus any departments with no employees (showing as “Unassigned”)

Result (partial):

     employee_name     |    dept_name
-----------------------+---------------
 David Miller          | Engineering
 Jennifer Martinez     | Engineering
 Susan Thomas          | Engineering
 N/A                   | Advanced
 N/A                   | Research
 James Wilson          | Sales

CROSS JOIN: Cartesian Product

CROSS JOIN combines every row from the first table with every row from the second table. The result has (rows1 × rows2) rows.

CROSS JOIN Example

Get all possible employee-department combinations:

SELECT e.first_name, d.dept_name
FROM employees e
CROSS JOIN departments d
LIMIT 10;

If you have 30 employees and 5 departments, you get 150 rows. Each employee is paired with each department.

Result:

 first_name |   dept_name
------------+---------------
 James      | Sales
 James      | Engineering
 James      | Marketing
 James      | Operations
 James      | Advanced
 Mary       | Sales
 Mary       | Engineering
 Mary       | Marketing
 Mary       | Operations
 Mary       | Advanced

CROSS JOIN is rarely useful for real queries, but it’s powerful for generating data combinations (like creating schedules or cartesian coordinates).

Self-Joins: Joining a Table to Itself

A self-join joins a table to itself. Useful for hierarchical data like manager-employee relationships.

Self-Join Example

Find employees and their managers:

SELECT
    e.first_name || ' ' || e.last_name as employee_name,
    m.first_name || ' ' || m.last_name as manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id
ORDER BY e.last_name;

Breaking it down:

FROM employees e - employees alias
LEFT JOIN employees m - join to employees again (managers alias)
ON e.manager_id = m.emp_id - employee’s manager_id matches a manager’s emp_id

Result (if manager_id was populated):

    employee_name    |     manager_name
---------------------+---------------------
 David Miller        | Sarah Anderson
 James Wilson        | Sarah Anderson
 Jennifer Martinez   | David Miller

Multiple JOINs: Combining More Than 2 Tables

You can chain JOINs to combine 3, 4, or more tables.

Multiple JOINs Example

Get employees, their departments, and projects assigned to their departments:

SELECT
    e.first_name,
    e.last_name,
    d.dept_name,
    p.project_name,
    p.budget
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
INNER JOIN projects p ON d.dept_id = p.dept_id
WHERE p.budget > 100000
ORDER BY d.dept_name, p.project_name;

Breaking it down:

Start with employees
JOIN to departments (get department info)
JOIN to projects (get projects for that department)
Filter to projects > $100,000

Result:

 first_name |   dept_name    |          project_name          | budget
------------+----------------+--------------------------------+----------
 David      | Engineering    | Cloud Migration Initiative     | 320000.00
 David      | Engineering    | Mobile App Redesign            | 250000.00
 David      | Engineering    | API Performance Optimization   | 180000.00
 Matthew    | Operations     | Facility Upgrade               | 150000.00

JOIN Performance Tips

1. Use WHERE to Filter Before JOIN

-- Slower: joins all rows, then filters
SELECT *
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_name = 'Engineering'

-- Faster: filters first (if possible)
SELECT *
FROM departments d
INNER JOIN employees e ON d.dept_id = e.dept_id
WHERE d.dept_name = 'Engineering'

PostgreSQL’s query planner often optimizes both the same way, but specifying filter conditions efficiently helps.

2. Create Indexes on JOIN Columns

CREATE INDEX idx_employees_dept_id ON employees(dept_id);
CREATE INDEX idx_departments_dept_id ON departments(dept_id);

JOINs on indexed columns are much faster.

3. Use INNER JOIN by Default

INNER JOIN is faster than LEFT JOIN because it doesn’t need to handle NULL rows. Use LEFT JOIN only when you need unmatched rows.

4. Avoid Unnecessary JOINs

-- Don't do this if you only need employee salary
SELECT e.*, d.*, p.*
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
LEFT JOIN projects p ON d.dept_id = p.dept_id

-- Do this instead
SELECT e.first_name, e.salary
FROM employees e

Common Errors & Solutions

Error: “column reference is ambiguous”

Problem:

SELECT first_name, salary
FROM employees
INNER JOIN departments
WHERE dept_id = 2;

Both tables have a column reference that’s unclear (dept_id exists in both).

Solution:

Specify which table:

SELECT e.first_name, e.salary
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_id = 2;

Error: “invalid reference to FROM-clause variable”

Problem:

SELECT e.first_name, d.dept_name
FROM employees e
WHERE d.dept_id = 2;

You’re referencing d (departments) without joining it.

Solution:

Add the JOIN:

SELECT e.first_name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_id = 2;

FAQ

Q: What’s the difference between INNER and LEFT JOIN? A: INNER returns only matching rows. LEFT returns all left table rows plus matches. If you want unmatched rows, use LEFT.

Q: Which JOIN is fastest? A: INNER JOIN is typically fastest. LEFT/RIGHT/FULL OUTER are slower because they handle NULL values. Only use them if you need unmatched rows.

Q: Can I chain multiple JOINs? A: Yes. FROM table1 JOIN table2 JOIN table3 JOIN table4 works fine. Each JOIN adds rows/columns.

Q: What’s a self-join used for? A: Hierarchical data (employees-managers), graph relationships, or finding similar records in the same table.

Q: Should I use aliases? A: Yes! e for employees, d for departments makes queries readable and prevents ambiguous column errors.

Next Steps

You now understand how to combine data from multiple tables. Your next steps:

Multiple JOINs Deep Dive - Chain 3+ tables together
Advanced JOIN Conditions - Complex filtering and non-equi joins

JOINs are fundamental to SQL. Master them, and you can query almost any data relationship your database contains!

PostgreSQL JOINs: Complete Guide to INNER, LEFT, RIGHT & FULL OUTER Joins