Query of Queries user guide

The following sections discuss Query of Queries functionality. If you know SQL or have interacted with databases, you might be familiar with some of these features.

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 tables. The re are two main types of JOIN operations:

ColdFusion does not support OUTER JOINs, nor does it support the INNER JOIN syntax, as the following example shows:

SELECT Dog_ID, Breed_ID,
FROM Dogs INNER JOIN Breed
ON Dogs.Dog_ID = Breed.Dog_ID;

ColdFusion supports INNER JOINs between two tables, as the following example shows. This operation is the most common type of join.

SELECT Dog_ID, Breed_ID
FROM Dogs, Breed
WHERE Dogs.Dog_ID = Breed.Dog_ID;

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:

Note:   Query Of Queries does not support ODBC-formatted dates and times.

Syntax

select_expression = select_expression UNION [ALL] select_expression

Example

This example uses the following tables:
Table1
Type(int)
Name(varchar)
1
Tennis
2
Baseball
3
Football

Table2
ID(int)
Sport(varchar)
3
Football
4
Volleyball
5
PingPong

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:
Result table
Type(int)
Name(varchar)
1
Tennis
2
Baseball
3
Football
4
Volleyball
5
PingPong

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 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:

Note:   Earlier versions of ColdFusion do not support bracketed ranges.

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

ColdFusion 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 match 'Sylvester', 'sylvester', 'SYLVESTER', and so on:

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

Escaping wildcards

You can specify your own escape character 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 '\';

Using aggregate functions

Aggregate functions operate on a set of data and return a single value. Use these functions for retrieving summary information from a table, as opposed to retrieving an entire table and then operating on the record set of the entire table.

Consider using aggregate functions to perform the following operations:

Since not every relational database management system (RDBMS) supports all aggregate functions, refer to your database's documentation. The following table lists the aggregate functions that ColdFusion supports:
Function
Description
AVG()
Returns the average (mean) for a column.
COUNT()
Returns the number of rows in a column.
MAX()
Returns the largest value of a column.
MIN()
Returns the lowest value of a column.
SUM()
Returns the sum of values of a column.

Note:   For more information, see CFML Reference.

Syntax

aggregate_func ::= <COUNT>(* | column_name) | AVG | SUM | MIN | MAX)
([ALL | DISTINCT] numeric_exp)

Example

The following example uses the AVG() function to retrieve the average IQ of all terriers:

SELECT dog_name, AVG(dog_IQ) AS avg_IQ
FROM Dogs
WHERE breed LIKE '%Terrier';

Arbitrary expressions in aggregate functions

ColdFusion supports aggregate functions of any arbitrary expression, as follows:

SELECT lorange, count(lorange+hirange) 
FROM roysched
GROUP BY lorange;

Aggregate functions in arbitrary expressions

ColdFusion supports mathematical expressions that include aggregate functions, as follows:

SELECT MIN(lorange) + MAX(hirange)
FROM roysched
GROUP BY lorange;

Using group by and having expressions

ColdFusion supports the use of any arbitrary arithmetic expression, as long as it is referenced by an alias.

Examples

The following code is correct:

SELECT (lorange + hirange)/2 AS midrange,
COUNT(*)
FROM roysched
GROUP BY midrange;

The following code is correct:

SELECT (lorange+hirange)/2 AS x, 
COUNT(*)
FROM   roysched GROUP BY x 
HAVING x > 10000;

The following code is not supported in Query of Queries:

SELECT (lorange + hirange)/2 AS midrange,
COUNT(*)
FROM roysched
GROUP BY (lorange + hirange)/2;

Using ORDER BY clauses

ColdFusion supports the ORDER BY clause to sort. Make sure that it is the last clause in your SELECT statement. You can sort by multiple columns, by relative column position, by nonselected columns. You can specify a descending sort direction with the DESC keyword (by default, most RDBMS sorts are ascending, which makes the ASC keyword unnecessary).

Syntax

order_by_column ::= ( <IDENTIFIER> | <INTEGER_LITERAL> ) [<ASC> | <DESC>]

Examples

The following example shows a simple sort using an ORDER BY clause:

SELECT acetylcholine_levels, dopamine_levels
FROM results
ORDER BY dopamine_levels

The following example shows a more complex sort; results are first sorted by ascending levels of dopamine, then by descending levels of acetylcholine. The ASC keyword is unnecessary, and is used only for legibility.

SELECT acetylcholine_levels, dopamine_levels
FROM results
ORDER BY 2 ASC, 1 DESC

Using aliases

ColdFusion supports the use of database column aliases. An alias is an alternate name for a database field or value. ColdFusion lets you reuse an alias in the same SQL statement.

One way to create an alias is to concatenate (append) two or more columns to generate a value. For example, you can concatenate a first name and a last name to create the value fullname. Because the new value does not exist in a database, you refer to it by its alias. The AS keyword assigns the alias in the SELECT statement.

Examples

ColdFusion supports alias substitutions in the ORDER BY, GROUP BY, and HAVING clauses.

Note:   ColdFusion does not support aliases for table names.

SELECT FirstName + ' ' + LastName AS fullname
from Employee;

The following examples rely on these two master queries:

<cfquery name="employee" datasource="2pubs">
  SELECT * FROM employee
</cfquery>

<cfquery name="roysched" datasource="2pubs">
  SELECT * FROM roysched
