August 10, 2017 at 3:30 pm
I am trying to determine what I am doing wrong to get different record counts between the 2 below queries. It is my understanding that in the end they should be resulting in the same records returned.
select *
from mkpop
where not exists
(
select *
from wohead where cast(oedate as date) >= '2015-08-09' and oedate is not null and mkpop.kmfg = wohead.kmfg and mkpop.kmodel = wohead.kmodel and mkpop.kserialno1 = wohead.kserialno1 and wohead.kequipnum = mkpop.kequipnum
)
SELECT kmfg , kmodel , kserialno1, kequipnum
from mkpop
except
select kmfg, kmodel, kserialno1, kequipnum
from wohead where cast(oedate as date) >= '2015-08-09' and oedate is not null
August 10, 2017 at 3:46 pm
I think I have answered my own question. The EXCEPT operator returns DISTINCT records where the NOT EXISTS will return all records so when a DISTINCT clause was added to the NOT EXISTS query the records returned match.
August 10, 2017 at 6:20 pm
lucaskhall - Thursday, August 10, 2017 3:46 PMI think I have answered my own question. The EXCEPT operator returns DISTINCT records where the NOT EXISTS will return all records so when a DISTINCT clause was added to the NOT EXISTS query the records returned match.
Your statement about the EXCEPT returning DISTINCT is correct. The INTERSECT and UNION set operators do the same thing. The one that doesn't is UNION ALL.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply