Oracle NON-EQUIJOINS

What is Non-Equijoin in Oracle?

The nonequijoins is such a join which match column values from different tables based on an inequality (instead of the equal sign like >, <, >=, <= ) expression. The value of the join column in each row in the source table is compared to the corresponding values in the target table. A match is found if the expression based on an inequality operator used in the join, evaluates to true.

Syntax:

SELECT table1.column, table2.column
FROM table1
[JOIN table2 ON (table1.column_name < table2.column_name)]|
[JOIN table2 ON (table1.column_name > table2.column_name)]|
[JOIN table2 ON (table1.column_name <= table2.column_name)]|
[JOIN table2 ON (table1.column_name >= table2.column_name)]|
[JOIN table2 ON (table1.column BETWEEN table2.col1 AND table2.col2)]| 

Pictorial presentation

Example Oracle Non Equijoins:

The following SQL query returns the department no, name of the department and city from locations and departments table for the range of location no between 1800 and 2500 and department no is below 30.

Sample table: locations

Sample table: departments

SELECT a.department_id, a.department_name, b.city
FROM departments a, locations b
WHERE b.location_id BETWEEN 1800 AND 2500
AND a.department_id < 30; 

Sample Output:

DEPARTMENT_ID DEPARTMENT_NAME                CITY
------------- ------------------------------ ------------
           10 Administration                 Toronto
           10 Administration                 Whitehorse
           10 Administration                 Beijing
           10 Administration                 Bombay
           10 Administration                 Sydney
           10 Administration                 Singapore
           10 Administration                 London
           10 Administration                 Oxford
           20 Marketing                      Toronto
           20 Marketing                      Whitehorse
           20 Marketing                      Beijing
           20 Marketing                      Bombay
           20 Marketing                      Sydney
           20 Marketing                      Singapore
           20 Marketing                      London
           20 Marketing                      Oxford

16 rows selected.

Previous: EQUIJOINS
Next: NATURAL JOINS