Networking

Using SQL Server JOINs for easy range lookups

Arthur Fuller goes beyond the traditional use of JOINs to link two tables on equal values. In this tip, he illustrates how to perform a range lookup using a JOIN.

JOINs are an essential component for SQL Server developers, yet relatively few of them understand their real power. Most developers learn about equi-JOINs, wherein a value from table T1 is compared to a value from table T2. The pattern looks like this:

SELECT * FROM T1 INNER JOIN T2 ON T1.someColumn = T2.someColumn

This staple of SQL Server development is only the beginning of what you can do with a JOIN. The first thing to realize is that you can substitute another operator for the equality sign above. It wouldn't be very useful in most circumstances, but you could join a row in T1 to all rows in T2 that do not match the value in T1 simply by substituting <> for the equals (=) sign.

Get SQL tips in your inbox
TechRepublic's 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 sign up today!

A much more interesting and practical use for JOINs is the range lookup. There are thousands of business processes in which range lookups are essential, which include tax tables, shipping rates, student grades, and insurance rates.

The general idea underlying these scenarios is that any given value may not be found in the lookup table. Rather, the lookup table contains two columns: one for the floor of the range and one for its ceiling. Listing A contains a script to create the sample tables. Figure 1 and Figure 2 contain sample rows.

Figure 1

RangeID Floor Ceiling Rate
1 0.00 1.99 5.0000
2 2.0 3.99 8.5000
3 4.0 6.99 11.0000
4 7.0 9.99 13.0000
NULL NULL NULL` NULL

Figure 2

ShipmentID Weight
1 1.30
2 2.70
3 3.00
4 1.90
5 2.40
6 3.30
7 4.70
NULL NULL

The SQL required to perform a range lookup given this data is in Listing B. This produces the result set that you will see in Figure 3. The query does exactly what you want. As written, it's a straight SELECT query, but you can easily turn it into a stored procedure or a parameterized UDF, depending on your requirements.

Figure 3

ShipmentID Weight Floor Ceiling Rate
1 1.30 0.00 1.99 5.00
4 1.90 0.00 1.99 5.00
2 2.70 2.00 3.99 8.50
3 3.00 2.00 3.99 8.50
5 2.40 2.00 3.99 8.50
6 3.30 2.00 3.99 8.50
7 4.70 4.00 6.99 11.00

This approach has numerous applications, and it's easier to code than the equivalent SQL written using the WHERE predicate. In addition, in a large lookup table such as one used to calculate income tax ranges, you could index the Floor and Ceiling columns and improve performance, since the optimizer would take advantage of these indexes and find the corresponding rows very quickly.

Miss a tip?

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

3 comments
erherqh
erherqh

hats off to the person who gave the query...i was pulling my hair out for this issue for a whole day...... u are really done a great job./......... i appreciate you .......

argordon
argordon

AWESOME! This is the most basic explanation I've seen so far! Thankyou very much! :) You are a life saver!

gsquared
gsquared

I've also found range joins very useful on date fields. select * from customers inner join promotions on customers.dateadded between promotions.startdate and promotions.enddate for example. I've used a few joins with > relations or < relations, but not many. Complex joins, with more than one relation, also come in handy. select * from customers inner join promotions on customers.dateadded between promotions.startdate and promotions.enddate and promotions.purchaseminimum < (select sum(price) from orders where account = customers.account and paymentdate < promotions.startdate and paid = 1) for example. (That one would be a performance killer if not done as a proc/function with an input parameter of the account number, if your tables are at all large, because of the correlated subquery, but it can be quite a good way to pull data if you manage the data scope correctly.)

Editor's Picks