PostgreSQL multiple joins

A multiple join is a query that contains similar or different join types that are used more than once. In this comprehensive article, I’ll walk you through everything you need about using multiple joins in PostgreSQL – from basic concepts to advanced approaches.

PostgreSQL multiple joins

Using multiple joins in PostgreSQL, we can combine various data tables to overcome relational database issues. We will understand this by using an example in which we create three tables and apply multiple joins to them.

Approach 1: Using the Direct Approach

Example

Let’s check the query for three tables.

select * from emp;
select * from customers;
select * from payment;

Let’s check the output of the three tables.

Now we tend to represent a three-table join example, so let’s consider the situation where you need to determine how much each customer has paid to which employee. We need a query that can get this data quickly and effectively. Let us break the query into two parts.

Firstly, we should know which customer paid which employee. This can be achieved through a single join query on the customer and payment table. Let’s check the query.

SELECT cust.name, pay.amount
FROM Customers cust
INNER JOIN Payment pay ON pay.customerID = cust.customerID ;

This query will give us outcomes about all customers and their respective payments. The second main query is to analyze which employee has received which payment. This can be attained through a common join query on the payment and employee tables.

SELECT emp.name , pay.amount
FROM Employees emp
INNER JOIN Payment pay ON pay.employeeID = emp.employeeID ;

Now, we will see that both of the above queries are similar, just referenced to different tables. If we want to check which customer paid which of the employees, we only have to merge the above two queries with another join statement. This query will point to three tables and two join statements. Let’s see the query.

SELECT
              cust.name AS cust_name,
              emp.name AS emp_name,
              pay.amount
FROM
              Customer cust
              INNER JOIN Payment pay ON pay.customerID = cust.customerID
              INNER JOIN Employees emp ON pay.employeeID = emp.employeeID;

This query will provide us with the outcome of how much each customer paid to each employee. It will first note the customer and payment tables with one join and then unite the resulting table with the employee table as a second join. Here is the output.

Approach-2: Using The Linear Multiple Join Pattern

In this approach, each table joins to the next in sequence.

Example

In this example, we are connecting customers to their orders, then to the ordered items, then to product details, and finally to shipment information.

SELECT c.name AS customer_name, o.order_date, p.product_name, s.ship_date
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
INNER JOIN shipments s ON o.id = s.order_id
WHERE c.state = 'California';

Approach-3: Using The Star Join Pattern

In this join pattern, a central fact table connects to multiple dimension tables.

Example

SELECT 
    ft.transaction_amount,
    d_date.quarter,
    d_customer.segment,
    d_product.category,
    d_location.region
FROM fact_transactions ft
INNER JOIN dim_date d_date ON ft.date_id = d_date.id
INNER JOIN dim_customer d_customer ON ft.customer_id = d_customer.id
INNER JOIN dim_product d_product ON ft.product_id = d_product.id
INNER JOIN dim_location d_location ON ft.location_id = d_location.id
WHERE d_date.year = 2023;

Approach-4: Using The Snowflake Join Pattern

You can also use the snowflake join pattern for this purpose.

Example

SELECT 
    p.name AS product_name,
    c.name AS category_name,
    d.name AS department_name
FROM products p
INNER JOIN categories c ON p.category_id = c.id
INNER JOIN departments d ON c.department_id = d.id
WHERE d.name = 'Electronics';

Best Practices for PostgreSQL Multiple Joins

Below are the best practices for multiple joins:

1. Use Aliases: Always use meaningful table aliases for readability
2. Include Join Conditions: Be explicit about join conditions to prevent accidental cross joins
3. Limit Data Early: Apply filtering conditions as early as possible in the join sequence
4. Test Performance: Use EXPLAIN ANALYZE to understand query execution
5. Consider Denormalization: For read-heavy analytical workloads, consider denormalizing or using materialized views

Conclusion

This article discusses PostgreSQL’s multiple joins. You can use the approaches mentioned in this article and the best practices while working with multiple joins in PostgreSQL.

You may also like the following articles.

• PostgreSQL Joins
• PostgreSQL Average
• PostgreSQL Add Primary Key Multiple Columns