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.