Oracle SQL - Table Join JOIN Clause

  1. HOME
  2. Oracle SQL
  3. Table Join
  4. JOIN Clause

Introduction

The following code shows an equivalent query using JOIN and ON.

The join is fully specified in the FROM clause and the WHERE clause contains only the filtering (i.e., the non-join) condition. 


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

Demo

SQL>
SQL>-- from  w  ww  . ja v a2s  .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>
SQL> select e.ename as employee
  2  ,      m.ename as manager
  3  from   emp m
  4         JOIN
  5         emp e
  6         ON e.mgr = m.empno
  7  where  e.bdate > date '1965-01-01'
  8  order  by employee;

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

EMPLOYEE | MANAGER
-------- | --------
SMITH    | FORD
WARD     | BLAKE

13 rows selected.

SQL>

Quiz