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:
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:
The below query is used to select records from both tables (Employee and department):
Once we implemented the above command, we will get the below result:
Working of PostgreSQL inner join
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:
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.
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:
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:
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:
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:
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.
In the PostgreSQL inner join section, we have learned the following topics: