PostgreSQL Multiple Inner Joins

Mastering inner joins is essential for anyone working with relational databases. In this comprehensive article, I’ll share my expertise on implementing multiple inner joins in PostgreSQL.

PostgreSQL Multiple Inner Joins

Now, we will understand how multiple inner joins are implemented in Postgresql using the psql tool. In PostgreSQL, the INNER JOIN will select all rows from both tables as long as the condition is satisfied. This will create the result set by combining all rows from both tables where the condition is satisfied, i.e., the value of the common field will be the same.

Inner Join is the procedure of retrieving data from multiple tables based on a required condition. The necessary conditions are that there must be either common columns or matched columns between the two tables of the database, and that the data types of columns should be similar.

Syntax

Let’s check the syntax for applying multiple inner joins.

SELECT column_name1,column_name2,..
FROM table_name1
INNER JOIN 
table_name2
ON condition_1
INNER JOIN 
table_name3
ON condition_2
INNER JOIN 
table_name4
ON condition_3
.
.
.

Example

Now, we will learn multiple inner joins by creating an example. We will create three tables and then apply the inner joins to them. Let’s check the query for the first table.

CREATE TABLE students
( 
 stu_id varchar(10),
 stu_name varchar(20),
 course_id varchar(10),
 branch varchar(20),
 teacher_id varchar(10)
);
CREATE TABLE course
(
  course_id varchar(10),
  course_name varchar(20)
);
CREATE TABLE teachers
(
  teacher_id varchar(10),
  teacher_name varchar(20)
);

Now we will insert values in these tables.

INSERT INTO STUDENTS VALUES
('191','Smith','RY103','Ruby', 'P4002'),
('192','Olivia','AD101','Andriod', 'P4001'),
('193','Charles','JA104', 'Java','P4004'),
('194','William','PY102','Python', 'P4003');

INSERT INTO COURSE VALUES
('AD101', 'Andriod'),
('PY102', 'Python'),
('RY103', 'Ruby'),
('JA104', 'Java');

INSERT INTO TEACHERS VALUES
('P4001', 'Lucas'),
('P4002', 'William'),
('P4003', 'James'),
('P4004', 'Emma');

Let’s check the output as shown in the screenshot below.

Let’s execute the below query.

SELECT stu_id, stu_name,course.course_id,course.course_name,
teacher.teacher_name
FROM students  
INNER JOIN  
course  
ON  
students.course_id = course.course_id
INNER JOIN  
teachers  
ON  
students.teacher_id = teachers.teacher_id;

Let’s check the output. After executing the above query, I got the expected output as shown in the screenshot below.

Common Multiple Inner Join Patterns

There are several join patterns that appear frequently in business applications:

Star Join Pattern

This pattern, common in data warehousing, joins a central fact table to multiple dimension tables:

SELECT 
    ft.sales_amount,
    dd.date,
    dc.customer_name,
    dp.product_name,
    dl.location_name
FROM fact_sales ft
INNER JOIN dim_date dd ON ft.date_id = dd.date_id
INNER JOIN dim_customer dc ON ft.customer_id = dc.customer_id
INNER JOIN dim_product dp ON ft.product_id = dp.product_id
INNER JOIN dim_location dl ON ft.location_id = dl.location_id
WHERE dd.year = 2025;

Bridge Table Pattern

This pattern handles many-to-many relationships by joining through a bridge table:

SELECT 
    s.student_name,
    c.course_name,
    sc.enrollment_date
FROM students s
INNER JOIN student_courses sc ON s.student_id = sc.student_id
INNER JOIN courses c ON sc.course_id = c.course_id
WHERE c.department = 'Computer Science';

Best Practices

Below are the best practices that you should follow.

1. Use Meaningful Aliases: Choose clear, consistent table aliases
2. Comment Complex Queries: Document the purpose of complex joins for future reference
3. Test with Sample Data: Verify your joins work correctly with known test data
4. Consider Performance Early: Don’t wait until production to optimize join performance
5. Filter Early: Apply WHERE conditions as early as possible in the join sequence

Advanced Techniques for Multiple Inner Joins

Using Table Aliases for Better Readability

This practice significantly improves query readability, especially with multiple joins:

-- Much more readable with aliases
SELECT e.employee_name, d.department_name, p.position_title
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN positions p ON e.position_id = p.position_id;

Self-Joins: Connecting a Table to Itself

Sometimes you need to connect a table to itself—for example, we can use the following query.

SELECT 
    e.employee_name AS employee,
    m.employee_name AS manager
FROM employees e
INNER JOIN employees m ON e.manager_id = m.employee_id
ORDER BY m.employee_name, e.employee_name;

This query shows each employee alongside their manager by joining the employees table to itself.

Using Different Join Conditions

We can use the following query for this purpose.

SELECT 
    p.product_name,
    pr.price_amount
FROM products p
INNER JOIN pricing_tiers pr ON p.product_value BETWEEN pr.min_value AND pr.max_value
WHERE p.category = 'Electronics';

This example joins products to the appropriate pricing tier based on a value range rather than an exact match.

Conclusion

Multiple inner joins are a technique for working with normalized database designs. The approaches mentioned in this article can help you use various inner Joins in PostgreSQL.

You may also like the following articles.

• PostgreSQL multiple joins
• PostgreSQL Joins
• PostgreSQL Average