Non-Equi-Joins

So far, our join conditions have been based on matching values in selected fields in two or more tables. However, with Microsoft Jet you can also create join conditions based on tests other than equality. For example, you can create joins based on a range of acceptable values.

Suppose you have a table called PerformanceGrade that contains the following values. (This table exists in the NorthwindTables sample database.)

You want to assign a PerformanceGradeKey value to each employee’s sales. Employees with sales between 0 and $100,000 receive a 0, those with sales between $100,000.01 and $200,000 receive a 1, and so on.

The first step is to create a QueryDef object that sums up the sales by employee. Call this QueryDef object SelectEmployeeSales:

SELECT 
	Orders.EmployeeID, 
	Sum((UnitPrice*Quantity) - Discount) AS ExtPrice
FROM Orders 
INNER JOIN [Order Details] 
ON Orders.OrderID = [Order Details].OrderID
GROUP BY Orders.EmployeeID;

Sample output from this query may be:

Now this saved QueryDef object has to be joined to the PerformanceGrade table. The grade assigned is determined by the high and low ranges, and the actual sales of each employee. Here is the SQL statement that produces the desired result:

SELECT 
	SelectEmployeeSales.EmployeeID, 
	SelectEmployeeSales.ExtPrice, 
	PerformanceGrade.PerformanceGradeKey
FROM SelectEmployeeSales, PerformanceGrade
WHERE (SelectEmployeeSales.ExtPrice Between PerformanceGrade.LowRange
	And PerformanceGrade.HighRange)
ORDER BY PerformanceGrade.PerformanceGradeKey;

As you can see from the sample output shown, Microsoft Jet is able to link values from the SelectEmployeeSales query to the PerformanceGrade table even though neither table has common fields or shares common values. The join is based entirely on the condition specified in the WHERE clause.

Be aware that Microsoft Jet can’t perform its normal optimization techniques on non-equi-join queries.