SQL For Dummies, 8th Edition by Allen G. Taylor

Author:Allen G. Taylor
Language: eng
Format: epub
Publisher: John Wiley and Sons, Inc.
Published: 2013-08-04T16:00:00+00:00

Greater than

>

Greater than or equal

>=

BETWEEN

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.

IN and NOT IN

The IN and NOT IN predicates deal with whether specified values (such as OR, WA, and ID) are contained within a particular set of values (such as the states of the United States). You may, for example, have a table that lists suppliers of a commodity that your company purchases on a regular basis. You want to know the phone numbers of the suppliers located in the Pacific Northwest. You can find these numbers by using comparison predicates, such as those shown in the following example:

SELECT Company, Phone

FROM SUPPLIER

WHERE State = 'OR' OR State = 'WA' OR State = 'ID' ;

You can also use the IN predicate to perform the same task, as follows:

SELECT Company, Phone

FROM SUPPLIER

WHERE State IN

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.