Interview Questions on Self Join

We used to get below questions frequently on SELF JOIN concept.

1) Find out emp id who are managers.
2) Find out managers with count of sub ordinates.
3) Find out managers who are having more than one sub ordinate



we are going to take EMP table to show examples.

SQL> select * from emp;




EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7839 KING       PRESIDENT          11/17/1981    5000.00                  10
 7698 BLAKE      MANAGER    7839 5/1/1981      2850.00                   30
 7782 CLARK      MANAGER    7839 6/9/1981      2450.00                   10
 7566 JONES      MANAGER    7839 4/2/1981      2975.00                   20
 7788 SCOTT      ANALYST     7566 4/19/1987     3000.00                  20
 7902 FORD       ANALYST      7566 12/3/1981     3000.00                  20
 7369 SMITH      CLERK         7902 12/17/1980     800.00                   20
 7499 ALLEN      SALESMAN   7698 2/20/1981     1600.00    300.00     30
 7521 WARD       SALESMAN  7698 2/22/1981     1250.00    500.00     30
 7654 MARTIN     SALESMAN  7698 9/28/1981     1250.00   1400.00    30
 7844 TURNER     SALESMAN  7698 9/8/1981      1500.00      0.00       30
 7876 ADAMS      CLERK        7788 5/23/1987     1100.00                   20
 7900 JAMES      CLERK         7698 12/3/1981      950.00                    30
 7934 MILLER     CLERK         7782 1/23/1982     1300.00                   10



Case 1: Find out emp id who are managers.



SQL> select e1.empno, e1.mgr from emp e1 join emp e2 on e2.empno= e1.mgr;

EMPNO   MGR
----- -----
 7698  7839
 7782  7839
 7566  7839
 7788  7566
 7902  7566
 7369  7902
 7499  7698
 7521  7698
 7654  7698
 7844  7698
 7876  7788
 7900  7698
 7934  7782

If we observe empno in  above ouput , we didnt get "7839" as its President and He does not need to report to anyone.

If we want president ID also then we can use left join to get it as below.

SQL> select e1.empno, e1.mgr from emp e1 left join emp e2 on e2.empno= e1.mgr;

EMPNO   MGR
----- -----
 7839
 7698  7839
 7782  7839
 7566  7839
 7788  7566
 7902  7566
 7369  7902
 7499  7698
 7521  7698
 7654  7698
 7844  7698
 7876  7788
 7900  7698
 7934  7782
 
 Case 2: Find out managers with count of sub ordinates.

As we required count the sub ordinates so we will use aggregate  function ie. COUNT and we also know that whenever we use aggregate function its mandatory to use Group by clause for remaining  columns of select query.
 
SQL> select count(e1.empno), e1.mgr from emp e1 join emp e2 on e2.empno= e1.mgr group by e1.mgr;
COUNT(E1.EMPNO)   MGR
--------------- -----
              2  7566
              5  7698
              1  7782
              1  7788
              3  7839
              1  7902 

Case 3: Find out managers who are having more than one sub ordinate


 Since we required to find more than one sub ordinate so we need to use having clause as below.
SQL> select count(e1.empno), e1.mgr from emp e1 join emp e2 on e2.empno= e1.mgr group by e1.mgr having count(e1.empno) >1;
 
COUNT(E1.EMPNO)   MGR
--------------- -----
              2  7566
              5  7698
              3  7839


 

Hierarchical queries

Hierarchical query is a type of SQL query which is commonly used to get meaningful results from  hierarchical data.

Hierarchical data means table columns which are related to each other by hierarchical relationships.

Hierarchical Relationship exist when one column data is parent to another column data.


In Our real life, Below would be hierarchical relations.

1) Employee and Manager Hierarchy
2)  Organization Hierarchy

Below clauses we need to know to write Hierarchical queries.



START WITH: It specifies the root rows of the hierarchy.

Connect by: It specifies the relationship between parent rows and child rows of the hierarchy.

Prior: We use this keyword to specify parent row.

In Hierarchical queries,One expression in condition must be specified with PRIOR to refer Parent row.
eg:

… PRIOR expr = expr
or
… expr = PRIOR expr

Level: It refers to a pseudo column in Oracle which is used in a hierarchical query to identify the hierarchy level in numeric format.


A database evaluates hierarchical queries in the following order:

•     A join, if present, is evaluated first, whether the join is specified in the FROM clause or with WHERE clause predicates

•     The CONNECT BY condition is evaluated

•     Any remaining WHERE clause predicates are evaluated

The database engine then uses the information from these evaluations to form the hierarchy using the following steps (referred from Oracle 10g documentation):

1) It selects the root row(s) of the hierarchy, meaning those rows that satisfy the START WITH condition.

2) It selects the child rows of each root row. Each child row must satisfy the condition of the CONNECT BY condition with respect to one of the root rows.

3) It selects successive generations of child rows. The database engine first selects the children of the rows returned in step 2, and then the children of those children, and so on. The database always selects children by evaluating the CONNECT BY condition with respect to a current parent row.

4) If the query contains a WHERE clause without a join, then the database eliminates all rows from the hierarchy that do not satisfy the condition of the WHERE clause. The database evaluates this condition for each row individually, rather than removing all the children of a row that does not satisfy the condition.

5) The database returns the rows in the order shown in Figure 1 below. In the diagram, children appear below their parents.


lets take EMP table to write examples.

case1: If we want to know relation between empid and manager.

SQL> select e1.empno,e1.mgr manager from emp e1
  2  connect by mgr = prior empno
  3  start with mgr is null
  4  /
EMPNO MANAGER
----- -------
 7839
 7566    7839
 7788    7566
 7876    7788
 7902    7566
 7369    7902
 7698    7839
 7499    7698
 7521    7698
 7654    7698
 7844    7698
 7900    7698
 7782    7839
 7934    7782

Here we used start with mgr is null as we would like to see CEO empid as well but he does not report to anyone.

and same kind of output we can get with below query as well but here we will not have output in hierarchical relation. so  we can compare both above and below output to understand difference.

SQL> select e1.empno, e1.mgr from emp e1 left  join emp e2 on e2.empno= e1.mgr;
 
EMPNO   MGR
----- -----
 7839
 7698  7839
 7782  7839
 7566  7839
 7788  7566
 7902  7566
 7369  7902
 7499  7698
 7521  7698
 7654  7698
 7844  7698
 7876  7788
 7900  7698
 7934  7782

Case 2: We need to get level for each hierarchy.

SQL> select e1.empno,e1.mgr manager ,level, decode(level,1,'CEO',2,'Director',3,'Manager',4, 'Employee') from emp e1
  2  connect by mgr = prior empno
  3  start with mgr is null;


EMPNO MANAGER      LEVEL DECODE(LEVEL,1,'CEO',2,'DIRECT
----- ------- ---------- --------------------------------
 7839                  1 CEO
 7566    7839          2 Director
 7788    7566          3 Manager
 7876    7788          4 Employee
 7902    7566          3 Manager
 7369    7902          4 Employee
 7698    7839          2 Director
 7499    7698          3 Manager
 7521    7698          3 Manager
 7654    7698          3 Manager
 7844    7698          3 Manager
 7900    7698          3 Manager
 7782    7839          2 Director
 7934    7782          3 Manager

case 3: generate Sequential values.


SQL> select level from dual
  2  connect by level <=5
  3  ;
     LEVEL
----------
         1
         2
         3
         4
         5

You can change value 5 and generate up to whichever value you want.