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.