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!
Are You Ready For Office 365?
Office 365 is
- Email and calendaring powered by Microsoft Exchange
- Office Web Apps - the office productivity apps you already know, tailored for the
web
- Websites powered by Microsoft SharePoint both for internal-facing intranet and customer-facing
website
- Instant Messaging and Online Meetings powered by Microsoft Lync
- All with a financially-backed 99.9% uptime guarantee
Sign up for a free trial:
Your information will not be shared with any other party. You must provide
a valid email in order to recieve your login information and starting information
kit.
Watch
this video to see how Office 365 can help your business.
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.
|
|