How to do an OUTER JOIN in Query of Queries
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, joinQuery WHERE 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)#)

July 17th, 2007 at 11:52 pm
Doesn’t like
SELECT *,NULL,NULL
Query Of Queries syntax error.
Encountered “NULL. Incorrect Select List, Incorrect select column
BUT since I need a specific result and know what column types are needed, your code showed the way. THANKS
July 19th, 2007 at 9:56 pm
@ Pat
Thanks for spotting that.
I have updated the post to correct the issue. It makes it a bit more complicated, but at least it works
June 29th, 2008 at 11:32 pm
hi,
the code above is not the finished product right? i am trying to replicate it but on the line:
SELECT QueryA.*, joinQuery
joinquery is not a collumn therefore it is producing an error.
Also, you don’t specify a join condition in the second query of your UNION statement, which means that it’ll return a Cartesian product
thanks
August 2nd, 2008 at 3:58 pm
@Richard
Yes that’s a typo, it should be SELECT QueryA.*, joinQuery.* I’ve updated the post.
The CROSS JOIN in the second query is used to include the empty columns from the joinQuery to the filtered results from QueryA. This is done to give the results set of a LEFT OUTER JOIN.
I should also state that this technique assumes that the joining columns (.ID) can never have a value of -1. e.g. They are auto-numbers of a primary key.
February 2nd, 2009 at 1:35 pm
I dont get it.
I am just going through the logic but dont understand why do this:
SELECT *
FROM QueryB
WHERE QueryB.ID = -1
When it return zero rows as no ID matches -1
and then just add 1 row to it.
Can someone enlighten me or am i missing something here…
February 3rd, 2009 at 8:48 pm
@Matt – With UNION you need the same amount of columns in the second query result as the first, hence the creation of a “null row” in the first few lines and a join with that row to the second query. I was kinda wondering that myself before I looked at UNION here: http://www.w3schools.com/sql/sql_union.asp
May 26th, 2009 at 5:43 pm
Also, as a side note…. if the join field is a string, you’ll need to change the “ValueList” to “QuotedValueList”
But thanks for this great code. I just ran it on Portable Blue Dragon/Jetty, and it works fine.
July 31st, 2009 at 7:10 pm
I’ve got this set up but the output returns duplicate rows of the list in NOT IN statement rows in addition to the originally selected rows – just without the additional data
Any thoughts?
SELECT *
FROM joinclientquery, GetLossData
WHERE GetLossData.client_number1 = joinclientquery.cclientid
UNION
SELECT joinclientquery.*, joinQuery.*
FROM joinclientquery, joinQuery
WHERE joinclientquery.cclientid NOT IN (#quotedValueList(GetLossData.client_number1)#)
order by cclientname
sample output
100386 457,466 0 0
100386 100386 1 1 0 457,466 0 0
July 5th, 2010 at 2:53 pm
is this still not supported in cf9?
July 5th, 2010 at 3:06 pm
How do you do this if you have 2 join condions?
cond1 & cont2
cheers
July 8th, 2010 at 11:37 am
@Nikos
No its not in CF9
You should just be able to add extra conditions to the main WHERE expression.