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

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

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.

Editor's Picks

Free Newsletters, In your Inbox