If you know SQL or have interacted with databases, you might be familiar with some of the Query of Queries functionality.

Using dot notation

ColdFusion supports using dot notation in table names.

Example

If a structure named A contains a field named B, which contains a table named Products, you can refer to the table with dot notation, as follows:

SELECT tape_ID, length
FROM A.B.Products;

Using joins

A join operation uses a single SELECT statement to return a result set from multiple, related tables, typically those with a primary key - foreign key relationship. There are two SQL clauses that perform joins:

• WHERE clause: ColdFusion supports joins through a WHERE clause.
• INNER JOIN and OUTER JOIN: ColdFusion does not support joins through INNER JOIN or OUTER JOIN clauses.

Using unions

The UNION operator lets you combine the results of two or more SELECT expressions into a single record set. The original tables must have the same number of columns, and corresponding columns must be UNION-compatible data types. Columns are UNION-compatible data types if they meet one of the following conditions:

• The same data type; for example, both Tinyint
• Both Numeric; for example, Tinyint, Smallint, Integer, Bigint, Double, Float, Real, Decimal, or Numeric
• Both Characters; for example, Char, Varchar, or LongVarchar
• Both Dates; for example, Time, TimeStamp, or Date

Syntax

select_expression = select_expression UNION [ALL] select_expression

Example

This example uses the following tables:

To combine Table1 and Table2, use a UNION statement, as follows:

SELECT * FROM Table1 
UNION 
SELECT * FROM Table2 

The UNION statement produces the following result (UNION) table:

Using aliases for column names

The column names of a UNION table are the column names in the result set of the first SELECT statement in the UNION operation; ColdFusion ignores the column names in the other SELECT statement. To change the column names of the result table, you can use an alias, as follows:

Select Type as SportType, Name as SportName from Table1
UNION
Select * from Table2

Duplicate rows and multiple tables

By default, the UNION operator removes duplicate rows from the result table. If you use the keyword ALL, then duplicates are included.

You can combine an unlimited number of tables using the UNION operator, for example:

Select * from Table1
UNION 
Select * from Table2
UNION 
Select * from Table3
... 

Parentheses and evaluation order

By default, the Query of Queries SQL engine evaluates a statement containing UNION operators from left to right. You can use parentheses to change the order of evaluation. For example, the following two statements are different:

/* 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

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;

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');

LIKE conditional

This conditional lets you perform wildcard searches, in which you compare your data to search patterns. This strategy differs from other conditionals, such as BETWEEN or IN, because the LIKE conditional compares your data to a value that is partially unknown.

Syntax

like_cond ::= left_string_exp [NOT] LIKE right_string_exp [ESCAPE escape_char] 

The left_string_exp can be either a constant string, or a column reference to a string column. The right_string_exp can be either a column reference to a string column, or a search pattern. A search pattern is a search condition that consists of literal text and at least one wildcard character. A wildcard character is a special character that represents an unknown part of a search pattern, and is interpreted as follows:

• The underscore (_) represents any single character.
• The percent sign (%) represents zero or more characters.
• Square brackets ([ ]) represents any character in the range.
• Square brackets with a caret [^] represent any character not in the range.
• All other characters represent themselves.

Examples

The following example uses the LIKE conditional to retrieve only those dogs of the breed Terrier, whether the dog is a Boston Terrier, Jack Russell Terrier, Scottish Terrier, and so on:

SELECT dog_name, dog_IQ, breed
FROM Dogs
WHERE breed LIKE '%Terrier';

The following examples are select statements that use bracketed ranges:

SELECT lname FROM Suspects WHERE lname LIKE 'A[^c]%';
SELECT lname FROM Suspects WHERE lname LIKE '[a-m]%';
SELECT lname FROM Suspects WHERE lname LIKE '%[]';
SELECT lname FROM Suspects WHERE lname LIKE 'A[%]%';
SELECT lname FROM Suspects WHERE lname LIKE 'A[^c-f]%';

Case sensitivity

Unlike the rest of ColdFusion, Query of Queries is case-sensitive. However, Query of Queries supports two string functions, UPPER() and LOWER(), which you can use to achieve case-insensitive matching.

Examples

The following example matches only 'Sylvester':

SELECT dog_name
FROM Dogs
WHERE dog_name LIKE 'Sylvester';

The following example is not case-sensitive; it uses the LOWER() function to treat 'Sylvester', 'sylvester', 'SYLVESTER', and so on as all lowercase, and matches them with the all lowercase string, 'sylvester':

SELECT dog_name
FROM Dogs
WHERE LOWER(dog_name) LIKE 'sylvester';

If you use a variable on the right side of the LIKE conditional and want to ensure that the comparison is not case-sensitive, use the LCase or UCase function to force the variable text to be all of one case, as in the following example:

WHERE LOWER(dog_name) LIKE '#LCase(FORM.SearchString)#';

Escaping wildcards

You can specify your own escape character by using the conditional ESCAPE clause.

Example

The following example uses the ESCAPE clause to enable a search for a literal percent sign (%), which ColdFusion normally interprets as a wildcard character:

SELECT emp_discount
FROM Benefits
WHERE emp_discount LIKE '10\%' 
ESCAPE '\';