PostgreSQL Self Join
In this section, we are going to understand the working of PostgreSQL Self joins, which is used to relate rows within the same table. We also learned how to get the hierarchical data from similar table with the help of the PostgreSQL Self join clause.
What is PostgreSQL Self Join?
In PostgreSQL, we have one particular type of join, which is known as Self Join. The "PostgreSQL Self Join is used to set the different names of a similar table completely, and we can use the aliases also.
To proceed a self-join, we will define a similar table two times with different table aliases and give the Join predicate after the ON keyword.
In real-time, we use a self-join to compare rows within the same table (because the comparison of similar table name is not allowed in PostgreSQL), and to fetch the hierarchical data.
Note: There is no such keyword as Self Join; however, we can use the PostgreSQL Inner Join, Left Join, Right Join with the help of aliases.
PostgreSQL Self Join Syntax
In PostgreSQL, we have different syntaxes for Self-Join, which are as follows:
In the below syntax, we use an Inner Join Keyword, which combines the table to itself:
In the above syntax, the table_name is combined with the help of the PostgreSQL INNER JOIN clause.
In the below syntax, we use the Left Join Keyword, which combines the table to itself:
In the above syntax, the table_name is combined itself with the help of the PostgreSQL LEFT JOIN clause.
In the below syntax, we use the Right Join Keyword, which combines the table to itself:
In the above syntax, the table_name is combined itself with the help of the PostgreSQL RIGHT JOIN clause.
Example of PostgreSQL Self join
Let us see an example to understand how the PostgreSQL Self join works:
Example of fetching the Hierarchical Records from a table
For this, we will create one sample database then creates one table named Customer with the help of the CREATE command and insert some values using the INSERT command.
Firstly, we will create one sample database as we created in the earlier section of the PostgreSQL tutorial or refer to the below link to understand, how we create a database in the PostgreSQL:
Now, we will follow the below process, to create a database in pgadmin4:
Databases → Create → Database
After the successful creation Origination database, we will create the Customer table by using the CREATE command as we can see in the below statement:
Now, we will insert some customer records into the Customer table with the help of INSERT as we can see in the below statement:
After creating and inserting the values in the Customer table, we will get the following output on executing the below command:
In the Customer table, the Order_id column references the Customer_id column. The value in the Order_id column displays the Order that is purchased by the Customer.
The customer does not purchase anything if the value is Null in the Order_id column.
As we can see in the following image that the overall hierarchy looks like this:
In the below example, we will fetch the data of who purchase with the help of Self Join in the Customer table:
After implementing the above command, we will get the following result:
As we can observe in the above output table, the Customer table is executed two times, one as the Customer, and another as the Orders.
In the above command, we use the table aliases such as c for the customer and o for the Orders.
And the join predicate identifies Customer/Orders pair with the help of similar values in the Customer_id and Orders_id columns.
Note: The customer (Mia Rodriguez) whose Order_id is Null does not appear on the outcome.
We will use the LEFT JOIN in its place of INNER JOIN clause for including the top Order in the Output table using the following command:
After executing the above command, we will get the following output:
In the PostgreSQL Self Join section, we have learned the following topics: