# How to Use SELF JOIN in PostgreSQL

> SELF JOIN is used in the PostgreSQL database to join a table to itself when tables are created in a hierarchy fashion to apply queries on it.

In relational databases like PostgreSQL, data can be distributed across multiple tables to ensure efficient organization. However, retrieving comprehensive information often requires fetching data from multiple tables. In that particular scenario, JOINs come in handy as they allow us to gain useful insights from the data stored in tables.

This guide will explain how to use SELF JOIN in PostgreSQL.

 **How to Use SELF JOIN in PostgreSQL?**

SELF JOIN allows us to join/merge a table with itself and it is needed when tables are created in a hierarchy fashion, to draw the details of the table. It is more of a strategy to join a table using its own reference and joining itself, table can use either of the JOIN like INNER, LEFT, etc. It uses tables with their alias name which is the temporary name given at the time of the query execution.

 **Syntax With INNER JOIN**

The following is the syntax for the Postgres SELF JOIN:
    
    
    SELECT <Columns>
     FROM <Table> <Alias1>
     INNER JOIN <Table> <Alias2> ON Alias1.column = Alias2.column;

In this code block:

\- The SELECT keyword selects one, multiple, or all columns of the table  
\- It starts the query at FROM keyword which contains the table name and its Alias name, which is a temporary name for the query  
\- An alias can be used to specify the table name and the column name of the table.  
\- The INNER JOIN contains the table name with an alias to join the column provided in the ON condition.

 **Syntax With LEFT JOIN**

SELF JOIN can also be used with LEFT JOIN via the following syntax:
    
    
    SELECT <Columns>
     FROM <Table> <Alias1>
     LEFT JOIN <Table> <Alias2> ON Alias1.column = Alias2.column;

The above snippet uses almost the same syntax as the previous one with a minor change, i.e. LEFT JOIN instead of INNER JOIN.

 **Example 1: SELF JOIN Using LEFT JOIN**

To start the example, use the following query to fetch the table created on the PostgreSQL database:
    
    
    SELECT * FROM orders;

The above query will return all the records of the “orders” table:

Use the following query to apply SELF JOIN on the orders table:
    
    
    SELECT o1.customer_id, o1.order_date
     FROM orders o1
       LEFT JOIN orders o2 
      ON o1.customer_id = o2.customer_id
      AND o1.order_date = o2.order_date
      AND o1.order_id <> o2.order_id;

The above query performs the following tasks:

\- It selects two columns **customer_id** and **order_date** from the orders table and assigns them their aliases.  
\- After that, it applies **LEFT JOIN** to the order table with the second alias.  
\- **ON** condition returns the customer_id and order_date with order by **order_id** column:

 **Example 2: SELF JOIN Using INNER JOIN**

Take another example using the employee table to use SELF JOIN with INNER JOIN, start by accessing the employee table using the given query:
    
    
    SELECT * FROM employee;

Running the above query will return the data from the employee table:

Use the following query to apply SELF JOIN on the employee table:
    
    
    SELECT
      a1.first_name || ' ' || a1.last_name employee,
      a2 .first_name || ' ' || a2 .last_name manager
       FROM
      employee a1
       INNER JOIN employee a2 ON a2 .employee_id = a1.manager_id
     ORDER BY manager;

Here in the above code block:

\- The **SELECT** query is used to fetch the data from the **first_name** and **last_name** columns of the **employee** table with their aliases assigned to them.  
\- **INNER JOIN** applies **SELF JOIN** on the employee table using the **ON** condition on the employee_id and manager_id.  
\- It adds to the condition using **ORDER BY** to display the result according to the **manager** hierarchy:

That’s all about using SELF JOIN in PostgreSQL.

 **Conclusion**

In PostgreSQL, SELF JOIN allows the user to apply SELF JOIN to the table using JOINs like INNER, LEFT, etc. to get the reference to itself. The **ON** condition can be used with the **INNER** and **LEFT** JOIN to apply **SELF JOIN** on the table to join a table using hierarchy distribution. The tables and columns used in the SELF JOIN should contain their **alias** names for the query execution. This guide has explained the use of SELF JOIN in the PostgreSQL tables.

---
[View this page online](https://www.commandprompt.com/education/how-to-use-self-join-in-postgresql/)