My Ah-ha! Moment with Self-Joins

I was recently taking an online class in SQL from Oracle. I will admit that sometimes self-joins still baffle my mind. So I decided to try what they said.

Oracle's statement was:

"To join a table to itself, the table is given two names or aliases. This will make the database “think” that there are two tables. Choose alias names that relate to the data's association with that table."

So I decided to create two EMP tables in the Scott schema with just the information I needed.

create table e_emp as select empno, ename, mgr from emp;

create table m_emp as select empno, ename, mgr from emp;

Now it's very easy to see that 'MGR' in the 'E' table will have to connect to 'EMPNO' in the 'M' table to get the report I want.

So I'll code a very basic query from these two separate tables with the aliases 'E' and 'M' and will end up with the report shown below:

Select e.empno as Emp#, e.ename as Employee

     , m.empno as Mgr#, m.ename as Manager

  from e_emp e

     , m_emp m

 where e.mgr = m.empno;

But Oracle doesn't make me create two tables. I can run the same query listing EMP twice and get the same thing. The only thing to do is change the two table names 'e_emp' and 'm_emp' both to 'emp.'

Select e.empno as Emp#, e.ename as Employee

     , m.empno as Mgr#, m.ename as Manager

  from emp e

     , emp m

 where e.mgr = m.empno;