PostgreSQL is a famous relational database that is freely available for all operating systems like Windows, Linux, and MacOS. In relational databases, different tables are linked with each other using various constraints. To get the related information from different tables, you might need to merge various tables. To do that, the Joins can be used in databases. PostgreSQL supports various types of JOINS, such as “INNER JOIN”, “OUTER JOIN”, “NATURAL JOIN”, “SELF JOIN”, etc.

This post will explain how to use the Postgres INNER JOIN to merge three different tables.

How to Join Three Tables in PostgreSQL?

Postgres uses the INNER JOIN to get matching results from two or more tables based on the defined join conditions.

Use the below syntax to combine multiple tables using “INNER JOIN”:

SELECT <tab_1.col_names >, <tab_2.col_names>, <tab_3.col_names>
FROM <tab_1>
INNER JOIN <tab_2>
ON <table_1.col_name> = <tab_2.col_name>;
INNER JOIN <tab_3>
ON <tab_3.col_name> = <tab_2.col_name>;

Here, in the above syntax:

- “tab_1”, “tab_2”, and “tab3” represents the tables to be joined.
- To avoid ambiguity, the tables’ columns must be specified with the table name, such as “tab_1.col_name”, “tab_2.col_name”, and tab_3.col_name.
- The “INNER JOIN” joins the given tables on the basis of the defined JOIN condition.
- The “ON” clause specifies the joining condition.

Example: Joining Three Tables in Postgres

Suppose we have already created three sample tables named “emp_bio”, “dpt_info”, and “emp_details''. All three tables are connected via the foreign key constraint.

Step 1: Get Tables’ Data

Let’s type the “SELECT *” command to fetch all the tables:

SELECT * FROM emp_bio;

The “e_id” column is defined as a primary key in the “emp_bio” table.

Now, run the “SELECT” command to fetch the results from the “dpt_info” table:

SELECT * FROM dpt_info;

The “dpt_id” column is defined as a primary key in the “dpt_info” table.

The final table is “emp_details” which contains the following records:

SELECT * FROM emp_details;

In the “emp_details” table, the “e_id” and “dpt_id” are foreign keys.

Step 2: Join Three Tables’ Data

Now use the INNER JOIN to combine the three given tables:

SELECT emp_bio.e_name, emp_bio.emp_name, 
dpt_info.dpt_name, emp_salary
FROM emp_bio
INNER JOIN emp_details
ON emp_bio.e_id = emp_details.e_id
INNER JOIN dpt_info
ON dpt_info.dpt_id = emp_details.dpt_id;

In this example:

- The SELECT statement specifies the columns to be fetched from all three tables.
- The first INNER JOIN is utilized to merge the “emp_bio” and “emp_details” tables based on the “e_id” column.
- The second INNER JOIN is utilized to combine the emp_info table with the other two tables based on the “dpt_id” column:

The output demonstrates that the given three tables have been joined successfully.

Conclusion

Postgres uses the INNER JOIN to get matching results from two or more tables based on the defined join conditions. The “ON” clause is utilized with the INNER JOIN to define a joining condition. To avoid ambiguity, the tables’ columns must be specified with the table name. This article has explained how to use the INNER JOIN to combine three different tables in PostgreSQL.