Query of Queries user guide

Last update:

May 18, 2026

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

Using dot notation

Query of Queries 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:

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 tables with a primary key - foreign key relationship. The two SQL clauses that perform joins are:

WHERE clause: Query of Queries supports joins through a WHERE clause.
INNER JOIN and OUTER JOIN: Query of Queries does not support joins through INNER JOIN or OUTER JOIN clauses.

Note:

Query of Queries supports joins between two tables only.

Using unions

The UNION operator lets you combine the results of two or more SELECT expressions into a single recordset. 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

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)

Tennis

Baseball

Football

Table2

ID(int)

Sport(varchar)

Football

Volleyball

PingPong

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

UNION SELECT * FROM Table2

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

Result table

Type(int)

Name(varchar)

Tennis

Baseball

Football

Volleyball

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; Query of Queries 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:

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:

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

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



WHERE _isValid IS true;

Null conditional

This conditional tests whether an expression is null.

Syntax

null_cond ::= expression IS [NOT] NULL

Example

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 recordset.

Syntax

comparison_cond ::= expression [= | > | >= | <> | != | < | <=] expression

Example

The following example uses a comparison conditional to retrieve only those dogs whose IQ is at least 150:

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 recordset. Like the comparison conditional, the BETWEEN conditional also compares; however, the BETWEEN conditional compares against a range of values. Therefore, its syntax requires two values, which are inclusive, a minimum and a maximum. 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:

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:

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.
Brackets [ ] represent any character in the range.
Brackets with a caret represent any character not in the range.
All other characters represent themselves.

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:

FROM Dogs

WHERE breed LIKE '%Terrier';

The following examples are select statements that use bracketed ranges:

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

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:

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

FROM Benefits

WHERE emp_discount LIKE '10\%'

ESCAPE '\';

Managing data types for columns

A Query of Queries requires that every column has metadata that defines the data type of the column. All queries that ColdFusion creates have metadata. However, a query created with QueryNewfunction that omits the second parameter does not contain metadata. You use this optional second parameter to define the data type of each column in the query.

Specify column data types in the QueryNew function

Type a QueryNewfunction, specifying the column names in the first parameter and the data types in the second parameter, as the following example shows:

<cfset qInstruments = queryNew("name, instrument, years_playing", "CF_SQL_VARCHAR, CF_SQL_VARCHAR, CF_SQL_INTEGER")>

Note:

To see the metadata for a Query of Queries, use the GetMetaDatafunction.

Specify the column data types in the QueryAddColumn function

Create a query by specifying the QueryNew function with no parameters.

<cfset myQuery = QueryNew("")>

Add and populate a column with the QueryAddColumn function, specifying the data type in the third parameter:

<cfset FastFoodArray = ArrayNew(1)>

<cfset FastFoodArray[1] = "French Fries">

<cfset FastFoodArray[2] = "Hot Dogs">

<cfset FastFoodArray[3] = "Fried Clams">

<cfset FastFoodArray[4] = "Thick Shakes">

<!--- Use the array to add a column to the query. --->

<cfset nColumnNumber = QueryAddColumn(myQuery, "FastFood", "CF_SQL_VARCHAR",

FastFoodArray)>

If you do not specify the data type, ColdFusion examines the first 50 rows of each column to determine the data type when performing conditional expressions. In some cases, ColdFusion can guess a data type that is inappropriate for your application. In particular, if you use columns in a WHERE clause or other conditional expression, the data types must be compatible. If they are not compatible, use the CAST function to recast one of the columns to a compatible data type. For more information on casting, see Using the CAST function below.

For more information on data type compatibility, see Understanding Query of Queries processing.

Note:

Specifying the data type in the QueryNew function helps you avoid compatibility issues.

Using the CAST function

In some cases, the data type of a column is not compatible with the processing you want to do. For example, query columns returned by the cfhttp tag are all of type CF_SQL_VARCHAR, even if the contents are numeric. In this case, use the Query of Queries CAST function to convert a column value into an expression of the correct data type. The syntax for the CAST function is as follows:

CAST ( expression AS castType )

Where castType is one of the following:

