Coldfusion Query OF Query Generates more records than expected

Question

This one has really stumped me

I have 3 queries

Query 1:

Complete query with series of where statements

Query 2 is query 1 ( minus all there where statements ) as I decide to create a query or queries instead

query 3 is the query of query where it references query 2 in the from statement and then has the where statements to filter the results received from query 2

I would expect the results from query 1 and 3 to be identical yet the query of queries outputs nearly 1500 records more than query 1

any advise suggestions why please enlighten me

<CFQUERY datasource="#Application.Workflow#" name="query1">
SELECT 
                * 
FROM
                Campaigns
INNER JOIN         
                CampaignReceipients
ON
                Campaigns.CampaignId=CampaignReceipients.CampaignId
WHERE
                Campaigns.CampaignId  = 191
AND
                ClientMobilePhone <> ''
AND
                ClientEmailAddress like '%@%'
AND
                ClientBusinessPhone is null 
AND
                ClientHomePhone is null 
AND 
                AssignedToDate is null
</CFQUERY>      
                
<CFQUERY datasource="#Application.Workflow#" name="query2">
SELECT 
                * 
FROM
                Campaigns
INNER JOIN         
                CampaignReceipients
ON
                Campaigns.CampaignId=CampaignReceipients.CampaignId
WHERE
                Campaigns.CampaignId  = 191
</CFQUERY>
                
<cfquery dbtype="query" name="query3">
SELECT 
                *
FROM
                query2
WHERE 
                ClientMobilePhone <> ''
AND
                ClientEmailAddress like '%@%'
AND
                ClientBusinessPhone is null 
AND
                ClientHomePhone is null 
AND 
                AssignedToDate is null
</cfquery>         

Answer 1

Without seeing your data or your DB server type, I would still be confident of this being due to how CF query of queries handles NULL values in string comparisons.

With your original datasource level query, the comparison WHERE ClientMobilePhone <> '' will ignore all records WHERE ClientMobilePhone IS NULL.

Whereas with CF QoQ, WHERE ClientMobilePhone <> '' will return records where ClientMobilePhone is either NULL or not empty string.

SQL ISNULL() / IFNULL() is not supported here, but you can test for NULL separately; you can confirm if this is definitely what is happening, by adding a fourth query to the code in your question:

<cfquery dbtype="query" name="query4">
SELECT 
                *
FROM
                query2
WHERE 
                ClientMobilePhone <> '' AND ClientMobilePhone IS NOT NULL
AND
                ClientEmailAddress like '%@%'
AND
                ClientBusinessPhone is null 
AND
                ClientHomePhone is null 
AND 
                AssignedToDate is null
</cfquery>