Inequality Joins In Access
For most of our queries, we join tables based on a column in one table
exactly matching a column in another table. For instance, we can retrieve a
customer’s contact information from one table and their order history from
another table based on a matching customer id number.
SQL Server and many other industrial strength databases also support
queries where columns do not match—one field is greater than the other or
between two values in the foreign table’s record. These types of joins are
called inequality joins or non-equijoins.
Take an example of a company that is trying to find orders that were
improperly placed. For whatever reason, some customers are being allowed to
place orders in excess of their credit limit. We need to search the customers
table and the orders table for these occurrences. We join the two tables on
customer ID—that much is standard. But we also join them on the inequality
relationship (less than, in this case) of the customer credit line and the
order amount. If you are used to SQL, this will look familiar, if not exactly
standard, to you:
SELECT Customers.FirstName, Customers.LastName,
Customers.CreditLine, Orders.TotalAmount FROM Customers INNER JOIN Orders ON
Customers.CustID = Orders.CustID AND Customers.CreditLine <
Orders.TotalAmount
We are using the less than operator in a JOIN statement!
Access will
allow this query in the SQL window and will return the correct results, but
if you try to view it the query design window, this is what you see:
If you like to work in the Query Design window, you can set up the
inequality in the WHERE clause instead of the FROM clause like so:
SELECT Customers.FirstName, Customers.LastName,
Customers.CreditLine, Orders.TotalAmount FROM Customers INNER JOIN Orders ON
Customers.CustID = Orders.CustID
WHERE Customers.CreditLine< Orders.TotalAmount
The difference in speed of the two queries depends on the flavor of SQL
you’re running, but the results will be identical. See our prior articles on
Access Subqueries,
Using SQL In Access and
Cross Joins in Access for more about
SQL and Access.