How to Use Comparison Predicates and BETWEEN in SQL Statements

Typically, you will use SQL comparison predicates to compare one value with another. For every row in which the comparison evaluates to a True value, that value satisfies the WHERE clause, and the operation (SELECT, UPDATE, DELETE, or whatever) executes upon that row. Rows that the comparison evaluates to FALSE are skipped. Consider the following SQL statement:

SELECT * FROM FOODS
 WHERE Calories <219 ;

This statement displays all rows from the FOODS table that have a value of less than 219 in the Calories column.

Comparison Symbol
Equal =
Not equal <>
Less than <
Less than or equal <=
Greater than >
Greater than or equal >=

Sometimes you want to select a row if the value in a column falls within a specified range. One way to make this selection is by using comparison predicates. For example, you can formulate a WHERE clause to select all the rows in the FOODS table that have a value in the Calories column greater than 100 and less than 300, as follows:

WHERE FOODS.Calories > 100 AND FOODS.Calories < 300

This comparison doesn’t include foods with a calorie count of exactly 100 or 300 — only those values that fall between these two numbers. To include the end points (in this case, 100 and 300), you can write the statement as follows:

WHERE FOODS.Calories >= 100 AND FOODS.Calories <= 300

Another way of specifying a range that includes the end points is to use a BETWEEN predicate in the following manner:

WHERE FOODS.Calories BETWEEN 100 AND 300

This clause is functionally identical to the preceding example, which uses comparison predicates. This formulation saves some typing — and it’s a little more intuitive than the one that uses two comparison predicates joined by the logical connective AND.

The BETWEEN keyword may be confusing because it doesn’t tell you explicitly whether the clause includes the end points. In fact, the clause does include these end points. When you use the BETWEEN keyword, a little birdy doesn’t swoop down to remind you that the first term in the comparison must be equal to or less than the second.

If, for example, FOODS.Calories contains a value of 200, the following clause returns a True value:

WHERE FOODS.Calories BETWEEN 100 AND 300

However, a clause that you may think is equivalent to the preceding example returns the opposite result, False:

WHERE FOODS.Calories BETWEEN 300 AND 100

If you use BETWEEN, you must be able to guarantee that the first term in your comparison is always equal to or less than the second term.

You can use the BETWEEN predicate with character, bit, and datetime data types as well as with the numeric types. You may see something like the following example:

SELECT FirstName, LastName
 FROM CUSTOMER
 WHERE CUSTOMER.LastName BETWEEN 'A' AND 'Mzzz' ;

This example returns all customers whose last names are in the first half of the alphabet.

  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
Advertisement

Inside Dummies.com

Dummies.com Sweepstakes

Win $500. Easy.