PostgreSQL is a renowned freely available relational database. In relational databases, data is distributed into various tables. To get detailed information, you might need to combine/merge the data of various tables. For this purpose, the Joins are used in databases. PostgreSQL supports various types of JOINS, such as “INNER JOIN”, “OUTER JOIN”, “NATURAL JOIN”, “SELF JOIN”, etc.

This post will specifically discuss the usage of the Postgres INNER JOIN using suitable examples.

How to Use INNER JOIN in Postgres?

In Postgres, the INNER JOIN is used to get the matching results from two or more tables based on a specific join condition.

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

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

Here, in the above syntax:

- “tab_1” and “tab_2” are the tables to be joined.
- The tables’ columns will be specified as “table_name.col_name”, i.e., “tab_1.col_name”, and “tab_2.col_name”.
- The “INNER JOIN” joins the given tables according to the specified JOIN condition.
- The “ON” clause defines the joining condition.

If the tables to be joined have the same column names, then the “USING” clause can be used instead of the “ON” clause to define a join condition:

SELECT col_list
FROM tab_1
INNER JOIN tab_2
USING (col_names);

Here, the “INNER JOIN” will join the selected tables based on the condition defined via the “USING” clause.

Example 1: INNER JOIN With ON Clause

We have already created a couple of sample tables, whose details are depicted in the following snippets:

SELECT * FROM employee_information;

Use the “SELECT *” command to get the detailed information regarding the “department_information” table:

SELECT * FROM department_information;

The “e_id” is a primary key in the “employee_information” table and the foreign key in the “department_information” table.

Let’s use the INNER JOIN to join the “employee_information” and “department_information” tables based on the “e_id”:

SELECT employee_information.e_id, 
e_name, e_email, dpt_name, dpt_id
FROM employee_information 
INNER JOIN department_information
ON employee_information.e_id = department_information.e_id;

The output snippet validates that the given tables have been successfully joined using the “INNER JOIN”.

Example 2: INNER JOIN With USING Clause

The “USING” clause can also be used along with the “INNER JOIN” to join/combine multiple tables. However, in such a case the column names should be the same in both tables:

SELECT employee_information.e_id, 
e_name, e_email, dpt_name, dpt_id
FROM employee_information 
INNER JOIN department_information
USING(e_id);

Here, the column name “e_id” is the same in both tables, so the given tables can be joined using the INNER JOIN and the USING clause:

The “employee_information” and “department_information” tables have been joined successfully using the “INNER JOIN”.

Example 3: Joining Three Tables Using the INNER JOIN

Suppose we have three tables: “emp_bio”, “dpt_info”, and “emp_details''. All these tables are linked with each other via the foreign key constraint. Let’s utilize the SELECT query to fetch the details of each table:

SELECT * FROM emp_bio;

In the “emp_bio” table, “e_id” is a primary key. Let’s execute the “SELECT” command one more time to fetch the results from the “dpt_info” table:

SELECT * FROM dpt_info;

In the “dpt_info” table, “dpt_id” is a primary key. The third sample table is “emp_details”, whose details are enlisted in the following snippet:

SELECT * FROM emp_details;

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

Let’s learn how to join three tables using the INNER JOIN:

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 the above code, the first INNER JOIN is used to join the “emp_bio” and “emp_details” tables on the basis of “e_id”. While the second INNER JOIN is used to join the emp_info table with the other two tables based on the “dpt_id”:

The output authenticates that the “emp_bio”, “dpt_info”, and “emp_details'' tables have been successfully joined.

Conclusion

In PostgreSQL, the INNER JOIN is used to get the matching results from two or more tables based on a specific join condition. The “ON” clause is used with the INNER JOIN to define a joining condition. If the tables to be joined have the same column names, then the “USING” clause can be used instead of the “ON” clause to define a join condition. This post has explained the usage of the INNER JOIN in PostgreSQL using practical examples.