CODEWITHSUNDEEP
sqlsql-server-2008t-sqldynamics-crm-2011
Alex Gordon
Alex Gordon

Reputation: 60811

using LEFT JOIN produces different results than using IN

I am wondering whether you can uncover this mystery of why these two queries, that should be producing the same exact results, are in fact producing different results. The first query is producing the incorrect result.

The following query shows that o.OwnerId=b.systemuserid is never true:

select distinct o.assignedto,b.systemuserid 
  from Opportunity o
  left join crmtestdb.dm1_mscrm.dbo.systemuserbase b
    on o.OwnerId = b.systemuserid

because it returns all nulls on the right side:

enter image description here

Whereas this query shows that o.OwnerId=b.systemuserid is indeed true for some records:

select distinct assignedto 
  from Opportunity
 where assignedto in (select distinct systemuserid 
                       from crmtestdb.dm1_mscrm.dbo.systemuserbase)

and this shows that they do have those fields in common:

enter image description here

What's going on here? What am I doing wrong? Please let me know if you need clarification on anything.

Upvotes: 1

Views: 134

Answers (1)

Zane
Zane

Reputation: 4169

select distinct 
    o.assignedto,
    b.systemuserid 
from Opportunity o
left join  crmtestdb.dm1_mscrm.dbo.systemuserbase b
    on o.OwnerId=b.systemuserid 
    --here you are matching Onwer to sysuser

select distinct 
    assignedto 
from Opportunity
where assignedto in (
    select distinct 
        systemuserid 
        --here you are matching assignedto to sysuser
        --this is not equivilant
    from crmtestdb.dm1_mscrm.dbo.systemuserbase
    )

You are not matching on the same things. In one query you are selecting Assignedto and matching OwnerID to sytemuserID in the second you are searching where assigned to is IN systemuserId Try changing the second query to

select distinct 
    assignedto 
from Opportunity
  where OwnerId in (
    select distinct 
        systemuserid 
    from crmtestdb.dm1_mscrm.dbo.systemuserbase
    )

Upvotes: 6

Related Questions