SQL Self Join

A self join is one the join in which a table is joined with itself (is called as Unary relationships),  To join a table itself means that each row of the table is combined with itself and with every other row of the table. The syntax of the command for joining a table to itself is almost same as that for joining two different tables with aliases.

Table name aliases are defined in the FROM clause of the SELECT statement. See the syntax:

 

Example of SQL SELF JOIN

In the following example we will use the table EMPLOYEE twice and in order to do this we will use the alias of the table.

Following Records are available in Employee Table.

To get the list of employees and their TL and PL the following sql statement has used :

Select E1.Employee_id, E1.First_name , TL.Employee_id, TL.First_name , PL.Employee_id, Pl.First_name from Employee E1  left join Employee TL on TL.Employee_id = e1.TL  left join Employee PL on PL.Employee_id = e1.PL

Outputs of the SQL statement shown here…