Self Join is a join of a table to itself. The same table appears twice in the FROM clause and is followed by table aliases. The table alias must qualify the column names in the join condition.
SELECT D1.Deptno, D1.Dname, D1.Loc, D2.Deptno, D2.Dname, D2.Loc FROM Dept D1, Dept D2 WHERE D1.Deptno = D2.Deptno; DEPTNO DNAME LOC DEPTNO DNAME LOC ---------- -------------- ------------- ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 20 RESEARCH DALLAS 30 SALES CHICAGO 30 SALES CHICAGO 40 OPERATIONS BOSTON 40 OPERATIONS BOSTON
The above is a self-join, but what is the use of this type of joins? Does joining the same table with equi join condition seems any valid purpose? Answer to those is clearly “NO”. Whatever you did with above self join, can be done without a join too by querying the same table only once. For example ” SELECT * FROM Dept
” would give you the same rows like the above self-join.
So, let’s identify few scenarios and proper use cases, where self-joins undoubtedly play a great role.
Q : Find the employees name and their manager’s name?
Ans:
SELECT Employee.Ename "Employees", Manager.Ename "Managers" FROM Emp Employee, Emp Manager WHERE Manager.Empno = Employee.Mgr; Employees Managers ---------- ---------- SMITH FORD ALLEN BLAKE WARD BLAKE JONES KING MARTIN BLAKE BLAKE KING CLARK KING SCOTT JONES TURNER BLAKE ADAMS SCOTT JAMES BLAKE FORD JONES MILLER CLARK 13 rows selected.
You can add more info to the above by getting more details about employee and their managers.
Q : Find the employees name, salary, department and their manager’s name, salary and department for employees who are working in department 30?
Ans:
SELECT Employee.Ename "Name", Employee.Sal "Emp Salary", Employee.Deptno "Emp Department", Manager.Ename "Manager", Employee.Sal "Manager Salary", Employee.Deptno "Manager Department" FROM Emp Employee, Emp Manager WHERE Manager.Empno = Employee.Mgr AND Employee.Deptno = 30; Name Emp Salary Emp Department Manager Manager Salary Manager Department ---------- ---------- -------------- ---------- -------------- ------------------ WARD 1250 30 BLAKE 1250 30 ALLEN 1600 30 BLAKE 1600 30 JAMES 950 30 BLAKE 950 30 MARTIN 1250 30 BLAKE 1250 30 TURNER 1500 30 BLAKE 1500 30 BLAKE 2850 30 KING 2850 30 6 rows selected.
Q : Get the output in following format for all employees in Emp table?
“Employee” works under “Manager” and earns basic – 999 $ and allowances – 99 $
Ans:
SELECT RPAD(e.Ename, 10) || ' works under ' || m.Ename || ' and earns basic - ' || e.Sal || '$' || ' and allowances - ' || nvl(e.Comm, 0) || '$' AS "Employee Details" FROM Emp e, Emp m WHERE m.Empno = e.Mgr; Employee Details ---------------------------------------------------------------------------------- FORD works under JONES and earns basic - 3000$ and allowances - 0$ SCOTT works under JONES and earns basic - 3000$ and allowances - 0$ ALLEN works under BLAKE and earns basic - 1600$ and allowances - 300$ JAMES works under BLAKE and earns basic - 950$ and allowances - 0$ TURNER works under BLAKE and earns basic - 1500$ and allowances - 0$ MARTIN works under BLAKE and earns basic - 1250$ and allowances - 1400$ WARD works under BLAKE and earns basic - 1250$ and allowances - 500$ MILLER works under CLARK and earns basic - 1300$ and allowances - 0$ ADAMS works under SCOTT and earns basic - 1100$ and allowances - 0$ BLAKE works under KING and earns basic - 2850$ and allowances - 0$ CLARK works under KING and earns basic - 2450$ and allowances - 0$ JONES works under KING and earns basic - 2975$ and allowances - 0$ SMITH works under FORD and earns basic - 800$ and allowances - 0$ 13 rows selected.
Q : Find the employees name, salary, designation and their manager’s name, salary and designation for all employees who are earning more than their managers?
Ans:
SELECT e.Ename "Employees", e.Sal "Emp Salary", e.Job "Emp Job", m.Ename "Managers", m.Sal "Mgr Salary", m.Job "Mgr Job" FROM Emp e, Emp m WHERE m.Empno = e.Mgr AND e.Sal > m.Sal; Employees Emp Salary Emp Job Managers Mgr Salary Mgr Job ---------- ---------- --------- ---------- ---------- --------- FORD 3000 ANALYST JONES 2975 MANAGER SCOTT 3000 ANALYST JONES 2975 MANAGER
While dealing with Self joins, column alias has its own importance as it helps distinguish the displayed columns.
SELECT e.Ename "Employees", e.Job "Employee Designation", m.Ename "Managers", m.Job "Manager Designation" FROM Emp e, Emp m WHERE m.Empno = e.Mgr AND e.Job = 'MANAGER' AND e.Deptno <> m.Deptno; Employees Employee Designation Managers Manager Designation ---------- ------------------------- ---------- ------------------------- BLAKE MANAGER KING PRESIDENT JONES MANAGER KING PRESIDENT
Since, you deal with same table, same columns in a self join, you must be very careful while naming column alias/ table alias. If alias names are not given properly the result set would seem erroneous.
--Incorrect column alias provided and gives incorrect output SELECT e.Ename "Employees", e.Job "Manager Designation", m.Ename "Managers", m.Job "Employee Designation" FROM Emp e, Emp m WHERE m.Empno = e.Mgr AND e.Job = 'MANAGER' AND e.Deptno <> m.Deptno; Employees Manager Designation Managers Employee Designation ---------- ------------------------- ---------- ------------------------- BLAKE MANAGER KING PRESIDENT JONES MANAGER KING PRESIDENT
In all examples explained above where we were fetching details for all employees, we see the result has 13 rows. However, Emp table has 14 rows. So, where is the missing row? Why the row is not part of the output?
We explain this in our next chapter.