Archives of the TeradataForum

Message Posted: Wed, 16 Feb 2005 @ 18:25:50 GMT


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

Subj:		Re: Null values in a select

From:		Dieter Noeth

Fred Pluebell wrote:

The behavior you describe follows ANSI SQL rules as to how NULL values are to be handled for NOT IN (subquery). An alternative is to use NOT EXISTS. Check the Teradata SQL Reference (V5 - Functions & Operators) for more detail.

And if you carefully read "Behavior of Nulls for NOT IN" you'll see that the behaviour for multi-column subqueries is definitely not Standard SQL:

     create table test1(a int, b int);
     ins test1(1,1);
     ins test1(2,2);
     ins test1(3,3);

     create table test2(a int, b int);
     ins test2(1,1);
     ins test2(2,null);
     ins test2(2,6);
     ins test2(3,1);

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

       *** Query completed. One row found. 2 columns returned.
       *** Total elapsed time was 1 second.

                a            b
     -----------  -----------
                3            3


     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 never checked if multi column subqueries are Standard SQL, but the order of columns may never influence the result set. But it's not a bug, it's a documented (but well hidden) feature.

So it's just another reason why you never ever should use NOT with NULLs, even if you think you really know 3-value logic.

Btw, it wasn't me who found out, someone sent me that example some months ago...

Dieter


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

Attachments

Library

Quick Reference