Specifying a join condition not based on equality
The condition for joining values in two fields is usually based on equality (=), as shown in all the examples so far. Less common are join conditions that use any of the other comparison operators: not equal (<>), greater than (>), less than (<), greater than or equal to (>=), and less than or equal to (<=).
The following example shows the use of joins that are not based on equality. In the example, a Sales data set is joined with a Commissions data set. The joined data set uses a >= join and a < join to look up the commissions to pay to sales managers, based on their sales totals and management levels.
Figure 10‑19 shows the Sales and Commissions data sets. In the Commissions data set, each level has four commission rates. For level 1, a commission rate of 25% is paid if a sales total is between 75000 and 100000, 20% is paid if a sales total is between 50000 and 75000, and so on.
Figure 10‑19 Data sets returning sales and commission rates data
The following join conditions specify the fields on which to join and how to compare the values in the fields being joined:
The first condition, shown in
Figure 10‑20, compares the Level values in the Sales and Commissions data sets and looks for a match.
Figure 10‑20 Joining on the Level field and looking for a match
The second condition, shown in
Figure 10‑21, uses the >= operator to compare the TotalSales values in the Sales data set with the LowRange values in the Commissions data set.
Figure 10‑21 Joining on TotalSales and LowRange fields using the >= operator
The third condition, shown in
Figure 10‑22, uses the < operator to compare the TotalSales values in the Sales data set with the HighRange values in the Commissions data set.
Figure 10‑22 Joining on TotalSales and HighRange fields using the < operator
The second and third join conditions check if a sales total is greater than or equal to LowRange and less than HighRange.
The joined data set returns the results shown in
Figure 10‑23.
Figure 10‑23 Data rows returned by the joined data set
