NON-EQUIJOIN based on condition rather than column

This is a 100% guaranteed situation on the exam. We all know how to join tables based on equijoin conditions, but non-equijoin will also be tested. Moreover, tests become more and more complex. They include more logic than before. Therefore, be prepared for questions containing 3 or 4 tables being joined. Some of them may be joined via equijoin condition, while some other tables may be joined via non-equijoin condition. An example below demonstrates this logic.

The first two tables orders_items and orders are connected through typical JOIN USING() condition. There is nothing unusual here. Then we have a third table product_information connected to the first two tables based on NON-EQUIJOIN condition (see highlighted). Fields min_price and list_price exists only in product_information table. They don’t exist in the first two tables. But, NON-EQUIJOIN allows us to connect tables which do not have a common field. Only data types must be compatible.

One of the answers among other answers in Multi-Choice selection for this type of question will be: This join cannot be accomplished because the product_information table has no common fields with other two tables. And at first glance, it seems true, but in reality, you can join two tables even if they have no common fields. Non-equijoin is used for this purpose. You just need to use the columns with a compatible data types for it.

Leave a comment

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