As somebody who works in education, I use this type of construct on a regular basis. It would be nice to see an example with real numbers rather than integers. Typically you end up writing it as

SELECT

      s.StudentID,

      g.LetterGrade

FROM

      tb_StudentGrade s

INNER JOIN

      tb_GradeScale g

          ON(

(s.NumericGrade >= g.MinNumeric) AND (s.NumericGrade < g.MaxNumeric)

)

Which may or may not be the best way.

Hello Chris

Please excuse a question from a newbie, but is there an advantage in using the between operator as opposed to using >= and <= comparisons. 

Looking at the execution plans of some of my queries it seems to make no difference to the plan structure or cost of the query replacing >= and <= with between in either the WHERE or from clause, but it certainly makes the syntax a bit neater.

David

BETWEEN is purely simpler to read. There is no specific engine enhancements that make BETWEEN any faster.

What about performance using this methodology?
There are many articles about using "SARG"able methodologies which allow indexes to operate correctly.
http://www.databasejournal.com/features/mssql/article.php/1436301
http://www.windowsitpro.com/SQLServer/Article/ArticleID/42349/42349.html

It appears that the JOIN portion is SARGable, but how do we know for sure since the join seems to now operate onmultiple values?

INNER JOIN
      tb_GradeScale g
      ON(
            s.NumericGrade BETWEEN g.MinNumeric AND g.MaxNumeric
      )

- B