From Oracle FAQ
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).
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;
|Glossary of Terms|