PostgreSQL – SELF JOIN

PostgreSQL has a special type of join called the SELF JOIN which is used to join a table with itself. It comes in handy when comparing the column of rows within the same table. As, using the same table name for comparison is not allowed in PostgreSQL, we use aliases to set different names of the same table during self-join.

It is also important to note that there is no such keyword as SELF JOIN, but it is achieved with the help of LEFT JOIN, RIGHT JOIN or INNER JOIN using aliases.

Syntax:
SELECT column_list
FROM table_name T1
INNER JOIN table_name T2 ON join_predicate;

or,

Syntax:
SELECT column_list
FROM table_name T1
LEFT JOIN table_name T2 ON join_predicate;

or,

Syntax:
SELECT column_list
FROM table_name T1
RIGHT JOIN Table_name T2 ON join_predicate;

Let’s set up a sample database and table for the demonstration of self-join.