Self join

A self join is a join in which a table is joined with itself. For example, when you require details about an employee and his manager (also an employee).

Examples

Oracle join syntax (with SCOTT's tables):

SCOTT> SELECT e1.ename||' works for '||e2.ename "Employees and their Managers" 
  2    FROM  emp e1, emp e2
  3    WHERE e1.mgr = e2.empno;

Employees and their Managers
-------------------------------
FORD works for JONES
SCOTT works for JONES
TURNER works for BLAKE
ALLEN works for BLAKE
WARD works for BLAKE
JAMES works for BLAKE
MARTIN works for BLAKE
MILLER works for CLARK
ADAMS works for SCOTT
BLAKE works for KING
JONES works for KING
CLARK works for KING
SMITH works for FORD

ANSI join syntax (Oracle 9i and above):

SELECT e1.ename||' works for '||e2.ename "Employees and their Managers" 
FROM emp e1 JOIN emp e2 ON e1.mgr = e2.empno;

Also see

• Cartesian join
• Equi join
• Inner join
• Natural join
• Nonequi join
• Outer join

Glossary of Terms
A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	#