Cacher is the code snippet organizer for pro developers

<!---
ColdFusion Query of Queries does not natively support OUTER JOINs. 
The following code demonstrates a work around to perform a LEFT OUTER JOIN 
between two CF queries, QueryA and QueryB.

i.e. To do this;
--->

SELECT *
FROM QueryA
LEFT OUTER JOIN QueryB ON QueryA.ID = QueryB.ID
One can use;

<cfquery name="joinQuery" dbtype="query" >
SELECT *
FROM QueryB
WHERE QueryB.ID = -1
</cfquery>

<cfset QueryAddRow(joinQuery) />

<cfquery name="result" dbtype="query" >
SELECT *
FROM QueryA, QueryB
WHERE QueryA.ID = QueryB.ID

UNION

SELECT QueryA.*, joinQuery.*
FROM QueryA, joinQueryWHERE QueryA.ID NOT IN (#ValueList(QueryB.ID)#)
</cfquery>

<!---
A common use of an OUTER JOIN is to find the non-matching records between 
two record sets, a so called NULL Based OUTER JOIN.

e.g.
--->

SELECT *
FROM QueryA
LEFT OUTER JOIN QueryB ON QueryA.ID = QueryB.ID
WHERE QueryB.ID IS NULL

<!---
This situation can be more efficiently implemented in Query of Queries by using 
the following technique;
--->

SELECT *
FROM QueryA
WHERE QueryA.ID NOT IN (#ValueList(QueryB.ID)#)