Javatpoint Logo
Javatpoint Logo

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:

Syntax1

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.

Syntax2

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.

Syntax3

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:

https://www.javatpoint.com/postgresql-create-database

Now, we will follow the below process, to create a database in pgadmin4:

  • We will open the pgAdmin in our local system and then in the Object tree, and we will right-click on the Databases and select Create then select database.

Databases → Create → Database

PostgreSQL Self Join
  • After that, the create database window will open where we need to provide some necessary details (Database name, Comment) for creating a database and then click on the Save button.
PostgreSQL Self Join
  • The Organization database has been created successfully and display in Object tree as we can see in the below screenshot:
PostgreSQL Self Join

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:

Output

PostgreSQL Self Join

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:

PostgreSQL Self Join

In the below example, we will fetch the data of who purchase with the help of Self Join in the Customer table:

Output

After implementing the above command, we will get the following result:

PostgreSQL Self Join

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:

Output

After executing the above command, we will get the following output:

PostgreSQL Self Join

Overview

In the PostgreSQL Self Join section, we have learned the following topics:

  • The PostgreSQL Self Join is Special Join, where we can also get the hierarchical data within the same table.
  • We used the PostgreSQL Self join clause to combine a table itself with the help of Inner Join and Left Join clauses.





Youtube For Videos Join Our Youtube Channel: Join Now

Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA