Self Join

A self join is a join with the same table. To use a self join, you must use a different table alias.


CREATE TABLE departments
(department_id             number(10)            not null,
 department_name           varchar2(50)      not null,
 CONSTRAINT departments_pk PRIMARY KEY (department_id)
);

insert into departments ( department_id, department_name )values(2,'Purchasing' );
insert into departments ( department_id, department_name )values(3,'Call Center' );
insert into departments ( department_id, department_name )values(4,'Communication' );

CREATE TABLE employees
( employee_id          number(10)      not null,
  last_name            varchar2(50)      not null,
  job_id               varchar2(30),
  department_id        number(10),
  salary               number(6),
  manager_id           number(6),
  CONSTRAINT           employees_pk PRIMARY KEY (employee_id),
  CONSTRAINT           fk_departments FOREIGN KEY (department_id) 
                       REFERENCES departments(department_id)
);
insert into employees( employee_id, last_name, job_id, salary,department_id ,manager_id)
values(                1001,        'Lawson',  'MGR',  30000, 1,             1004);
insert into employees( employee_id, last_name, job_id, salary, department_id ,manager_id)
values(                1002,        'Wells',   'DBA',  20000, 2,             1005 );
insert into employees( employee_id, last_name, job_id, salary, department_id ,manager_id)
values(                1003,        'Bliss',   'PROG', 24000, 3,             1004);
insert into employees( employee_id, last_name, job_id, salary, department_id, manager_id)
values(                1004,        'Kyte',    'MGR',  25000 ,4,             1005);
insert into employees( employee_id, last_name, job_id, salary, department_id, manager_id)
values(                1005,        'Viper',   'PROG', 20000, 1,             1006);
insert into employees( employee_id, last_name, job_id, salary, department_id,manager_id)
values(                1006,        'Beck',    'PROG', 20000, 2,             null);
insert into employees( employee_id, last_name, job_id, salary, department_id, manager_id)
values(                1007,        'Java',    'PROG', 20000, 3,             1006);
insert into employees( employee_id, last_name, job_id, salary, department_id, manager_id)
values(                1008,        'Oracle',  'DBA',  20000, 5,             1006);

SQL>
SQL> select e.employee_id, e.last_name
  2  from employees e, employees m
  3  where e.manager_id = m.employee_id;

EMPLOYEE_ID LAST_NAME
----------- --------------------------------
       1003 Bliss
       1007 Java

SQL>
Home »
Oracle »
Select » 

Join:
  1. Table Join
  2. Using Table name to reference duplicate names
  3. Table Alias
  4. Cartesian Products
  5. Join more than two tables
  6. Join Conditions and Join Types
  7. Outer Joins
  8. Left and Right Outer Joins
  9. Outer join Error
  10. Self Join
  11. Outer Self Join
  12. Inner Joins Using SQL/92
  13. Joins with USING Keyword
  14. Inner Joins with More than Two Tables Using SQL/92
  15. Inner Joins on Multiple Columns Using SQL/92
  16. Outer Joins in SQL/92 Syntax
  17. Self Joins Using SQL/92
  18. Cross Joins Using SQL/92
Related: