Non-Equi Joins

Typically in SQL, tables joined on the equality of a condition based on the concept of primary and foreign keys. However, as the name implies, a non-equi join query is based on the "inequality" of a condition.  Consider a result set based on the inequality of an employee ID in an address table.  A non-equi join would join all address records to other employees, but not to the employee record to which it should belong.  However, the non-equi join does this, but on fields other than the primary and foreign key.  Take a look at an example.  Suppose we had the following two tables, Employees and Salary_Grade:

Employees
emp_id, first_name, last_name, job_id, salary
Salary_Grade
grade, min_salary, max_salary

Here the salary field in the Employees table is joined on a range of minimum and maximum salaries in the Salary_Grade table, join conditions which do not match.

SQL> select E.last_name,E.salary,G.grade
  2  from employees E INNER JOIN sal_grade G
  3  on E.salary BETWEEN G.min_sal AND G.max_sal
  4  order by G.grade;

The result is shown below:

LAST_NAME SALARY G
------------------------- ---------- -
Philtanker 2200 A
Markle 2200 A
Olson 2100 A
Gee 2400 A
Landry 2400 A
Perkins 2500 B
Himuro 2600 B
Matos 2600 B
OConnell 2600 B
Grant 2600 B
Mikkilineni 2700 B

Although not very common, there are some situations where a non-equi join makes sense.