PostgreSQL Self Join | PostgreSQL Tutorials and Solutions

PostgreSQL Self Join is joining a table to itself is called self join.

Self join is performed in tables having self referential integrity.
Self join is performed in tables foreign key and primary key belongs to same tables.
To perform PostgreSQL Self join, the same table must be listed in the query twice with different alias.

PostgreSQL Self Join Examples:

Tables used for below examples:

emp table:

1. Display employee names working under manager ‘BLAKE’.

select x.empno,x.ename,y.ename as manager from emp x, emp y
where x.mgr=y.empno
and
y.ename='BLAKE';

Output:

 empno | ename  | manager
-------+--------+---------
  7521 | WARD   | BLAKE
  7654 | MARTIN | BLAKE
  7499 | ALLEN  | BLAKE