Look up values in SQL Server using range joins

by Arthur Fuller | May 15, 2006 7:00:00 AM

Tags: Databases, Network technology, Arthur Fuller, server, join, Microsoft SQL Server, SQL Server Newsletter

Takeaway: Arthur Fuller wants to shake up developers' assumptions that joins are always tests of equality. He explains that there are lots of ways to join, and demonstrates how you can use range joins to match a potentially large number of rows.

It's a myth that joins are always tests of equality�they don't have to be. There are uses for joins based on "less than or equal to" and "greater than or equal to"; I can even think of a case in which a "not equal to" join would be useful. The point is, there are lots of ways to join, and range joins is one of them.

SQL Server developers usually use joins to bring together two tables (Listing A) even though we all know the textbook example of relational multiplication:

SELECT T1.*, T2.ColumnOfInterest

FROM T1, T2

This gives you the number of rows in T1 times the number of rows in T2. It's conceptually interesting, but how many times have you actually used it?

We tend to think of joins in terms of equality (T1.ColumnName = T2.ColumnName). However, your tables may include information about such realities as taxation, insurance, and shipping rates. For instance, if your package weighs less than 1 kilogram, you pay rate x; if it weighs between 1 and 4 kilograms, then you pay rate y. I call this a range join because it can match a potentially large number of rows.

You can express the join using the BETWEEN keyword. First, assume two tables (Listing B). In said database, assume two tables (Listing C). Listing D contains the shipper's rate schedule. Listing E contains the values of the shipments table. Listing F contains the view that pulls all of these values together. Listing G contains the values obtained by applying the view to the tables.

We have a problem, as evinced by the first two rows. A weight of 1 corresponds to two rows in the ShippingRates table, so we need to refine our boundaries a little. Our mistake was to define the MinWeight and MaxWeight columns as integers. If we change them to floats and adjust the MinWeight values slightly (row two becomes 1.01 and row three becomes 4.01), then the problem is fixed. Listing H contains the new results.

You can use this same technique with almost any data type that might involve a range, e.g., salaries for income tax, ages for insurance rates, heights, weights�and use virtually all of them for demographic analyses.

Miss a tip?

Check out the SQL Server archive, and catch up on the most recent editions of Arthur Fuller's column.

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!

4 comment(s)