121
Chapter 6: Introducing Basic Query Flow
6
TABLE 6 -1 Standard Comparison OperatorsDescription Operator ExampleEquals = Quantity = 12
Greater than > Quantity > 12
Greater than or equal to >= Quantity >= 12
Less than < Quantity < 12
Less than or equal to <= Quantity<= 12
Not equal to <> , != Quantity <> 12 , Quantity != 12
Not less than !< Quantity !< 12
Not greater than !> Quantity !> 12The comparison operators that include an exclamation point are not ANSI standard SQL. <> is portable; != is not.In addition to the standard comparison operators, which are no doubt familiar, SQL provides four spe-
cial comparison operators: BETWEEN, IN, LIKE, and IS. The fi rst three are explained in this section.
Testing for nulls using the IS keyword and handling nulls are explained in Chapter 8.Best Practice
The best way to fi nd a thing is to look for it, rather than to fi rst eliminate everything it isn’t. It’s easier
to locate a business in a city than it is to prove that the business doesn’t exist. The same is true of
database searches. Proving that a row meets a condition is faster than fi rst eliminating every row that
doesn’t meet that condition. In general (but not always), restating a negative WHERE condition as a
positive condition improves performance.Using the BETWEEN Search Condition
The BETWEEN search condition tests for values within a range. The range can be deceiv-
ing, however, because it is inclusive. For example, BETWEEN 1 and 10 would be true
for 1 and 10. When using the BETWEEN search condition, the fi rst condition must be less
than the latter value because in actuality, the BETWEEN search condition is shorthand for
“greater than or equal to the fi rst value, and less than or equal to the second value.”c06.indd 121c06.indd 121 7/30/2012 4:16:01 PM7/30/2012 4:16:01 PM
http://www.it-ebooks.info