PostgreSQL offers a concept of JOINS to get the data from multiple tables. In Postgres, there are several types of joins, such as inner join, outer join, left outer join, right outer join, full outer join, cross join, and natural join. Among them, the most frequently used is INNER join.

This blog will demonstrate the usage of the INNER joins through practical examples. So, let’s start.

What is the Need for JOINS in Postgres?

Use joins if there are some common attributes between multiple tables. If we have only one table, then we can fetch its records via a select statement. However, if two or more tables depend on each other and we have to fetch the common data from such tables, then we have to use the JOINS.

For example, we have two tables: employee_info with three columns employee_id, employee_name, employee_email and department_info with three columns department_id, department_name, employee_id. Now, if we have to fetch the employee_name and his department, then in such a case, we will use a Join.

How to Use INNER JOIN Clause in Postgres?

Postgres provides an INNER JOIN clause to combine the records of multiple tables based on a specific condition/criteria. It retrieves all records with common/matching values in both tables.

Syntax

The “INNER JOIN” Clause is used with the “ON” clause to combine two tables via the inner JOIN in Postgres:

SELECT col_list
FROM tab_1 
INNER JOIN tab_2
ON tab_1.column = tab_2.column;

In the above snippet:

- col_list represents the columns to be selected.
- tab_1 and tab_2 represent the tables to be joined.
- “tab_1.column = tab_2.column” represents the matching/common columns.

PostgreSQL's INNER JOIN retrieves the records where table1 intersects table2. In simple terms, the INNER join returns the matching/common values from the targeted tables.

The best way to comprehend a concept is to implement it practically. So, let’s do it.

Example: How to Combine Two Tables Via INNER Join in Postgres?

A step-by-step guide on joining different tables via the INNER JOIN clause is presented in this section:

Step 1: Create Sample Tables

Firstly, we will create a couple of sample tables named “employee_info” and “department_info”. After that, we will insert some records into the newly created tables:

CREATE TABLE employee_info( 
e_id INT PRIMARY KEY, 
e_name VARCHAR NOT NULL, 
e_email VARCHAR(50) NOT NULL
);

The “CREATE TABLE” message in the output proves that the “employee_info” table has been created successfully. Let’s create one more sample table:

CREATE TABLE department_info(
dpt_id INT NOT NULL, 
dpt_name VARCHAR NOT NULL,
e_id INT,
CONSTRAINT fk_employee
FOREIGN KEY(e_id) 
REFERENCES employee_info(e_id)
);

The “department_info” table has been created successfully.

Step 2: Insert Data

Let’s insert employees' information and departments' information into the respective tables:

INSERT INTO employee_info(e_id, e_name, e_email)
VALUES (1, 'Mike', 'mike@abc.com'),
(2, 'John', 'johm@abc.com'),
(3, 'Ambrose', 'ambrose@abc.com'),
(4, 'Seth', 'seth@abc.com'),
(5, 'Joe', 'joe@abc.com'),
(6, 'Kane', 'kane@abc.com');

Six records have been inserted into the employee_info table successfully. Let’s insert the department information into the department_info table:

INSERT INTO department_info(dpt_id, dpt_name, e_id)
VALUES (1, 'Writing Department', 1),
(2, 'Video Editing Department', 2),
(3, 'HR Department', 3),
(2, 'Video Editing Department', 4),
(1, 'Writing Department', 5),
(1, 'Writing Department', 6);

All the records have been inserted into the department_info table.

Step 3: Verify/Check Table’s Data

To check the “employee_info” data, we will execute the select command as follows:

SELECT * FROM employee_info;

Now we will check the “department_info” records via the SELECT statement:

SELECT * FROM department_info;

The SELECT statement retrieves all the data of the “department_info” table.

Step 4: Join Tables

Suppose we want to fetch the employees' data along with their respective departments. For this purpose, we will use the INNER Join as follows:

SELECT employee_info.e_id, 
e_name, e_email, dpt_name
FROM employee_info 
INNER JOIN department_info
ON employee_info.e_id = department_info.e_id;

The output proves that the INNER Join retrieves the data from the targeted tables.

Conclusion

Postgres provides an INNER JOIN clause to combine the records of multiple tables based on a specific condition/criteria. The “INNER JOIN” Clause is used with the “ON” clause to combine two tables via the inner JOIN in Postgres. Postgres' INNER JOIN retrieves the records where table1 intersects table2. In simple terms, the INNER join returns the matching/common values from the targeted tables. This post demonstrated the usage of INNER JOIN using suitable examples.