Oracle SQL - Joins of Three or More Tables

Introduction

The following code joins three tables together.

Demo

SQL>
SQL>--   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 salgrades;

Table dropped.

SQL> create table salgrades(
  2  grade      NUMBER(2)   primary key,
  3  lowerlimit NUMBER(6,2) not null check (lowerlimit >= 0),
  4  upperlimit NUMBER(6,2) not null,
  5  bonus      NUMBER(6,2) not null);
SQL>
SQL> insert into salgrades values (1,  700,1200,   0);
SQL> insert into salgrades values (2, 1201,1400,  50);
SQL> insert into salgrades values (3, 1401,2000, 100);
SQL> insert into salgrades values (4, 2001,3000, 200);
SQL> insert into salgrades values (5, 3001,9999, 500);
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>
SQL> select e.ename           employee
  2  ,      12*e.msal+s.bonus total_salary
  3  ,      d.dname           department
  4  from   emp   e
  5  ,      salgrades   s
  6  ,      departments d
  7  where  e.msal between s.lowerlimit
  8                    and s.upperlimit
  9  and    e.deptno = d.deptno;

EMPLOYEE | TOTAL_SALARY | DEPARTMENT
-------- | ------------ | ----------
ADAMS    |     13200.00 | TRAINING
WARD     |     15050.00 | ACCOUNTING
BROWN    |     15050.00 | SALES
MARY     |     15650.00 | ACCOUNTING
SMITH    |     21700.00 | TRAINING
ALLEN    |     19300.00 | SALES
BREAD    |     18100.00 | SALES
JACK     |     35900.00 | TRAINING
BLAKE    |     34400.00 | ACCOUNTING
CLARK    |     29600.00 | ACCOUNTING
SCOTT    |     36200.00 | TRAINING

EMPLOYEE | TOTAL_SALARY | DEPARTMENT
-------- | ------------ | ----------
FORD     |     36200.00 | TRAINING
KING     |     60500.00 | ACCOUNTING
JONES    |     96500.00 | SALES

14 rows selected.

SQL>

We now have three free tuple variables (e, s, and d) ranging over three tables.

We need at least two conditions in the WHERE clause to get the correct row combinations in the query result.

SQL language supports table names as default tuple variables, without the need to declare them explicitly in the FROM clause.

select emp.ename, departments.location
from   emp, departments
where  emp.deptno = departments.deptno;

Related Topic