A Non-Equijoin with no corresponding columns

A non-equijoin is represented by the use of an operator other than equality operator. A non-equijoin is used when no corresponding columns exist between the tables in the query, but rather a relationship exists between two columns having compatible data types.

Several conditions can be used to define a non-equijoin, including <, <=, >, >=, BETWEEN, and IN.

In the given scenario a non-eqijoin relationship exists between the salary column of employees table and the min_salary and max_salary columns of the jobs table. To produce the report containing employee’s name, his salary, minimum salary and maximum salary, you have to JOIN employees table with jobs table via non-equijoin condition in the ON clause, namely ON (employees.salary BETWEEN jobs.min_salary AND jobs.max_salary) ;

As we can see two tables have been joined on non-equijoin condition via three columns.

In fact, we could have made this condition even more strict and more practical, by searching only top earners ( whose salaries 2.4 times higher than minimum salary) by doing this query

The second query gives us only five top earners whose salaries are at least 2.4 times higher than the minimum salary.

The most important conclusion from this example: You can join tables even if they don’t have the corresponding column names. Column names just need to be compatible data types.


Leave a comment

Your email address will not be published. Required fields are marked *