SQL Self JOIN

A self-join is a query in which a table is joined (compared) to itself. Self-joins are used to compare values in a column with other values in the same column in the same table. The SQL self-join can be done by using table aliases to cheat one table like a different table and then join them together.

SQL self-join can be any form of join such as inner join, outer join … so you can apply any join to the SQL self-join. SQL self-join is useful when you want to retrieve related data storing in one table such as organization structure.

In order to perform the operation of Self join we need to open the two copies of same table. Because the tables are same so we can use table alias to avoid confusion.

To find out the Manager of each employee.

Select w.ename || ‘works for’ || m.ename “Employees and their Managers” From emp w, emp m Where w.mgr = m.empno;
15 rows selected

On execution the result is shown as above in the query. The two different names for table are used as ‘w’ and ‘M’. We used two Aliases for different names. This query will return all employees and their respective managers whom under they work.