Question:

What is Self Join and why is it required?

Answer:

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.


Keywords:

© 2017 QuizBucket.org