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:

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

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	#