All Forums General
Shoushounette 9 posts Joined 01/14
08 Dec 2014
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

 

 

dnoeth 4628 posts Joined 11/04
09 Dec 2014

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

Dieter

Shoushounette 9 posts Joined 01/14
09 Dec 2014

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

You must sign in to leave a comment.