OUTER JOIN WHERE IS NULL vs MINUS vs NOT EXISTS

General
Enthusiast

OUTER JOIN WHERE IS NULL vs MINUS vs NOT EXISTS

Hey,

I am trying to get the rows from table which do not exist in the second table in a period of time,  OUTER JOIN WHERE IS NULL is not given me the same result of MINUS ! and the output is million of rows. So, i got confused ! Please any comment or help.

1) SEL  COUNT(Col1)  

FROM table B RIGHT OUTER JOIN table S

ON S.colX = B.colX

WHERE B.colX IS NULL

AND S.ColDate1 between   '2014-11-02 00:00:01' and  '2014-11-09 23:59:59' 

AND B.ColDate2 between   '2014-11-02 00:00:01' and  '2014-11-09 23:59:59' 

2) SEL COUNT(*) FROM 

( SEL ColX FROM  Table S where ColDate1 between   between   '2014-11-02 00:00:01' and  '2014-11-09 23:59:59'

minus 

select ColX from Table B  where  ColDate2 between   '2014-11-02 00:00:01' and  '2014-11-09 23:59:59')  as Result

2 REPLIES
Senior Apprentice

Re: OUTER JOIN WHERE IS NULL vs MINUS vs NOT EXISTS

MINUS is DISTINCT, so it might return a lower number when ColX is not unique in both sets.

Enthusiast

Re: OUTER JOIN WHERE IS NULL vs MINUS vs NOT EXISTS

One sentence says everything, @dnoeth Thanks a ton! You're the best.