Oracle SQL - Table Join Non-equijoins

Introduction

If you use a comparison operator other than an equal sign in the WHERE clause in a join, it is called a non-equijoin.

The following code use non-equijoin which calculates the total annual salary for each employee.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from   ww  w  . j av a2 s .  c  om

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>
SQL> select e.ename           employee
  2  ,      12*e.msal+s.bonus total_salary
  3  from   emp e
  4  ,      salgrades s
  5  where  e.msal between s.lowerlimit
  6                    and s.upperlimit;

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

EMPLOYEE | TOTAL_SALARY
-------- | ------------
FORD     |     36200.00
KING     |     60500.00
JONES    |     96500.00

14 rows selected.

SQL>

You can choose any name you like for your tuple variables.

Related Topic