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:

SELECT column_list
FROM table_name Table1
INNER JOIN table_name Table2 ON join_predicate;

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:

SELECT column_list
FROM table_name Table1
LEFT JOIN table_name Table2 ON join_predicate;

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:

SELECT column_list
FROM table_name Table1
RIGHT JOIN Table_name Table2 ON join_predicate;

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

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.

The Organization database has been created successfully and display in Object tree as we can see in the below screenshot:

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:

CREATE TABLE Customer (
Customer_id INT PRIMARY KEY,
First_name VARCHAR NOT NULL,
Last_name VARCHAR NOT NULL,
Order_id INT,
FOREIGN KEY (Order_id) REFERENCES Customer (Customer_id) 
ON DELETE CASCADE
);

Now, we will insert some customer records into the Customer table with the help of INSERT as we can see in the below statement:

INSERT INTO Customer(Customer_id,First_name, Last_name,
Order_id )VALUES
(1, 'Mia', 'Rodriguez', NULL),
(2, 'Maria', 'Garcia', 1),
(3, 'James ', 'Johnson', 1),
(4, 'Michael', 'Smith', 2),  
(5, 'David', 'Smith', 2), 
(6, 'Jones', 'Smith', 2),
(7, 'Margaret', 'Brown', 3),
(8, 'Jane', 'Miller', 3),
(9, 'Catherine', 'Martinez', 4),
(10, 'Ana', 'Clark', 4);

After creating and inserting the values in the Customer table, we will get the following output on executing the below command:

Output

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:

SELECT
c.First_name || ' ' || o.Last_name Customer,
o.first_name || ' ' || o.last_name Orders
FROM
Customer c
INNER JOIN Customer o ON o. order_id = c.order_id
ORDER BY Orders;

Output

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:

SELECT
c.First_name || ' ' || o.Last_name Customer,
o.first_name || ' ' || o.last_name Orders
FROM
Customer c
LEFT JOIN Customer o ON o. order_id = c.order_id
ORDER BY Orders;

Output

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

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.

Next TopicPostgreSQL Natural Join

← prev next →