LEFT JOIN on static list of items?
<p><strong>DBMS is <a href="/questions/tagged/intersystems-cache" class="post-tag" title="show questions tagged &#39;intersystems-cache&#39;" rel="tag">intersystems-cache</a>!</strong></p>

<p>Motivation: I need to do a left join on a table so I can get the same list of message types every time, even if the result is zero or null.  Unfortunately, this is a large table so including a <code>SELECT DISTINCT()</code> is prohibitively slow.  These should never change, so I thought I'd get the list once and just join them statically.  </p>

<p>Based on another SO question, here is what I have to replace the <code>SELECT DISTINCT()</code>:</p>

<pre><code>    SELECT 'HS.MESSAGE.GATEWAYREGISTRATIONREQUEST' as MessageBodyClassName
    UNION SELECT 'HS.MESSAGE.MERGEPATIENTREQUEST'
    UNION SELECT 'HS.MESSAGE.PATIENTSEARCHREQUEST'
</code></pre>

<p>This returns results that look exactly as expected, identical to the Distinct query.  However, when I plug this into my JOIN statement,  all the counts come back as zero.</p>

<p><strong>Failing Query</strong></p>

<pre><code>SELECT mh.MessageBodyClassName, count(l.MessageBodyClassName) as MessageCount FROM 
    (
        SELECT 'HS.MESSAGE.GATEWAYREGISTRATIONREQUEST' as MessageBodyClassName
        UNION SELECT 'HS.MESSAGE.MERGEPATIENTREQUEST'
        UNION SELECT 'HS.MESSAGE.PATIENTSEARCHREQUEST'
    ) mh LEFT JOIN
    (
        SELECT messageBodyClassName FROM ens.messageheader WHERE TimeCreated &gt; DATEADD(hh, -1, GETUTCDATE())
    ) l ON mh.MessageBodyClassName = l.MessageBodyClassName
GROUP BY mh.MessageBodyClassName
</code></pre>

<p><strong>Failed results</strong></p>

<blockquote>
<pre><code>MessageBodyClassName                  MessageCount 
------------------------------------- ------------ 
HS.MESSAGE.GATEWAYREGISTRATIONREQUEST 0            
HS.MESSAGE.MERGEPATIENTREQUEST        0            
HS.MESSAGE.PATIENTSEARCHREQUEST       0
</code></pre>
</blockquote>

<hr>

<p><strong>Working Query</strong></p>

<pre><code>SELECT mh.MessageBodyClassName, count(l.MessageBodyClassName) as MessageCount FROM 
    (
        SELECT DISTINCT(MessageBodyClassName) FROM ens.messageheader
    ) mh LEFT JOIN
    (
        SELECT messageBodyClassName FROM ens.messageheader WHERE TimeCreated &gt; DATEADD(hh, -1, GETUTCDATE())
    ) l ON mh.MessageBodyClassName = l.MessageBodyClassName
GROUP BY mh.MessageBodyClassName
</code></pre>

<p><strong>Working and expected results</strong></p>

<blockquote>
<pre><code>MessageBodyClassName                  MessageCount 
------------------------------------- ------------ 
HS.MESSAGE.GATEWAYREGISTRATIONREQUEST 0            
HS.MESSAGE.MERGEPATIENTREQUEST        0            
HS.MESSAGE.PATIENTSEARCHREQUEST       54
</code></pre>
</blockquote>

<hr>

<p>For VKP: <strong>Why are the results different? How can I adjust the first query with literals to get the proper (same) results?</strong></p>