Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 02 Sep 2003 @ 20:22:50 GMT


     
  <Prev Next>  
<<First
<Prev
Next> Last>>  


Subj:   MINUS ALL -vs- NOT EXISTS -vs- NOT IN
 
From:   Anomy Anom

<-- Anonymously Posted: Tuesday, September 02, 2003 16:06 -->

What may be causing MINUS ALL to return more rows that NO EXISTS and NOT IN?

select svc_ordno from provgact
where svc_ordno is not null minus all
(select svc_ordno from svcoprovg);
RETURNS 1603447 rows

*I cannot select prov_act_id because the selects must have the same number of columns.

select svc_ordno, prov_act_id from provgact p
where svc_ordno is not null and
not exists
(select svc_ordno from svcoprovg s where p.svc_ordno=s.svc_ordno);
RETURNS 1569862 rows

select svc_ordno, prov_act_id from provgact
where svc_ordno is not null and
svc_ordno not in
(select svc_ordno from svcoprovg);
RETURNS 1569862 rows

provgact where svc_ordno is not null = 1737890 rows
svcoprovg = 134443 rows
1737890 - 134443 = 1603447
There I expect 1603447 rows to be returned.

Thanks.



     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016