>> SELF JOIN in SQL

SELF JOIN in SQL

This is join of a  table with the same table itself.

Let's take an existing table EMPDATA as below:

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7839	KING	PRESIDENT		17-Nov-81	5000		10
7698	BLAKE	MANAGER	7839	1-May-81	2850		30
7782	CLARK	MANAGER	7839	9-Jun-81	800		10
7566	JONES	MANAGER	7839	2-Apr-81	2975		20
7788	SCOTT	ANALYST	7566	9-Dec-82	3000		20
7902	FORD	ANALYST	7566	3-Dec-81	3000		20
7369	SMITH	CLERK	7902	17-Dec-80	800		20
7499	ALLEN	SALESMAN	7698	20-Feb-81	800	300	30
7521	WARD	SALESMAN	7698	22-Feb-81	1250	500	30
7654	MARTIN	SALESMAN	7698	28-Sep-81	1250	1400	30
7844	TURNER	SALESMAN	7698	8-Sep-81	1500	0	30
7876	ADAMS	CLERK	7788	12-Jan-83	1100		20
7900	JAMES	CLERK	7698	3-Dec-81	1300		30
7934	MILLER	CLERK	7782	23-Jan-82	1300		10

Note: This table is taken as a reference from oracle website.

Now, let us find out the manager name of each employee having manager from above table. The table EMPDATA is given different aliases(A and B) so that the same table EMPDATA can be considered as two in the SQL script.

Note: A, B, EMP_NAME,  EMP_ENO, MGR_NAME, MGR_ENO    in below queries are aliases. Aliases are temporary names given to table or column. The aliases are given to make the columns name more relevant or readable or shorten the long names and to make them unique .  An alias exists only  until the duration of the SQL execution.

Select  A.Ename   AS   EMP_NAME ,  A.EMPNO  AS  EMP_ENO,  B.Ename  AS  MGR_NAME ,  B.EMPNO  AS  MGR_ENO  
from  empData  A ,  EMPDATA  B

WHERE

A.MGR = B.EMPNO;

The result is :

EMP_NAME	EMP_ENO	MGR_NAME	MGR_ENO
JONES	7566	KING	7839
CLARK	7782	KING	7839
BLAKE	7698	KING	7839
JAMES	7900	BLAKE	7698
TURNER	7844	BLAKE	7698
MARTIN	7654	BLAKE	7698
WARD	7521	BLAKE	7698
ALLEN	7499	BLAKE	7698
MILLER	7934	CLARK	7782
FORD	7902	JONES	7566
SCOTT	7788	JONES	7566
ADAMS	7876	SCOTT	7788
SMITH	7369	FORD	7902

NEXT

Go To Main SQL Page

.