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>>

Attachments

Library

Quick Reference