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.
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)]|
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;
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.
- New Content published on w3resource:
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework