CODEWITHSUNDEEP
sqlsql-server
cjw
cjw

Reputation: 354

Two almost identical queries returning different results

I am getting different results for the following two queries and I have no idea why. The only difference is one has an IN and one has an equals.

Before I go into the queries you should know that I found a better way to do it by moving the subquery into a common table expression, but this is still driving me crazy! I really want to know what caused the issue in the first place, I am asking out of curiosity

Here's the first query:

use [DB.90_39733]
Select distinct x.uniqproducer, cn.Firstname,cn.lastname,e.code,
ecn.FirstName, ecn.LastName, ecn.entid, x.uniqline 
from product x
join employ e on e.EmpID=x.uniqproducer
join contactname cn on cn.uniqentity=e.uniqentity 
join [ETL_GAWR92]..idlookupentity ide on ide.enttype='EM' 
                                                        and ide.UniqEntity=e.UniqEntity
left join [ETL_GAWR92]..EntConName ecn on ecn.entid=ide.empid 
                                      and ecn.opt='Y'
Where x.UniqProducer =(SELECT TOP 1 idl.UniqEntity
                               FROM [ETL_GAWR92]..IDLookupEntity idl
                               LEFT JOIN [ETL_GAWR92]..Employ e2 ON e2.ProdID = ''                 
                               WHERE idl.empID = e2.EmpID   AND
                                     idl.EntType     = 'EM')

And the second one: 

use [DB.90_39733]
    Select distinct x.uniqproducer, cn.Firstname,cn.lastname,e.code,
    ecn.FirstName, ecn.LastName, ecn.entid, x.uniqline 
    from product x
    join employ e on e.EmpID=x.uniqproducer
    join contactname cn on cn.uniqentity=e.uniqentity 
    join [ETL_GAWR92]..idlookupentity ide on ide.enttype='EM' 
                                                            and ide.UniqEntity=e.UniqEntity
    left join [ETL_GAWR92]..EntConName ecn on ecn.entid=ide.empid 
                                          and ecn.opt='Y'
    Where x.UniqProducer IN (SELECT TOP 1 idl.UniqEntity
                                   FROM [ETL_GAWR92]..IDLookupEntity idl
                                   LEFT JOIN [ETL_GAWR92]..Employ e2 ON e2.ProdID = ''                 
                                   WHERE idl.empID = e2.EmpID   AND
                                         idl.EntType     = 'EM')

The first query returns 0 rows while the second query returns 2 rows.The only difference is x.UniqProducer = versus x.UniqProducer IN for the last where clause.

Thanks for your time

Upvotes: 4

Views: 351

Answers (2)

Philip Kelley
Philip Kelley

Reputation: 40319

I would guess (with strong emphasis on the word “guess”) that the reason is based on how equals and in are processed by the query engine. For equals, SQL knows it needs to do a comparison with a specific value, where for in, SQL knows it needs to build a subset, and find if the "outer" value is in that "inner" subset. Yes, the end results should be the same as there’s only 1 row returned by the subquery, but as @RickS pointed out, without any ordering there’s no guarantee of which value ends up “on top” – and the (sub)query plan used to build the in - driven subquery might differ from that used by the equals pull.

A follow-up question: which is the correct dataset? When you analyze the actual data, should you have gotten zero, two, or a different number of rows?

Upvotes: 0

Rick S
Rick S

Reputation: 6586

SELECT TOP 1 doesn't guarantee that the same record will be returned each time. Add an ORDER BY to your select to make sure the same record is returned.

(SELECT TOP 1 idl.UniqEntity
                               FROM [ETL_GAWR92]..IDLookupEntity idl
                               LEFT JOIN [ETL_GAWR92]..Employ e2 ON e2.ProdID = ''                 
                               WHERE idl.empID = e2.EmpID   AND
                                     idl.EntType     = 'EM' ORDER BY idl.UniqEntity)

Upvotes: 4

Related Questions