How to Perform an INNER JOIN on 3 Tables in PostgreSQL

Learn how to perform an INNER JOIN between three tables in PostgreSQL. When working with relational databases like PostgreSQL, the INNER JOIN is a common operation used to combine rows from two or more tables based on a matching condition (usually a primary or foreign key). When you need to join three tables, you can use INNER JOIN sequentially.

In this guide, we will walk through how to perform an INNER JOIN between three tables in PostgreSQL, explaining the process step-by-step and providing practical examples.

The basic structure of an INNER JOIN between two tables looks like this:

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

For three tables, you can add additional joins in sequence. The format would look like this:

SELECT columns
FROM table1
INNER JOIN table2 ON table1.common_column = table2.common_column
INNER JOIN table3 ON table2.common_column = table3.common_column;

Practical Example of INNER JOIN on 3 Tables

Let’s create a practical example to illustrate how you can perform an INNER JOIN between three tables in a PostgreSQL database. Assume we have three tables: customers, orders, and products.

Table Structures

Customers

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

Orders

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT,
    product_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Products

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100)
);

Inserting Sample Data

-- Insert customers
INSERT INTO customers (name) VALUES ('John');
INSERT INTO customers (name) VALUES ('Maria');

-- Insert products
INSERT INTO products (product_name) VALUES ('Chair');
INSERT INTO products (product_name) VALUES ('Table');

-- Insert orders
INSERT INTO orders (customer_id, product_id, order_date) VALUES (1, 1, '2025-03-01');
INSERT INTO orders (customer_id, product_id, order_date) VALUES (1, 2, '2025-03-02');
INSERT INTO orders (customer_id, product_id, order_date) VALUES (2, 2, '2025-03-03');

Performing an INNER JOIN on 3 Tables

Now, let’s run a query that returns the customer name, product name, and order date, by joining the three tables (customers, orders, and products) through the foreign keys.

SELECT c.name AS customer_name,
       p.product_name,
       o.order_date
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN products p ON o.product_id = p.product_id;

Explanation:

• customers c: The customers table is aliased as c.
• orders o: The orders table is aliased as o.
• products p: The products table is aliased as p.
• INNER JOIN orders o ON c.customer_id = o.customer_id: This joins customers and orders where customer_id in the customers table matches customer_id in the orders table.
• INNER JOIN products p ON o.product_id = p.product_id: This joins orders and products where product_id in the orders table matches product_id in the products table.

Expected Result:

In this example, the query returns all valid combinations between customers, orders, and products.

Key Points to Keep in Mind

1. Order of Joins:
   • The order of the joins matters. The first INNER JOIN links the first and second tables, and the second INNER JOIN connects the second and third tables.
2. Additional Filters:
   • You can add a WHERE clause to further filter the results, for example: WHERE o.order_date >= '2025-03-01';
3. Aliases:
   • Using aliases (like c, o, p) helps to keep the query clean and easier to read, especially when dealing with multiple tables.
4. Combining Columns:
   • If you need additional columns or want to format the data in a specific way (such as calculating the total order), simply modify the column list in the SELECT.

Conclusion

Performing an INNER JOIN between three or more tables in PostgreSQL is a common technique for combining related data from different tables. The basic query structure involves joining the tables sequentially, using the matching columns (usually foreign keys) to combine the rows.

To summarize, here’s how to join three tables:

• PostgreSQL Query Structure: Use INNER JOIN to connect the tables in sequence, specifying the common columns.
• Aliases: Simplify your query with aliases for each table.
• Filters: Use the WHERE clause for further refinement.
• Custom Columns: Modify the SELECT clause to retrieve and manipulate the data as needed.

This technique is essential for running complex queries and aggregating data from different sources. If you need more examples or have further questions, feel free to ask!