</cfquery>

To generate output for the following examples, use the cfdump tag. For example, use <cfdump var="#order_by#"> for the following ORDER BY example.

ORDER BY example

<cfquery name="order_by" dbtype="query">
  SELECT (job_id + job_lvl)/2 AS job_value
  FROM employee 
  ORDER BY job_value
</cfquery>

GROUP BY example

<cfquery name="group_by" dbtype="query">
  SELECT     lorange+hirange AS x, count(hirange) 
  FROM     roysched 
  GROUP BY   x
</cfquery>

HAVING example

<cfquery name="having" dbtype="query">
  SELECT (lorange+hirange)/2 AS x, 
  COUNT(*)
  FROM   roysched GROUP BY x 
  HAVING x > 10000
</cfquery>

Handling null values

ColdFusion uses Boolean logic to handle conditional expressions. Proper handling of NULL values requires the use of ternary logic. The IS [NOT] NULL clause works correctly in ColdFusion. However the following expressions do not work properly when the column breed is NULL:

WHERE (breed > 'A')
WHERE NOT (breed > 'A') 

The correct behavior should not include NULL breed columns in the result set of either expression. To avoid this limitation, you can add an explicit rule to the conditionals and rewrite them in the following forms:

WHERE breed IS NOT NULL AND (breed > 'A')
WHERE breed IS NOT NULL AND not (breed > 'A')

Escaping reserved keywords

ColdFusion has a list of reserved keywords, which are typically part of the SQL language and are not normally used for names of columns or tables. To escape a reserved keyword for a column name or table name, enclose it in brackets.

Caution:   Earlier versions of ColdFusion let you use some reserved keywords without escaping them.

Examples

ColdFusion supports the following SELECT statement examples:

SELECT [from] FROM parts;
SELECT [group].firstname FROM [group];
SELECT [group].[from] FROM [group];

ColdFusion does not support nested escapes, such as in the following example:

SELECT [[from]] FROM T;

The following table lists ColdFusion reserved keywords:
ABSOLUTE
ACTION
ADD
ALL
ALLOCATE
ALTER
AND
ANY
ARE
AS
ASC
ASSERTION
AT
AUTHORIZATION
AVG
BEGIN
BETWEEN
BIT
BIT_LENGTH
BOTH
BY
CASCADE
CASCADED
CASE
CAST
CATALOG
CHAR
CHARACTER
CHARACTER_LENGTH
CHAR_LENGTH
CHECK
CLOSE
COALESCE
COLLATE
COLLATION
COLUMN
COMMIT
CONNECT
CONNECTION
CONSTRAINT
CONSTRAINTS
CONTINUE
CONVERT
CORRESPONDING
COUNT
CREATE
CROSS
CURRENT
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_USER
CURSOR
DATE
DAY
DEALLOCATE
DEC
DECIMAL
DECLARE
DEFAULT
DEFERRABLE
DEFERRED
DELETE
DESC
DESCRIBE
DESCRIPTOR
DIAGNOSTICS
DISCONNECT
DISTINCT
DOMAIN
DOUBLE
DROP
ELSE
END
END-EXEC
ESCAPE
EXCEPT
EXCEPTION
EXEC
EXECUTE
EXISTS
EXTERNAL
EXTRACT
FALSE
FETCH
FIRST
FLOAT
FOR
FOREIGN
FOUND
FROM
FULL
GET
GLOBAL
GO
GOTO
GRANT
GROUP
HAVING
HOUR
IDENTITY
IMMEDIATE
IN
INDICATOR
INITIALLY
INNER
INPUT
INSENSITIVE
INSERT
INT
INTEGER
INTERSECT
INTERVAL
INTO
IS
ISOLATION
JOIN
KEY
LANGUAGE
LAST
LEADING
LEFT
LEVEL
LIKE
LOCAL
LOWER
MATCH
MAX
MIN
MINUTE
MODULE
MONTH
NAMES
NATIONAL
NATURAL
NCHAR
NEXT
NO
NOT
NULL
NULLIF
NUMERIC
OCTET_LENGTH
OF
ON
ONLY
OPEN
OPTION
OR
ORDER
OUTER
OUTPUT
OVERLAPS
PAD
PARTIAL
POSITION
PRECISION
PREPARE
PRESERVE
PRIMARY
PRIOR
PRIVILEGES
PROCEDURE
PUBLIC
READ
REAL
REFERENCES
RELATIVE
RESTRICT
REVOKE
RIGHT
ROLLBACK
ROWS
SCHEMA
SCROLL
SECOND
SECTION
SELECT
SESSION
SESSION_USER
SET
SIZE
SMALLINT
SOME
SPACE
SQL
SQLCODE
SQLERROR
SQLSTATE
SUBSTRING
SUM
SYSTEM_USER
TABLE
TEMPORARY
THEN
TIME
TIMESTAMP
TIMEZONE_HOUR
TIMEZONE_MINUTE
TO
TRAILING
TRANSACTION
TRANSLATE
TRANSLATION
TRIM
TRUE
UNION
UNIQUE
UNKNOWN
UPDATE
UPPER
USAGE
USER
USING
VALUE
VALUES
VARCHAR
VARYING
VIEW
WHEN
WHENEVER
WHERE
WITH
WORK
WRITE
YEAR
ZONE



Comments