sql - 两个几乎相同的查询返回不同的结果

Question

以下两个查询得到不同的结果，但我不知道为什么。唯一的区别是一个有 IN，一个有 equal。

在我进入查询之前，您应该知道，我找到了一种更好的方法，通过将子查询移动到公共(public)表表达式中，但这仍然让我发疯!我真的很想知道到底是什么导致了这个问题，我出于好奇而问

这是第一个查询:

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')

第二个:

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')

第一个查询返回 0 行，而第二个查询返回 2 行。唯一的区别是最后一个 where 子句的 x.UniqProducer = 与 x.UniqProducer IN。

感谢您的宝贵时间

Answer 1

SELECT TOP 1 不保证每次都会返回相同的记录。将 ORDER BY 添加到您的选择中以确保返回相同的记录。

(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)