Self Join is the act of joining one table with itself.
Self Join is often very useful to convert a hierarchical structure into a flat structure
In our employee table example above, we have kept the manager ID of each employee in the same row as that of the employee. This is an example of how a hierarchy (in this case employee-manager hierarchy) is stored in the RDBMS table. Now, suppose if we need to print out the names of the manager of each employee right beside the employee, we can use self join. See the example below:
SELECT e.name EMPLOYEE, m.name MANAGER FROM EMPLOYEE e, EMPLOYEE m WHERE e.mgr_id = m.id (+)
| EMPLOYEE | MANAGER |
|---|---|
| Pete | Hash |
| Darl | Hash |
| Inno | Hash |
| Robo | Hash |
| Tomiti | Robo |
| Anno | Robo |
| Privy | Robo |
| Meme | Pete |
| Bhuti | Tomiti |
| Hash |
The only reason we have performed a left outer join here (instead of INNER JOIN) is we have one employee in this table without a manager (employee ID = 1). If we perform inner join, this employee will not show-up.