PostgreSQL INNER JOIN Examples: From Basic to Advanced Patterns

PostgreSQL INNER JOIN Examples: From Basic to Advanced

INNER JOINs are the most common join type. They return only matching rows from both tables. This guide walks through real-world patterns you’ll actually use - filtering, aggregating, joining multiple tables, and solving common business problems.

What You’ll Learn

This guide covers:

• Basic INNER JOIN syntax and matching
• Filtering with WHERE after joining
• Multiple INNER JOINs in one query
• Aggregate functions with joins
• Common business problems solved with INNER JOINs
• Performance optimization patterns

Understanding INNER JOIN Matching

Before diving into examples, understand how INNER JOIN works. The ON clause defines which rows match.

-- Simple match: emp_id matches
SELECT e.emp_id, e.first_name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

The ON condition e.dept_id = d.dept_id means:

• Take each employee
• Find the department where dept_id matches
• Include that row in results
• If no match exists, exclude the employee

Basic INNER JOIN Patterns

Single Table Join

Get employees with their department names.

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
LIMIT 10;

Result:

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
     15 | Thomas     | Harris     | Marketing       | Los Angeles
     16 | Karen      | Clark      | Marketing       | Los Angeles
      1 | James      | Wilson     | Sales           | New York
      2 | Mary       | Johnson    | Sales           | New York

Why INNER JOIN here? Because every employee has a dept_id. We want employees with their departments. LEFT JOIN would be used only if some employees might have NULL dept_id.

Join with Column Alias

Use aliases to clarify which table columns come from.

-- Find salary information alongside departments
SELECT
    e.first_name || ' ' || e.last_name as employee_name,
    e.salary,
    d.dept_name,
    d.location as dept_location
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
WHERE e.salary > 80000
ORDER BY e.salary DESC;

Result:

     employee_name     | salary  |    dept_name    | dept_location
-----------------------+---------+-----------------+---------------
David Miller           | 105000  | Engineering     | San Francisco
Jennifer Martinez      | 102000  | Engineering     | San Francisco
Susan Thomas           |  99000  | Engineering     | San Francisco
Richard Rodriguez      |  98000  | Engineering     | San Francisco

Filtering with WHERE

Find employees in specific departments.

-- Engineering employees only
SELECT e.first_name, e.last_name, e.salary
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 | last_name  | salary
------------+------------+--------
David      | Miller     | 105000
Jennifer   | Martinez   | 102000
Susan      | Thomas     |  99000
Richard    | Rodriguez  |  98000
Barbara    | Taylor     |  95000
Charles    | Anderson   |  92000
Joseph     | Lee        |  96000
Jessica    | White      |  90000

The WHERE clause filters after the join. PostgreSQL optimizes this automatically, but conceptually:

1. Join tables
2. Filter results

Intermediate INNER JOIN Patterns

Aggregate Functions with Joins

Count employees per department.

SELECT
    d.dept_name,
    d.location,
    COUNT(e.emp_id) as total_employees,
    AVG(e.salary) as avg_salary,
    MIN(e.salary) as lowest_salary,
    MAX(e.salary) as highest_salary
FROM departments d
INNER JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name, d.location
ORDER BY total_employees DESC;

Result:

   dept_name    | location      | total_employees | avg_salary | lowest_salary | highest_salary
-----------------+---------------+-----------------+------------+---------------+----------------
Operations      | Chicago       |              10 |  68,700.00 |      64,000   |      72,000
Engineering     | San Francisco |               8 |  97,625.00 |      90,000   |     105,000
Marketing       | Los Angeles   |               6 |  78,833.33 |      75,000   |      82,000
Sales           | New York      |               6 |  74,166.67 |      70,000   |      78,000

Key insight: GROUP BY includes all non-aggregate columns. The join allows us to get department information alongside employee statistics.

Multiple Conditions in ON Clause

Sometimes matching requires more than one condition.

-- Join with additional date constraint
SELECT
    e.first_name,
    e.last_name,
    s.salary,
    s.effective_date
FROM employees e
INNER JOIN salaries s
    ON e.emp_id = s.emp_id
    AND s.effective_date >= '2023-01-01'
