PostgreSQL INNER Join

In this section, we are going to understand the working of PostgreSQL inner join, which is used to select data from many tables. We also learn how to use WHERE clause, USING clause, operators, and join three tables, table-aliasing in PostgreSQL inner join.

What is PostgreSQL INNER JOIN clause?

In a relational database, data is naturally spread in more than one table, and to select aggregate data, we often need to select data from various tables.

The PostgreSQL Inner Join is used to return only those records from the tables, which are equivalent to the defined condition and hides other rows and columns. In PostgreSQL, it as a default Join, therefore it is not compulsory to use the Inner Join keyword with the query.

The following Venn diagram displays the PostgreSQL inner join where we can easily understand that the Inner Join returns only the matching records from Table1 and Table2:

PostgreSQL Inner Join Syntax

The Inner Join keyword is used with the SELECT command and must be written after the FROM clause.

The below syntaxes describe it more clearly:

Syntax of PostgreSQL Inner Join with USING clause

Syntax of PostgreSQL inner Join with WHERE Clause

We will follow the below steps to join Table A with Table B:

• Firstly, we will define the column list from both tables (tables 1 and 2), where we want to select data in the SELECT condition.
• Then, we will define the base table, which is table 1 in the FROM clause.
• And lastly, we will describe the second table (table 2) in the INNER JOIN condition, and write the join condition after the ON keyword.

Note: The Join condition returns the similar rows between the tables described in the Inner condition.

Example of PostgreSQL Inner join

Let us see an example to understand how the PostgreSQL inner join works:

To join two tables by using PostgreSQL INNER JOIN

For this, we will create two tables named Employee and department table with the help of the CREATE command and inserting some values by using the INSERT command.

Firstly, we are going to create an Employee and department tables by using the CREATE command:

The below command is used to create a Department table:

The Employee and department tables have been successfully created after executing the above commands.

Once the both the tables have been generated, we are ready to insert some values into it by using the INSERT command as follows:

In the below command, we are inserting the values in the department table:

After creating and inserting the values in the Employee and department table, we will get the following output:

Table1: Employee

Table2: department

The below query is used to select records from both tables (Employee and department):

Output

Once we implemented the above command, we will get the below result:

Working of PostgreSQL inner join

• The Inner Join is used to analyze each row in Table1 (Employee) and compare the values in the emp_id column with value in the dept_id of every row in Table2 (department).
• If these records are similar, then the inner Join creates a new row containing columns from both tables and enhances the particular row into the output.
• Or if these records are not similar, then the inner Join disregards them and moves to the next row.

Table-aliasing with PostgreSQL inner Join

Generally, the tables we want to join will have columns with a similar name like the emp_id column.

If we reference columns with a similar name from different tables in a command, the error will have occurred, and to avoid this particular error, we need to use the below syntax.

In real-time, we will use table aliases to assign the joined tables short names to make the command more understandable.

In the below command, we will use the table aliasing, and it returns the similar outcome as above:

Output

Once we implemented the above command, we will get the below output:

PostgreSQL inner join with USING Clause

Here, we will see how the PostgreSQL inner join works with the USING clause because sometimes the name of the columns is similar in both the tables; that?s why we can use the USING clause to get the values.

In the below example, we are using the USING clause as both tables have a similar emp_id column.

Output

After executing the above statement, we will get the below result:

PostgreSQL inner join using WHERE clause

We can also use the inner join with a WHERE condition. The WHERE clause allows us to return the filter outcome.

In the below example, we will select rows from both tables Employee and department where dept_name is equal to Sales:

Output

After successful execution of the above command, it will give the below output:

To join three tables using PostgreSQL INNER JOIN

In the above section of this tutorial, we have already created two tables as Employee and department. Now, we want to join one more table and get the records from that particular table with the help of the Inner join.

So, for this, we will create one more table as Jobs by using CREATE command as we can see in the following command:

Once the Jobs table has been created successfully, we will insert some values into it with the help of INSERT command as we can see in the following command:

After creating and inserting the values in Jobs table, we will get the following output:

Table3: Jobs

The below command is used to join the three tables, such as Employee, department, and Jobs. So, we will use the second INNER JOIN clause after the first INNER JOIN clause:

Output

Once we implemented the above query, we will get the below result:

Note: To join more than three tables, we can use the above method.

PostgreSQL Inner Join using Operators

PostgreSQL allows many operators, which we can use with Inner Join, like equal (=), not equal (!=), greater than (>), less than (<), etc.

In the below example, we are using the not equal (!=) operator with inner join:

Output

Once we click on the Execute button, we will get the following output that displays those values whose dept_name is not equal to SALES.

Overview

In the PostgreSQL inner join section, we have learned the following topics:

• We used the PostgreSQL inner join clause to select data from two tables or more than two tables.
• We used the PostgreSQL inner join condition with WHERE clause, USING clause, Operators, and table-aliasing.