MACROMEDIA COLDFUSION MX 61-DEVELOPING COLDFUSION MX, Develop Manual

Page: 465 / 860

Query of Queries user guide 465
UNION
SELECT * FROM TableC
In the first statement, there are no duplicates in the union between TableB and TableC. Then, in
the union between that set and TableA, the ALL keyword includes the duplicates. In the second
statement, duplicates are included in the union between TableA and TableB but are eliminated in
the subsequent union with TableC. The ALL keyword has no effect on the final result of this
expression.
Using other keywords with UNION
When you perform a UNION, the individual SELECT statements cannot have their own
ORDER BY or COMPUTE clauses. You can only have one ORDER BY or COMPUTE clause
after the last SELECT statement; this clause is applied to the final, combined result set. You can
only specify GROUP BY and HAVING expressions in the individual SELECT statements.
Using conditional operators
ColdFusion lets you use the following conditional operators in your SQL statements:
•
Test
•
Null
•
Comparison
•
Between
•
IN
•
LIKE
Test conditional
This conditional tests whether a Boolean expression is True, False, or Unknown.
Syntax
cond_test ::= expression [IS [NOT] {TRUE | FALSE | UNKNOWN} ]
Example
SELECT _isValid FROM Chemicals
WHERE _isValid IS true;
Null conditional
This conditional tests whether an expression is null.
Syntax
null_cond ::= expression IS [NOT] NULL
Example
SELECT bloodVal FROM Standards
WHERE bloodVal IS NOT null;