ORDER BY e.last_name, s.effective_date;

Result:

first_name | last_name | salary  | effective_date
------------+-----------+---------+----------------
James      | Wilson    | 72000   | 2022-01-01
James      | Wilson    | 75000   | 2023-06-01
Mary       | Johnson   | 77000   | 2021-07-01
Mary       | Johnson   | 78000   | 2023-01-15

The condition s.effective_date >= '2023-01-01' in the ON clause filters which salary records to join. This is different from WHERE - it affects which rows are considered for joining, not just which results are shown.

Three-Table Join

Get employees, their departments, and projects assigned to those 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.budget DESC;

Result:

first_name | last_name |    dept_name    |             project_name          | budget
------------+-----------+-----------------+-----------------------------------+----------
David      | Miller    | Engineering     | Cloud Migration Initiative        | 320000
David      | Miller    | Engineering     | Mobile App Redesign               | 250000
David      | Miller    | Engineering     | API Performance Optimization      | 180000
Matthew    | Hernandez | Operations      | Facility Upgrade                  | 150000

Each join adds another table:

1. Start with employees
2. Join to departments (get dept info)
3. Join to projects (get projects for that dept)
4. Filter to budgets > 100K

Advanced INNER JOIN Patterns

Self-Join: Employee Hierarchy

Find which employees are managed by which managers.

-- Employees and their managers
SELECT
    e.first_name || ' ' || e.last_name as employee_name,
    m.first_name || ' ' || m.last_name as manager_name,
    d.dept_name
FROM employees e
INNER JOIN employees m ON e.manager_id = m.emp_id
INNER JOIN departments d ON e.dept_id = d.dept_id
ORDER BY d.dept_name, e.last_name;

Note: This uses INNER JOIN, so only employees with a manager appear. A self-join with LEFT JOIN would include employees without managers.

Result:

       employee_name       |      manager_name       |    dept_name
--------------------------+-------------------------+---------------
[Self-joins require manager_id to be populated in your schema]

Join with Derived Table (Subquery)

Find departments whose average salary exceeds company average.

SELECT
    d.dept_name,
    d.location,
    AVG(e.salary) as dept_avg_salary,
    company_avg.company_average
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
INNER JOIN (
    SELECT AVG(salary) as company_average
    FROM employees
) company_avg ON TRUE
GROUP BY d.dept_id, d.dept_name, d.location, company_avg.company_average
HAVING AVG(e.salary) > company_avg.company_average
ORDER BY dept_avg_salary DESC;

Result:

   dept_name    | location      | dept_avg_salary | company_average
-----------------+---------------+-----------------+-----------------
Engineering     | San Francisco |     97,625.00   |     77,500.00
Operations      | Chicago       |     68,700.00   |     77,500.00

The subquery calculates company average once. The INNER JOIN on TRUE means “join everything” (there’s only one row in the subquery). HAVING filters after grouping.

Join with Window Functions

Rank employees by salary within their department.

SELECT
    e.first_name,
    e.last_name,
    d.dept_name,
    e.salary,
    RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) as dept_rank
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
ORDER BY d.dept_name, dept_rank;

Result:

first_name | last_name | dept_name     | salary | dept_rank
------------+-----------+---------------+---------+-----------
David      | Miller    | Engineering   | 105000 |         1
Jennifer   | Martinez  | Engineering   | 102000 |         2
Susan      | Thomas    | Engineering   |  99000 |         3
Richard    | Rodriguez | Engineering   |  98000 |         4

The window function RANK() OVER (PARTITION BY...) calculates rank separately for each department.

Real-World Business Scenarios

Sales Analysis by Department

Get total sales per department with performance metrics.

SELECT
    d.dept_name,
    COUNT(DISTINCT e.emp_id) as num_employees,
    AVG(e.salary) as avg_salary,
    SUM(e.salary) as total_payroll
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
GROUP BY d.dept_id, d.dept_name
HAVING COUNT(DISTINCT e.emp_id) >= 5
ORDER BY total_payroll DESC;

