Intro
What are comparison operators? Well, I suspect everyone has at least seen them before:
| Operator | Meaning |
|---|---|
| = | Equal |
| <> | Not equal to |
| > | Greater than |
| >= | Greater than or equal to |
| < | Less than |
| <= | Less than or equal to |
Equations are the normal context for comparison operators. So why do we use them for Joins in the first place?
The conventional scenario: Equal matching
We use them to match key values from the key column of one table with key values of the key column of another table. Usually our goal is to match equal key values hence we use the equal to-operator or “=”. We use this specific operator and its mechanism to ensure that we join different tables on the correct row by matching on the same key value.
For example, if in a retailer company we were to join an orders table with an adress table so that our logistics department can prepare and fulfill deliveries we wanted to make sure that all orders match the corresponding adresses and vice versa.
Different scenarios: Non-equal matching
But we also have non-equal operators such as smaller than “<” and greater than “>” available. So these will join two datasets by mapping only non-equal keys in contrast to the equal operator.
As a consequnce there will be many more than one value in the second table which are unequally matched to the first table. In simpler words: non-equal operators produce more than one entry per source entry in the resulting table.
The application scenario for unequal matching might not be instantly obvious. But this kind of unequal matching can be applied to different contexts. Often we see it in the context of time values and comparisons over time. Our retailer company might, for example, create a list of all sales during a promotion campaign. To obtain such a list it needs to copy every sale which happened after (greater than) the start state and before (less than) the end date of the campaign. So we need to concatenated logical conditions and comparison operators:
start_date AND < end_date
To compile this list with all sales during the promotion campaign bespoke retailer company could join the sales table with the one of the promotion campaign using sales_date and promo_date. Here the join logic in general terms would be similar:
sales_date > promo_start_date AND sales_date < promo_end_date