Oracle 19c SQL

  1. Home
  2. Docs
  3. Oracle 19c SQL
  4. 4 Joins in Oracle
  5. 4.6 Self Join

4.6 Self Join

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.

Was this article helpful to you? Yes No

How can we help?