Nonequi join

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.

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;

Also see

• Cartesian join
• Equi join
• Inner join
• Natural join
• Outer join
• Self join

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	#