Home Page for the TeradataForum
 
https:

Archives of the TeradataForum

Message Posted: Thu, 17 Feb 2005 @ 07:56:31 GMT


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


Subj:   Re: Null values in a select
 
From:   Dieter Noeth

Glenn McCall wrote:

  I'm not sure that the statement "...the order of columns may never influence the result set", means that you can mix up the order of the columns in the following query in anyway you like (i.e. ... (a,b) ... select b, a ... !  


Of course you're right, that's why i sent a second mail correcting that error...

No matter, if you use "(a,b) in (a,b)" or "(b,a) in (b,a)", the result always *must* be the same.

It's like "t1 join t2 on t1.a = t2.a and t1.b = t2.b"
vs. "t1 join t2 on t1.b = t2.b and t1.a = t2.a"


     select * from test1
     where (a,b) not in
     (select b,a from test2);

       *** Query completed. No rows found.
       *** Total elapsed time was 1 second.
  I would expect that if you supplied the columns the "right way around" you would get a different result! As in  


     select * from test1
     where (b,a) not in
     (select b,a from test2);
  **Unfortunately** my expectations would not be met. :-(  



I was surprised, too ;-)

I especially like that nice explanation:

"if a null is in a field other than the first field of expression_list_2 then some rows may be returned"

(SQL Ref. Functions and Operators, Chapter 10: Logical Predicates, IN/NOT IN, Behavior of Nulls for NOT IN)


Dieter



     
  <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: 28 Jun 2020