Faking a Left Outer Join in Query-of-Queries

Just picked up on Rip's post about Query-of-Queries tricks and thought I'd add share a trick I came up with some months ago.

One of the big things missing from Queries-of-Queries, IMHO, is support for Left Outer Joins. You can join two queries together using an "old-style" implied join like so:

<cfquery dbtype="query">
SELECT (fields)
FROM  myQuery1, myQuery2
WHERE myQuery1.fooID = myQuery2.fooID
</cfquery>

...but this "implied join" syntax is only an INNER JOIN - i.e. it will only return rows where both queries have matching rows.

Often, you need the functionality of a LEFT OUTER JOIN. For the example above, a left outer join would return a row for each record in myQuery1, plus each matching record in myQuery2 if a matching row exists, or NULL in the myQuery2 fields if no matching row exists.

For instance, you might want to list all products, with the order numbers of any orders for each product, but if the product has no orders you would still want it to appear in the query. This is a perfect situation for a LEFT OUTER JOIN.

Unfortunately, query-of-queries only supports the implied join syntax, therefore it doesn't support left outer joins. But left outer joins are just so mind-buggeringly useful that I had to find a way to fake them.... hyukhyukhyuk...

Q-of-Q does support the UNION construct, for joining two result sets together. By default, a UNION filters out duplicate rows, unless you specify it as a UNION ALL - which returns all rows including duplicates.

So you do one q-of-q to get all the rows which are matched, and then another q-of-q to get all the rows in your "left" query that DON'T have a matching row in the "right" query, and you UNION ALL them together. There's a little bit of fun you have to go through in order to work out the columns in the "right" query that aren't in the "left" query, and fill them with "NULL" values - especially as you have to work around the fact that you can't really do NULLs....

...anyway, here we go. This code has been written for MX 6.1, and works pretty well on that, and on 7. I'm sure someone could make it more bulletproof if they really wanted to, and it would be fairly simple to introduce support for explicitly-named column types, but frankly, I couldn't be arsed :) Feel free to tinker with at your leisure.

<!------------------------------------------------->
<!--- leftOuterJoin     --->
<!--- Emulates a left outer join in QofQ's --->
<!--- @author : Al Davidson   --->
<!------------------------------------------------->

<cffunction name="leftOuterJoin" access="public" returntype="query" output="yes">
 <cfargument name="qry_left" type="query" required="yes" />
 <cfargument name="qry_right" type="query" required="yes" />
 <cfargument name="sLeftJoinColumn" type="string" required="true" />
 <cfargument name="sRightJoinColumn" type="string" required="true" />
 <cfargument name="sOrderBy" type="string" required="false" default="" />
 
 <cfscript>
  // var'ed struct so that we don't have to var every local variable
  var stLocals = structNew();
  
  // check for an empty left query
  if( arguments.qry_left.recordcount EQ 0 ){
   return arguments.qry_left;
  }
  
  // get all the fields in qry_right that AREN'T in qry_left
  stLocals.lstRightColumns = getUnMatchedListElems( lstFilter=arguments.qry_right.columnlist, lstCompareTo=arguments.qry_left.columnlist );
  
 </cfscript>
 
 <cfquery name="stLocals.qryDistinct" dbtype="query">
  SELECT DISTINCT (#sRightJoinColumn#) AS sValues
  FROM qry_right
 </cfquery>
 <cfset stLocals.lstRValues = valuelist( stLocals.qryDistinct.sValues ) />
 <cfset stLocals.sEmptyClause = "0" />
 
 <!--- numeric or string values? --->
 <cfif NOT isNumeric( stLocals.qryDistinct.sValues[1] )>
  <cfset stLocals.lstRValues = listQualify( stLocals.lstRValues, "'" )  />
  <cfset stLocals.sEmptyClause = "''" />
 </cfif>
 
 <cfif listLen( stLocals.lstRValues ) EQ 0 >
  <Cfset stLocals.lstRValues = stLocals.sEmptyClause />
 </cfif>
 
 <!--- try and guess the right type for each column in qry_right --->
 <!--- by getting the first element in qry_right for each  --->
 <cfset stLocals.lstNullClause = "" />
 <cfloop list="#stLocals.lstRightColumns#" index="stLocals.sCol">
  <cfif isNumeric(arguments.qry_right[stLocals.sCol][1])
   AND compareNoCase( stLocals.sCol, "project_code" )>
   <cfset stLocals.sElem = "0 AS #stLocals.sCol#" />
  <cfelse>
   <cfset stLocals.sElem = "'' AS #stLocals.sCol#" />
  </cfif>
  <cfset stLocals.lstNullClause = listAppend( stLocals.lstNullClause, stLocals.sElem ) />
 </cfloop>
 
 <cfquery name="stLocals.qryUnion" dbtype="query">
  <cfif arguments.qry_right.recordcount GT 0>
  SELECT  qry_left.*,
    #stLocals.lstRightColumns#
  FROM qry_left, qry_right
  WHERE qry_left.#sLeftJoinColumn# = qry_right.#sRightjoinColumn#
  
  UNION ALL
  </cfif>
  SELECT qry_left.*, 
    #stLocals.lstNullClause#
  FROM qry_left
  <cfif arguments.qry_right.recordcount GT 0>
   WHERE qry_left.#sLeftJoinColumn# NOT IN( #stLocals.lstRValues# )
  </cfif>
  <cfif len(Trim(arguments.sOrderby))>
  ORDER BY #arguments.sOrderBy#
  </cfif>
 </cfquery>

 <cfreturn stLocals.qryUnion />
</cffunction>


<!--------------------------------------------------------->
<!--- getUnMatchedListElems    --->
<!--- Returns a list containing all elements of  --->
<!--- lstFilter that are NOT in lstCompareTo  --->
<!--- @author : Al Davidson    --->
<!--------------------------------------------------------->
<cffunction name="getUnMatchedListElems" access="public" returntype="string" output="no">
 <cfargument name="lstFilter" type="string" required="true" />
 <cfargument name="lstCompareTo" type="string" required="true" />
 
 <cfset var stLocals = structNew() />
 <cfset stLocals.lstReturn = "" />
 <cfloop list="#arguments.lstFilter#" index="stlocals.sThisElem">
  <cfif listFindNoCase( arguments.lstCompareTo, stlocals.sThisElem ) EQ 0>
   <cfset stLocals.lstReturn = listAppend( stLocals.lstReturn, stlocals.sThisElem ) />
  </cfif>
 </cfloop>
 <cfreturn stLocals.lstReturn />
</cffunction>