How to Join Three Tables in PostgreSQL: A Step-by-Step Guide

Joining three tables in PostgreSQL is a crucial skill for efficiently combining data from various sources. owever, joining three or more tables can introduce complexity. Let’s explore strategies to overcome these challenges and ensure your queries retrieve the desired data efficiently.

How does the JOIN clause work in PostgreSQL?

The JOIN clause combines rows from two or more tables based on a related column between them. INNER JOIN, the most common type, returns rows when matches exist in both tables.

Example schema

Consider three tables: employees, departments, and projects, where employees belong to departments and work on projects. Key columns include:

• employees: employee_id, name, department_id
• departments: department_id, department_name
• projects: project_id, project_name, employee_id

How to join three tables in PostgreSQL?

To combine these tables, use two JOIN clauses. The SQL query below selects all employees along with their departments and assigned projects:

SELECT employees.name, departments.department_name, projects.project_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id
INNER JOIN projects ON employees.employee_id = projects.employee_id;

Here, the first INNER JOIN merges employees with departments using department_id. The second INNER JOIN then connects to projects through employee_id.

Filtering and sorting

Incorporate WHERE clauses for specific criteria and ORDER BY clauses for sorting. For example, to list ‘Engineering’ department employees by project:

SELECT employees.name, departments.department_name, projects.project_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id
INNER JOIN projects ON employees.employee_id = projects.employee_id
WHERE departments.department_name = 'Engineering'
ORDER BY projects.project_name;

This adjusted approach streamlines data from different relational database areas, optimizing your queries to meet diverse data retrieval requirements.