Archives of the TeradataForum
Message Posted: Wed, 16 Feb 2005 @ 18:25:50 GMT
Fred Pluebell wrote:
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...
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|