Self Joins

Self joins are categories of joins where the join operation takes place within the similar table. Let consider a condition where can organization required a report of all the employee names along with their manager’s name. With performing this query, the manager number is available in the similar table under mgr column. Through equating the manager number with the employee number can be selected the records. In order to query from the similar table, a copy of the similar table is needed in the SELECT list to display records. Table aliasing is used to differentiate the two tables. The subsequent query described this concept.

Example

SELECT e1.ename ||'   works   for   '||e2.ename   FROM   emp   e1, emp   e2   WHERE e1.mgr=e2.empno; # to display the result e1 is matched for the corresponding record in table e2 and show #

Each record in table e1 is matched for the corresponding record in table e2. The records which match the condition are selected and displayed.