Adobe 38043740 Developing Applications - Page 443

DEVELOPING COLDFUSION 9 APPLICATIONS 438 Accessing and Using Data /* First statement. */ SELECT * FROM TableA UNION ALL (SELECT * FROM TableB UNION SELECT * FROM TableC ) /* Second statement. */ (SELECT * FROM TableA UNION ALL SELECT * FROM TableB ) 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 Query of Queries lets you use the following conditional operators in your SQL statements: 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; Last updated 1/20/2012