Oracle SQL - Table Join Equijoins

Introduction

The following code add WHERE clause and an ORDER BY clause to get the results ordered by department, and within each department, by employee name.
The code shows a join or, to be more precise, an equijoin. This is the most common join type in SQL.
Demo

SQL>
SQL>-- from  ww  w.j a  v  a 2 s. c om
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> drop table departments;

Table dropped.

SQL>
SQL> create table departments(
  2  deptno    NUMBER(2)     primary key,
  3  dname     VARCHAR2(10)  not null unique check (dname = upper(dname)),
  4  location  VARCHAR2(8)   not null        check (location = upper(location)),
  5  mgr       NUMBER(4)) ;
SQL>
SQL> insert into departments values (10,'ACCOUNTING','NEW YORK',7007);
SQL> insert into departments values (20,'TRAINING',  'DALLAS',  7004);
SQL> insert into departments values (30,'SALES',     'CHICAGO', 7006);
SQL> insert into departments values (40,'HR',        'BOSTON',  7009);
SQL>
SQL> select   d.deptno
  2  ,        d.location
  3  ,        e.ename
  4  ,        e.init
  5  from     emp   e
  6  ,        departments d
  7  where    e.deptno = d.deptno
  8  order by d.deptno
  9  ,        e.ename;

   DEPTNO | LOCATION | ENAME    | INIT
--------- | -------- | -------- | -----
    1     | NEW YORK | BLAKE    | R
    1     | NEW YORK | CLARK    | AB
    1     | NEW YORK | KING     | CC
    1     | NEW YORK | MARY     | ABC
    1     | NEW YORK | WARD     | TF
    2     | DALLAS   | ADAMS    | AA
    2     | DALLAS   | FORD     | MG
    2     | DALLAS   | JACK     | JM
    2     | DALLAS   | SCOTT    | DEF
    2     | DALLAS   | SMITH    | N
    30    | CHICAGO  | ALLEN    | JAM

   DEPTNO | LOCATION | ENAME    | INIT
--------- | -------- | -------- | -----
    30    | CHICAGO  | BREAD    | JJ
    30    | CHICAGO  | BROWN    | P
    30    | CHICAGO  | JONES    | R

14 rows selected.

SQL>
Oracle SQL - Table Join Equijoins

Introduction

Demo

Related Topics
