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.
SQL> SQL> drop table emp; Table dropped.-- www. j a v a2 s . co m 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.