LEFT JOIN on static list of items?

Question

DBMS is intersystems-cache!

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 SELECT DISTINCT() is prohibitively slow. These should never change, so I thought I'd get the list once and just join them statically.

Based on another SO question, here is what I have to replace the SELECT DISTINCT():

    SELECT 'HS.MESSAGE.GATEWAYREGISTRATIONREQUEST' as MessageBodyClassName
    UNION SELECT 'HS.MESSAGE.MERGEPATIENTREQUEST'
    UNION SELECT 'HS.MESSAGE.PATIENTSEARCHREQUEST'

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.

Failing Query

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 > DATEADD(hh, -1, GETUTCDATE())
    ) l ON mh.MessageBodyClassName = l.MessageBodyClassName
GROUP BY mh.MessageBodyClassName

Failed results

MessageBodyClassName                  MessageCount 
------------------------------------- ------------ 
HS.MESSAGE.GATEWAYREGISTRATIONREQUEST 0            
HS.MESSAGE.MERGEPATIENTREQUEST        0            
HS.MESSAGE.PATIENTSEARCHREQUEST       0

---

Working Query

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 > DATEADD(hh, -1, GETUTCDATE())
    ) l ON mh.MessageBodyClassName = l.MessageBodyClassName
GROUP BY mh.MessageBodyClassName

Working and expected results

MessageBodyClassName                  MessageCount 
------------------------------------- ------------ 
HS.MESSAGE.GATEWAYREGISTRATIONREQUEST 0            
HS.MESSAGE.MERGEPATIENTREQUEST        0            
HS.MESSAGE.PATIENTSEARCHREQUEST       54

---

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

Answer 1

The last thing I can think of is to run your DISTINCT query once into a permanent table in your database. That way the inner SELECT in your query will only have to process those three lines. The inner query would lose DISTINCT, like

SELECT MessageBodyClassName FROM ens.messageheader_permvals

EDIT: The below answer did not work

This may be a longshot, but if it doesn't work it might help you diagnose the problem. Instead of the UNION try

SELECT MessageBodyClassName FROM ens.messageheader
    WHERE MessageBodyClassName in (
    'HS.MESSAGE.GATEWAYREGISTRATIONREQUEST',        
    'HS.MESSAGE.MERGEPATIENTREQUEST',          
    'HS.MESSAGE.PATIENTSEARCHREQUEST')

That should return records only if those values actually exist in the table and are compatible with the format of MessageBodyClassName, which we know works using the DISTINCT version. I don't know if the performance will be better this way, but hopefully it will shed some light on the issue.

EDIT: the below answer does not apply, as the OP is was actually trying to select the literal quoted values

You don't have a FROM statements in your UNION query. Try

SELECT 'HS.MESSAGE.GATEWAYREGISTRATIONREQUEST' as MessageBodyClassName
            FROM ens.messageheader
        UNION SELECT 'HS.MESSAGE.MERGEPATIENTREQUEST'
            FROM ens.messageheader
        UNION SELECT 'HS.MESSAGE.PATIENTSEARCHREQUEST'
            FROM ens.messageheader

The rest of the query looks right.