MACROMEDIA ColdFusion MX, Руководство по разработке

Страница: 466 / 860

466 Chapter 22: Using Query of Queries
Comparison conditional
This conditional lets you compare an expression against another expression of the same data type
(Numeric, String, Date, or Boolean). You can use it to selectively retrieve only the relevant rows of
a record set.
Syntax
comparison_cond ::= expression [> | >= | <> | != | < | <=] expression
Example
The following example uses a comparison conditional to retrieve only those dogs whose IQ is at
least 150:
SELECT dog_name, dog_IQ
FROM Dogs
WHERE dog_IQ >= 150;
Between conditional
This conditional lets you compare an expression against another expression. You can use it to
selectively retrieve only the relevant rows of a record set. Like the comparison conditional, the
BETWEEN conditional makes a comparison; however, the between conditional makes a
comparison against a range of values. Therefore, its syntax requires two values, which are
inclusive, a minimum and a maximum. You must separate these values with the AND keyword.
Syntax
between_cond ::= expression [NOT] BETWEEN expression AND expression
Example
The following example uses a BETWEEN conditional to retrieve only those dogs whose IQ is
between 150 and 165, inclusive:
SELECT dog_name, dog_IQ
FROM Dogs
WHERE dog_IQ BETWEEN 150 AND 165;
IN conditional
This conditional lets you specify a comma-delimited list of conditions to match. It is similar in
function to the OR conditional. In addition to being more legible when working with long lists,
the IN conditional can contain another SELECT statement.
Syntax
in_cond ::= expression [NOT] IN (expression_list)
Example
The following example uses the IN conditional to retrieve only those dogs who were born at
either Ken’s Kennels or Barb’s Breeders:
SELECT dog_name, dog_IQ, Kennel_ID
FROM Dogs
WHERE kennel_ID IN ('Kens','Barbs');