Oracle SQL - Table Join Self-Joins

Introduction

In SQL, you can join a table to itself. It is called self-join or autojoin.

Autojoins contain tables being referenced more than once in the FROM clause.

The following table shows an example of an autojoin.

The query produces an overview of all emp born after January 1, 1965, with a second column showing the name of their managers.

select e.ename as employee
,      m.ename as manager
from   emp m
,      emp e
where  e.mgr = m.empno
and    e.bdate > date '1965-01-01';

Demo

SQL>
SQL>-- w w w. j av a2 s  .  c  o m
SQL> drop table emp;

Table dropped.

SQL> create table emp(
  2  empno      NUMBER(4)    primary key,
  3  ename      VARCHAR2(8)  not null   ,
  4  init       VARCHAR2(5)  not null   ,
  5  job        VARCHAR2(8)             ,
  6  mgr        NUMBER(4)               ,
  7  bdate      DATE         not null   ,
  8  msal       NUMBER(6,2)  not null   ,
  9  comm       NUMBER(6,2)             ,
 10  deptno     NUMBER(2)    default 10) ;
SQL>
SQL> insert into emp values(7001,'SMITH','N',  'TRAINER', 7902,date '1975-12-17',  1800 , NULL, 20);
SQL> insert into emp values(7002,'ALLEN','JAM','SALESREP',7006,date '1971-05-20',  1600, 300,   30);
SQL> insert into emp values(7003,'WARD', 'TF' ,'SALESREP',7006,date '1972-03-02',  1250, 500,   10);
SQL> insert into emp values(7004,'JACK', 'JM', 'MANAGER', 7009,date '1977-04-02',  2975, NULL,  20);
SQL> insert into emp values(7005,'BROWN','P',  'SALESREP',7006,date '1976-09-28',  1250, 1400,  30);
SQL> insert into emp values(7006,'BLAKE','R',  'MANAGER', 7009,date '1973-11-01',  2850, NULL,  10);
SQL> insert into emp values(7007,'CLARK','AB', 'MANAGER', 7009,date '1975-06-09',  2450, NULL,  10);
SQL> insert into emp values(7008,'SCOTT','DEF','TRAINER', 7004,date '1979-11-26',  3000, NULL,  20);
SQL> insert into emp values(7009,'KING', 'CC', 'DIRECTOR',NULL,date '1972-10-17',  5000, NULL,  10);
SQL> insert into emp values(7010,'BREAD','JJ', 'SALESREP',7006,date '1978-09-28',  1500, 0,     30);
SQL> insert into emp values(7011,'ADAMS','AA', 'TRAINER', 7008,date '1976-12-30',  1100, NULL,  20);
SQL> insert into emp values(7012,'JONES','R',  'ADMIN',   7006,date '1979-10-03',  8000, NULL,  30);
SQL> insert into emp values(7902,'FORD', 'MG', 'TRAINER', 7004,date '1979-02-13',  3000, NULL,  20);
SQL> insert into emp values(7934,'MARY', 'ABC','ADMIN',   7007,date '1972-01-23',  1300, NULL,  10);
SQL>
SQL> select e.ename as employee
  2  ,      m.ename as manager
  3  from   emp m
  4  ,      emp e
  5  where  e.mgr = m.empno
  6  and    e.bdate > date '1975-01-01';

EMPLOYEE | MANAGER
-------- | --------
FORD     | JACK
SCOTT    | JACK
JONES    | BLAKE
BREAD    | BLAKE
BROWN    | BLAKE
ADAMS    | SCOTT
CLARK    | KING
JACK     | KING
SMITH    | FORD

9 rows selected.

SQL>