Nonequi join

From Oracle FAQ
Jump to: navigation, search

An nonequi (or theta) join is an inner join statement that uses an unequal operation (i.e: <>, >, <, !=, BETWEEN, etc.) to match rows from different tables. The converse of an nonequi join is a equi join operation.

[edit] Examples

Using SCOTT's tables and Oracle join syntax:

SCOTT> SELECT e.ename, e.sal, s.grade
  2    FROM  emp e, salgrade s
  3    WHERE e.sal BETWEEN s.losal AND s.hisal;

ENAME             SAL      GRADE
---------- ---------- ----------
SMITH             800          1
JAMES             950          1
ADAMS            1100          1
WARD             1250          2
MARTIN           1250          2
MILLER           1300          2
TURNER           1500          3
ALLEN            1600          3
CLARK            2450          4
BLAKE            2850          4
JONES            2975          4
SCOTT            3000          4
FORD             3000          4
KING             5000          5

Using ANSI join syntax for the same query:

SELECT e.ename, e.sal, s.grade
FROM emp e INNER JOIN salgrade s
     ON e.sal BETWEEN s.losal AND s.hisal;

[edit] Also see

Glossary of Terms
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z #