SQL Self Join

SQL Self Join allows the programmer to join a table to itself. It is useful for querying hierarchical data or comparing rows within the same table.

Syntax

SELECT column_name(s)
FROM table1 Tbl1, table1 Tbl2
WHERE condition;

Tbl1 and Tbl2 are different table aliases for the same table.

Consider the below EMPLOYEES table where the employer and manager information is stored in the same table.

The below query can be used to retrieve the employee details along with their manager details

SELECT

tbl1.first_name as employee,

tbl2.first_name as manager

FROM

employees tbl1

INNER JOIN employees tbl2 ON tbl1.employee_id = tbl2.manager_id;

Output: