Query of Queries

Query of Queries (QoQ) lets you run a SQL statement against a query result you already have in memory, instead of going back to the database. Set dbtype="query" on <cfquery>, and the FROM clause refers to an existing query variable instead of a database table.

This is useful when you've already fetched a larger result set and need to filter, sort, or reshape it differently in a few places, without running the database query again each time.

Learning Objectives

After completing this lesson, you'll be able to:

Run a query against an existing query result with dbtype="query".
Filter and sort a cached result without a second database round-trip.
Recognize Query of Queries' real limitations — joins, case sensitivity, and UNION restrictions.

Basic Query of Queries

Tag Syntax

<cfquery name="employees" datasource="cfdocexamples">
    SELECT Emp_ID, FirstName, LastName, Department
    FROM Employees
</cfquery>

<cfquery name="sortedByLastName" dbtype="query">
    SELECT FirstName, LastName
    FROM employees
    ORDER BY LastName
</cfquery>

NOTE

sortedByLastName never touches the database — it operates entirely on the employees query result already sitting in memory.

Filtering an Existing Result

Tag Syntax

<cfquery name="engineeringOnly" dbtype="query">
    SELECT FirstName, LastName
    FROM employees
    WHERE Department = 'Engineering'
</cfquery>

Running Against an In-Memory Query

Query of Queries also works on a query you built yourself with queryNew(), not just one pulled from a database — useful for testing, or for querying data assembled from a non-database source.

CFScript

Simulated output — illustrative only, not a live ColdFusion/Lucee server.

NOTE

queryNew() is covered fully in the next lesson — here it's just illustrating that QoQ works on any query, not only ones from a real database.

Real Limitations

Query of Queries is not a full SQL engine — a handful of genuine restrictions are worth knowing before relying on it for anything complex.

Joins only work between two tables at a time, and only via WHERE-clause conditions — not INNER JOIN / OUTER JOIN syntax.
Query of Queries is case-sensitive, unlike the rest of CFML — column and comparison values need to match case exactly.
Individual SELECT statements combined with UNION can't use ORDER BY or COMPUTE.
NOT conditions don't automatically handle NULLs the way you might expect — WHERE NOT (column > 'A') won't include NULL rows; add an explicit column IS NOT NULL if that's needed.
No support for table aliases or nested bracket escapes like [[from]].

Real-World Example: Multiple Views of One Query

A common real use: fetch once, then derive several different views from the same result without hitting the database again for each one.

Tag Syntax

<cfquery name="allOrders" datasource="cfdocexamples">
    SELECT OrderID, CustomerName, Amount, Status
    FROM Orders
</cfquery>

<cfquery name="pendingOrders" dbtype="query">
    SELECT * FROM allOrders WHERE Status = 'Pending'
</cfquery>

<cfquery name="topOrders" dbtype="query">
    SELECT * FROM allOrders ORDER BY Amount DESC
</cfquery>

NOTE

One database trip, then two different derived views — cheaper than running two separate SELECT statements against the database.

Common Beginner Mistakes

Assuming Query of Queries is case-insensitive like normal CFML

It isn't — WHERE Status = 'pending' won't match a value stored as "Pending". Match case exactly, or normalize case in both the data and the comparison.

Trying to use INNER JOIN / OUTER JOIN syntax

Query of Queries only supports two-table joins expressed through WHERE-clause conditions, not standard JOIN syntax.

Expecting NOT conditions to exclude NULLs automatically

WHERE NOT (amount > 100) doesn't behave the way it might in application code — add an explicit amount IS NOT NULL if NULLs need to be excluded.

Best Practices

Use Query of Queries to avoid re-querying the database for multiple derived views of the same result — not as a general-purpose SQL replacement.
Keep case consistency in mind when filtering — normalize with UCASE()/LCASE() in the comparison if the source data's case isn't guaranteed.
For anything beyond simple filtering, sorting, or a two-table join, query the database directly instead of fighting Query of Queries' limitations.

Interview Questions

How do you tell <cfquery> to run against an existing query instead of a database?

Set dbtype="query" — the FROM clause then refers to an existing query variable's name.

Is Query of Queries case-sensitive?

Yes — unlike the rest of CFML, which is generally case-insensitive.

Can Query of Queries join more than two tables?

No — it only supports joins between two tables at a time, expressed via WHERE-clause conditions rather than JOIN syntax.

Summary

In this lesson, you learned how to run SQL against an existing query result with dbtype="query", why that's useful for deriving multiple views from one database fetch, and Query of Queries' real limitations around joins, case sensitivity, and UNION.

What's Next?

The next lesson covers query functions — queryNew, queryAddRow, querySetCell, and the functional queryEach, queryMap, queryFilter, and queryReduce for building and transforming query objects directly.