Result:

   dept_name    | num_employees | avg_salary | total_payroll
-----------------+---------------+------------+---------------
Operations      |            10 |  68,700    |     687,000
Engineering     |             8 |  97,625    |     781,000
Marketing       |             6 |  78,833    |     473,000
Sales           |             6 |  74,166    |     445,000

Employee-Project Assignment

Employees with current projects and budgets.

SELECT
    e.first_name,
    e.last_name,
    d.dept_name,
    p.project_name,
    p.budget,
    (p.budget / NULLIF(COUNT(e.emp_id) OVER (PARTITION BY p.project_id), 0))
        as budget_per_employee
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.end_date IS NULL  -- Only ongoing projects
ORDER BY p.budget DESC;

This shows employees, their projects, and budget allocation per employee.

Common Errors & Solutions

Error: “column reference is ambiguous”

Problem:

SELECT emp_id, dept_id, salary
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

Both tables have dept_id. PostgreSQL doesn’t know which one you want.

Solution:

Specify the table:

SELECT e.emp_id, e.dept_id, e.salary, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

Error: “INNER JOIN condition is redundant”

Problem:

FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
WHERE e.dept_id IS NOT NULL;  -- Redundant - INNER JOIN already requires match

INNER JOIN already guarantees a match exists.

Solution:

Remove the redundant WHERE:

FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id

Missing Rows After Join

Problem:

SELECT COUNT(*) FROM employees;        -- Returns 30
SELECT COUNT(*) FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;  -- Returns 30

Rows disappeared after the join!

Causes & Solutions:

1. Some employees have NULL dept_id - Use LEFT JOIN if you want them
2. Join condition is wrong - Check your ON clause matches correctly
3. Departments have been deleted - Referential integrity issue

Performance Tips

1. Index the Join Column

Joins on indexed columns are much faster:

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

Without these indexes, PostgreSQL must scan the entire table.

2. Use INNER JOIN When Appropriate

INNER JOIN is faster than LEFT JOIN because it doesn’t handle NULL rows:

-- Faster: INNER JOIN
SELECT * FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

-- Slower: LEFT JOIN (not needed if all employees have dept)
SELECT * FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

Only use LEFT JOIN if you need unmatched rows.

3. Join Order Matters for Performance

PostgreSQL’s query planner optimizes join order, but putting smaller tables first can help:

-- Usually fine - planner optimizes
SELECT * FROM big_table b
INNER JOIN small_table s ON b.id = s.id;

-- Also fine - same result
SELECT * FROM small_table s
INNER JOIN big_table b ON b.id = s.id;

4. EXPLAIN ANALYZE Shows Join Performance

EXPLAIN ANALYZE
SELECT * FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

Look for “Hash Join” or “Nested Loop” in the output. Hash Join is usually faster for large datasets.

FAQ

Q: When should I use INNER vs LEFT JOIN? A: INNER when you only want matching rows. LEFT when you want all rows from the left table plus matches from the right.

Q: Can I join on non-key columns? A: Yes, any columns can be joined: ON e.manager_id = m.emp_id joins on a non-primary key. Just ensure data makes sense.

Q: What if the join column has NULL values? A: INNER JOIN excludes rows with NULL (no match). Use LEFT JOIN if you need to keep them.

Q: Can I use > or < in ON? A: Yes, that’s a non-equi join: ON e.salary > d.avg_salary. But it’s usually slower.

Q: How many tables can I join? A: Practically, 4-6 tables is common. PostgreSQL supports more, but each join adds complexity and time.

Q: Should I filter in ON or WHERE? A: Conditions on single tables go in WHERE. Conditions involving multiple tables can go in ON, but WHERE is clearer for filtering results.

Related Topics

Deepen your knowledge of joins:

• PostgreSQL JOINs Complete Guide - All JOIN types explained
• LEFT JOIN vs RIGHT JOIN - When to use outer joins
• Multiple JOINs in Single Query - Chaining joins for complex queries
• Complex JOIN Conditions - Non-equi joins and edge cases

You now have practical patterns for INNER JOINs. These patterns apply to filtering, aggregating, and combining data across your database.