BINARY
BIGINIT
BIT
DATE
DECIMAL
DOUBLE
INTEGER
TIME
TIMESTAMP
VARCHAR For example:

<cfhttp url="http://quote.yahoo.com/download/quotes.csv?Symbols=csco,jnpr&format=sc1l1&ext=.csv" 

method="GET" 

name="qStockItems" 

columns="Symbol,Change,LastTradedPrice" 

textqualifier="""" 

delimiter="," 

firstrowasheaders="no"> 

<cfoutput> 

<cfdump var="#qStockItems#"> 

<cfdump var="#qStockItems.getColumnNames()#"> 

</cfoutput> 

<cfoutput> 

<cfloop index="i" from="1" to="#arrayLen(qStockItems.getColumnNames())#"> 

#qStockItems.getMetaData().getColumnTypeName(javaCast("int",i))#<br/> 

</cfloop> 

</cfoutput> 

<cftry> 

<cfquery name="hello" dbtype="query"> 

SELECT SUM(CAST(qStockItems.LastTradedPrice as INTEGER)) 

AS SUMNOW from qStockItems 

</cfquery> 

<cfcatch>Error in Query of Queries</cfcatch> 

</cftry> 

<cfoutput> 

<cfdump var="#hello#"> 

</cfoutput>

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 recordset of the entire table. Consider using aggregate functions to perform the following operations:

To display the average of a column
To count the number of rows for a column
To find the earliest date in a column Since not every relational database management system (RDBMS) supports all aggregate functions, refer to the documentation of your database. The following table lists the aggregate functions that Query of Queries 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.

Syntax



([ALL | DISTINCT] numeric_exp)

Example

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

FROM Dogs

WHERE breed LIKE '%Terrier';

Arbitrary expressions in aggregate functions

Query of Queries supports aggregate functions of any arbitrary expression, as follows:

FROM roysched

GROUP BY lorange;

Aggregate functions in arbitrary expressions

Query of Queries supports mathematical expressions that include aggregate functions, as follows:

FROM roysched

GROUP BY lorange;

Using group by and having expressions

Query of Queries 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;

/ Empty query

emptyQuery = queryNew("id,name");

// Aggregate query

result = queryExecute(

    "SELECT COUNT(id) AS cnt FROM emptyQuery",

    {},

    {dbtype="query"}

);

// CF2021: result.RowCount = 0

// CF2025: result.RowCount = 1, result.cnt = 0

Using ORDER BY clauses

Query of Queries 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>]

Example

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

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.

FROM results

ORDER BY 2 ASC, 1 DESC

Using aliases

Query of Queries supports the use of database column aliases. An alias is an alternate name for a database field or value. Query of Queries 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

Query of Queries supports alias substitutions in the ORDER BY, GROUP BY, and HAVING clauses.

Note:

Query of Queries does not support aliases for table names.

from Employee;

The following examples rely on these two master queries:

SELECT * FROM employee

</cfquery>

<cfquery name="roysched" datasource="2pubs">

SELECT * FROM roysched

</cfquery>

ORDER BY example

SELECT (job_id || job_lvl)/2 AS job_value

FROM employee

ORDER BY job_value

</cfquery>

GROUP BY example

SELECT lorange || hirange AS x, count(hirange)

FROM roysched

GROUP BY x

</cfquery>

HAVING example

SELECT (lorange || hirange)/2 AS x,

COUNT(*)

FROM roysched GROUP BY x

HAVING x > 10000

</cfquery>

Handling null values

Query of Queries 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 Query of Queries. However the following expressions do not work properly when the column breed is NULL:

WHERE NOT (breed > 'A')

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

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

Concatenating strings

Query of Queries support two string concatenation operators: + and ||, as the following examples show:

LASTNAME || ', ' || FIRSTNAME

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.

Note:

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

Examples

Query of Queries supports the following SELECT statement examples:

SELECT [group].firstname FROM [group];

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

Query of Queries 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	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

Using Queries of Queries with dates

If you create a query object with the QueryNew function and populate a column with date constants, ColdFusion stores the dates as a string inside the query object until a Query of Queries is applied to the query object. When ColdFusion applies a Query of Queries to the query object, it converts the string representations into date objects. Query of Queries supports date constants in SQL and ODBC format, as follows:

SQL format: Dates, times, or timestamps in one of the following format:Date string: yyyy -mm-dd, for example, 1955-06-13.Time string: hh:mm:ss[.nnn], for example, 14:34:30.75.Timestamp string: yyyy-mm-dd hh:mm:ss[.nnn], for example, 1924-01-14 12:00:00.000.
ODBC format: Dates, times, or timestamps in one of the following format:Date string: {d 'value'}, for example, {d '2004-07-06'}.Time string: {t 'value'}, for example, {t '13:45:30'}.Timestamp string: {ts 'value'}, for example, {ts '2004-07-06 13:45:30'}.

Note:

If you want to convert the date to its original format, use the DateFormat_ function and apply the "mm/dd/yy" mask._

<cfscript>

    myQuery = queryNew("id,name,amount,revenue","Integer,Varchar,Integer,Integer", 

                [ 

                        {id=1,name="One",amount=15,revenue=35}, 

                        {id=2,name="Two",amount=18,revenue=41}, 

                        {id=3,name="Three",amount=32,revenue=73} 

                ]); 

    //writeOutput("The new query is:")

    // writeDump(myQuery)

</cfscript>

<!--- qoq --->

<cfquery name="newQoQ" dbtype="query">

    select amount&revenue as andColumn

    from myQuery

</cfquery>

<cfdump var="#newQoQ#">

<cfscript>

    myQuery = queryNew("id,name,amount,revenue","Integer,Varchar,Integer,Integer", 

                [ 

                        {id=1,name="One",amount=15,revenue=35}, 

                        {id=2,name="Two",amount=18,revenue=41}, 

                        {id=3,name="Three",amount=32,revenue=73} 

                ]); 

    //writeOutput("The new query is:")

    // writeDump(myQuery)

</cfscript>

<!--- qoq --->

<cfquery name="newQoQ" dbtype="query">

    select amount|revenue as orColumn

    from myQuery

</cfquery>

<cfdump var="#newQoQ#">

cfscript>

    myQuery = queryNew("id,name,amount,revenue","Integer,Varchar,Integer,Integer", 

                [ 

                        {id=1,name="One",amount=15,revenue=35}, 

                        {id=2,name="Two",amount=18,revenue=41}, 

                        {id=3,name="Three",amount=32,revenue=73} 

                ]); 

    //writeOutput("The new query is:")

    // writeDump(myQuery)

</cfscript>

<!--- qoq --->

<cfquery name="newQoQ" dbtype="query">

    select amount^revenue as xorColumn

    from myQuery

</cfquery>

<cfdump var="#newQoQ#">

<cfscript>

    myQuery = queryNew("id,name,amount,revenue","Integer,Varchar,Integer,Integer", 

                [ 

                        {id=1,name="One",amount=15,revenue=35}, 

                        {id=2,name="Two",amount=18,revenue=41}, 

                        {id=3,name="Three",amount=32,revenue=73} 

                ]); 

    //writeOutput("The new query is:")

    // writeDump(myQuery)

</cfscript>

<!--- qoq --->

<cfquery name="newQoQ" dbtype="query">

    select ~revenue as compColumn

    from myQuery

</cfquery>

<cfdump var="#newQoQ#">

<cfscript> 

    // left shift

    myQuery = queryNew("id,name,amount,revenue","Integer,Varchar,Integer,Integer",  

    [  

        {id=1,name="One",amount=15,revenue=35},  

        {id=2,name="Two",amount=18,revenue=41},  

        {id=3,name="Three",amount=32,revenue=73}  

    ]);  //writeOutput("The new query is:") // writeDump(myQuery) 

</cfscript> <!--- qoq ---> 

<cfquery name="newQoQ" dbtype="query"> 

    select revenue<<1 as slColumn from myQuery 

</cfquery> 

<cfdump var="#newQoQ#">

<cfscript>

    // right shift

    myQuery = queryNew("id,name,amount,revenue","Integer,Varchar,Integer,Integer",  

    [  

        {id=1,name="One",amount=15,revenue=35},  

        {id=2,name="Two",amount=18,revenue=41},  

        {id=3,name="Three",amount=32,revenue=73}  ]);  //writeOutput("The new query is:") // writeDump(myQuery) 

</cfscript> <!--- qoq ---> 

<cfquery name="newQoQ" dbtype="query"> 

    select revenue>>1 as srColumn from myQuery 

</cfquery> 

<cfdump var="#newQoQ#">

<!--- initial query --->

<cfquery name="initialQuery" datasource="art">

    SELECT artid, artname, description, price

    FROM art

    WHERE price > 50000

</cfquery>

<!--- <cfdump var="#initialQuery#" > --->

<cfquery name="queryOfQueries" dbtype="query">

    SELECT artid,description, price%9 as newPrice

    FROM initialQuery

</cfquery>

<cfdump var="#queryOfQueries#" >

Understanding Query of Queries performance

Query of Queries performs well on single-table query objects that were accessed directly from a database. This happens because ColdFusion stores meta information for a query object accessed from a database. When working with a query resulting in a SQL join, Query of Queries performs as follows:

Query of Queries is efficient for simple joins in which only one equality exists between two column references or constants, for example:

SELECT T1.a, b, c, d FROM T1, T2 WHERE T1.a = T2.a

Query of Queries is less efficient for joins in which the predicate contains multiple expressions, for example:

Query of Queries is less efficient for joins in which the predicate contains multiple expressions, for example:

Understanding Query of Queries processing

Operator precedence in Query of Queries

Starting with ColdFusion (2025.0.08), Query of Queries (QoQ) uses defined operator precedence and left‑to‑right evaluation for operators of the same precedence. This brings QoQ behavior in line with standard SQL and removes earlier inconsistencies in chained expressions.

Prior to this change:

QoQ did not define precedence rules explicitly.
Expressions with multiple operators of the same precedence (for example, a - b - c) were effectively evaluated right‑to‑left, which did not match SQL behavior or user expectations.
Developers often had to rely on extra parentheses to force the desired result.

From ColdFusion 2025 onward, QoQ:

Applies SQL‑style precedence rules to arithmetic, comparison, and logical operators.
Evaluates operators of the same precedence left‑to‑right.
Continues to honor parentheses, which always override default precedence.

Important: This is a behavioral change. Existing QoQ code that relied on the older, right‑to‑left evaluation of chained operators may produce different results in ColdFusion 2025 and later if it did not use parentheses explicitly.

Precedence and evaluation rules

General rules

Parentheses are evaluated first and always override default precedence.
Operators with higher precedence are evaluated before operators with lower precedence.
Operators with the same precedence are evaluated left‑to‑right.

2. Simplified precedence order in QoQThe following table shows a simplified precedence ordering for commonly used QoQ operators:

Precedence (high → low)	Operator category	Examples
Highest	Parentheses	`( … )`
High	Multiplicative	`*`, `/`, `%` (modulus)
Medium	Additive	`+`, `-`
Medium‑low	Comparison	`=`, `<>`, `<`, `>`, `<=`, `>=`
Low	Logical AND	`AND`
Lowest	Logical OR	`OR`

Note: This table is intentionally simplified to focus on numeric/comparison/logical operators most relevant to QoQ filters and projections. Precedence of other operators and functions follows standard SQL rules unless otherwise documented.

Behavior examples

The following examples illustrate how QoQ now evaluates expressions with the new precedence rules.

Example 1: Three‑operand subtractionPreviously, chained subtraction in QoQ could behave as if evaluated right‑to‑left:

<cfset test = queryNew(

    "qtyOnHand, qty1, qty2",

    "CF_SQL_INTEGER, CF_SQL_INTEGER, CF_SQL_INTEGER"

)>

<cfset queryAddRow(test, 1)>

<cfset querySetCell(test, "qtyOnHand", 96, 1)>

<cfset querySetCell(test, "qty1", 64, 1)>

<cfset querySetCell(test, "qty2", 32, 1)>

<cfquery name="result" dbtype="query">

    SELECT qtyOnHand, qty1, qty2,

           qtyOnHand - qty1 - qty2 AS qty3

    FROM test

</cfquery>

<cfoutput query="result">

    #qtyOnHand# #qty1# #qty2# #qty3#

</cfoutput>

Before ColdFusion 2025 (older QoQ behavior):

96 - (64 - 32) = 64

ColdFusion 2025.0.08 onwards (correct SQL‑style behavior):

(96 - 64) - 32 = 0

Expected output:

96 64 32 0

Because - operators have the same precedence, the expression is evaluated left‑to‑right.

Example 2: Addition chain (left‑to‑right)

<cfset q = queryNew(

    "a, b, c",

    "CF_SQL_INTEGER, CF_SQL_INTEGER, CF_SQL_INTEGER"

)>

<cfset queryAddRow(q, 1)>

<cfset querySetCell(q, "a", 10, 1)>

<cfset querySetCell(q, "b", 20, 1)>

<cfset querySetCell(q, "c", 5, 1)>

<cfquery name="result" dbtype="query">

    SELECT a + b + c AS total

    FROM q

</cfquery>

<cfoutput query="result">

    #total#

</cfoutput>

Evaluation:

(10 + 20) + 5 = 35

Expected result:

Example 3: Mixed addition and subtraction

<cfset q = queryNew(

    "a, b, c",

    "CF_SQL_INTEGER, CF_SQL_INTEGER, CF_SQL_INTEGER"

)>

<cfset queryAddRow(q, 1)>

<cfset querySetCell(q, "a", 10, 1)>

<cfset querySetCell(q, "b", 20, 1)>

<cfset querySetCell(q, "c", 5, 1)>

<cfquery name="result" dbtype="query">

    SELECT a - b + c AS result

    FROM q

</cfquery>

<cfoutput query="result">

    #result#

</cfoutput>

+ and - share the same precedence, so the expression is evaluated left‑to‑right:

(10 - 20) + 5 = -10 + 5 = -5

Expected result:

-5

Example 4: Parentheses (still required to override precedence)Parentheses continue to work as before and always take precedence over default rules:

<cfset test = queryNew(

    "qtyOnHand, qty1, qty2",

    "CF_SQL_INTEGER, CF_SQL_INTEGER, CF_SQL_INTEGER"

)>

<cfset queryAddRow(test, 1)>

<cfset querySetCell(test, "qtyOnHand", 96, 1)>

<cfset querySetCell(test, "qty1", 64, 1)>

<cfset querySetCell(test, "qty2", 32, 1)>

<cfquery name="result" dbtype="query">

    SELECT qtyOnHand - (qty1 + qty2) AS qty3

    FROM test

</cfquery>

<cfoutput query="result">

    #qty3#

</cfoutput>

Evaluation:

96 - (64 + 32) = 96 - 96 = 0

Expected result:

Use parentheses to make complex expressions unambiguous and self‑documenting.Example 5: Comparison operators with arithmeticComparison operators have lower precedence than arithmetic operators. Arithmetic expressions on each side of the comparison are evaluated first:

<cfset q = queryNew(

    "price, discount, threshold",

    "CF_SQL_INTEGER, CF_SQL_INTEGER, CF_SQL_INTEGER"

)>

<cfset queryAddRow(q, 3)>

<cfset querySetCell(q, "price",     100, 1)>

<cfset querySetCell(q, "discount",   10, 1)>

<cfset querySetCell(q, "threshold",  80, 1)>

<cfset querySetCell(q, "price",      50, 2)>

<cfset querySetCell(q, "discount",    5, 2)>

<cfset querySetCell(q, "threshold",  80, 2)>

<cfset querySetCell(q, "price",      90, 3)>

<cfset querySetCell(q, "discount",   15, 3)>

<cfset querySetCell(q, "threshold",  80, 3)>

<cfquery name="result" dbtype="query">

    SELECT price, discount, threshold

    FROM q

    WHERE price - discount > threshold

</cfquery>

<cfoutput query="result">

    Price: #price#, Discount: #discount#, Net: #price - discount#

</cfoutput>

Evaluations:

Row 1: 100 - 10 > 80 → 90 > 80 → TRUE
Row 2: 50 - 5 > 80 → 45 > 80 → FALSE
Row 3: 90 - 15 > 80 → 75 > 80 → FALSE

Expected output:

Price: 100, Discount: 10, Net: 90

Example 6: Logical operators (AND / OR)

Logical operators follow the usual precedence rules: AND has higher precedence than OR. Within the same operator, evaluation is left‑to‑right.

<cfset q = queryNew(

    "stock, minStock, maxStock, active",

    "CF_SQL_INTEGER, CF_SQL_INTEGER, CF_SQL_INTEGER, CF_SQL_BIT"

)>

<cfset queryAddRow(q, 3)>

<cfset querySetCell(q, "stock",    50, 1)>

<cfset querySetCell(q, "minStock", 10, 1)>

<cfset querySetCell(q, "maxStock", 100, 1)>

<cfset querySetCell(q, "active",   1, 1)>

<cfset querySetCell(q, "stock",     5, 2)>

<cfset querySetCell(q, "minStock", 10, 2)>

<cfset querySetCell(q, "maxStock", 100, 2)>

<cfset querySetCell(q, "active",   1, 2)>

<cfset querySetCell(q, "stock",    80, 3)>

<cfset querySetCell(q, "minStock", 10, 3)>

<cfset querySetCell(q, "maxStock", 100, 3)>

<cfset querySetCell(q, "active",   0, 3)>

<cfquery name="result" dbtype="query">

    SELECT stock

    FROM q

    WHERE stock >= minStock

      AND stock <= maxStock

      AND active = 1

</cfquery>

<cfoutput query="result">

    In-stock and active: #stock#

</cfoutput>

Evaluations:

Row 1: 50 >= 10 AND 50 <= 100 AND active = 1 → TRUE AND TRUE AND TRUE → TRUE
Row 2: 5 >= 10 → FALSE (entire condition is FALSE)
Row 3: active = 1 is FALSE.

Expected output:

In-stock and active: 50

Query of Queries can process column comparisons.

Comparing columns with different data types

Starting with ColdFusion MX 7, ColdFusion includes enhancements that allow you to compare columns with different data types. If one of the operands has a known column type (only constants have an unknown column type), Query of Queries tries to coerce the constant with an unknown type to the type of the operand with metadata. The pairs of allowed coercions are as follows:

Binary, string
Dates, string
Numeric, bigdecimal
Boolean, numeric That is, ColdFusion can coerce between binary and string, but not between date and string. If both operands have known data types, the types must be the same. The only exception is that ColdFusion can coerce among integer, float, and double. If both operands are constants, ColdFusion tries to coerce the values, first to the most restrictive type, then to the least restrictive type.

First to binary then to string.
First to date then to string.
First to boolean then to numeric.

Was this page helpful?

We're glad. Tell us how this page helped.

Found the answer to my problem Understood the instructions Liked the feature

Other suggestions

We're sorry. Can you tell us what didn't work for you?

Didn't find the answer to my problem Couldn't understand the instructions Didn't like the feature

Other suggestions

Thank you for your feedback. Your response will help improve this page.

Was this helpful?

We are sorry the content didn't meet your needs.

Share additional feedback to help us improve.

0/255 | Character limit exceeded.

Thank you so much for sharing your feedback!

Query of Queries user guide

Using dot notation

Example

Using joins

Using unions

Syntax

Example

Using aliases for column names

Duplicate rows and multiple tables

Parentheses and evaluation order

Using other keywords with UNION

Using conditional operators

Test conditional

Syntax

Example

Null conditional

Syntax

Example

Comparison conditional

Syntax

Example

BETWEEN conditional

Syntax

Example

IN conditional

Syntax

Example

LIKE conditional

Syntax

Examples

Case sensitivity

Examples

Escaping wildcards

Example

Managing data types for columns

Specify column data types in the QueryNew function

Using the CAST function

Using aggregate functions

Syntax

Example

Arbitrary expressions in aggregate functions

Aggregate functions in arbitrary expressions

Using group by and having expressions

Examples

Using ORDER BY clauses

Syntax

Example

Using aliases

Examples

Handling null values

Concatenating strings

Escaping reserved keywords

Examples

Using Queries of Queries with dates

Understanding Query of Queries performance

Understanding Query of Queries processing

Operator precedence in Query of Queries

Comparing columns with different data types

On this page