NOT in

Database
Enthusiast

NOT in

I have created two tables
CREATE TABLE Table_a
(Col_a Integer)
PRIMARY INDEX ( Col_a );

CREATE TABLE Table_b
(Col_b Integer)
PRIMARY INDEX ( Col_b );
Follwoing rows were inserted
Insert into Table_a values (1);
Insert into Table_a values (2);
Insert into Table_a values (Null);

Insert into Table_b values (1);
Than i am running
select * from Table_a where col_a not in (select col_b from Table_b);
Produces one row with a value of 2.

select * from Table_a where not exists (select col_b from Table_b where col_a = col_b);
Produces two rows with values of 2 & Null.

Than i am inserting
Insert into Table_b values (Null); so now Table-b contains the values 1 & Null
and running
select * from Table_a where col_a not in (select col_b from Table_b); Produces no rows.

select * from Table_a where not exists (select col_b from Table_b where col_a = col_b);
Produces two rows with values of 2 & Null

1)As far as i know both not exists and not in uses exclusion join ,can anyone explain how it is different in two cases with the help of some example
2)I have heard that it works with a 3 way logic....but canbody explain how this 3 way logic result in elimination of all rows even when 1 row was expected in case of NOT IN (i.e 2 in our example)

Please clarify as all the were i have found out that
Because of the NOT IN subquery, the Optimizer
will produce the two join plan options side by
side.
• The system will scan the left relation for NULLS
and the right relation for NULLS.
• On the basis of the presence or absence of
NULLS, the system will choose from either plan..
But nowhere it is explained how it is done internally.....
Please clarify....
6 REPLIES
Enthusiast

Re: NOT in

Hi All,
Basically i want to know how 3 way logic works in case of NOT IN and NOT exists on cols with null values....With some example...

1 column has values (1,2,3) other has (2,3,?)
When we are doing NOT IN on these we are expecting a result with row 1.......
but because of 3 way logic on null values......result is empty....
Just wanted to know how it works internally...
As
Suppose after redistribution and all
AMP1----1...
AMP2---2,2
AMP3---3,3,?(AS NULLS ARE IN LAST AMP ALWAYS(not sure)
Now a exclusion merge join will work......just wanted to know how it will eliminate row in amp 1 WHICH IS OUR DESIRED RESULT
Hope somebody in this forum will reply soon
Teradata Employee

Re: NOT in

This is not really a Teradata question. The SQL standard semantics for NOT IN require this behavior.
Junior Contributor

Re: NOT in

This behaviour is one of the reasons why NULLs are evil.
Never ever use NOT IN on NULLable columns, the result set might be empty and even if it's correct, there's a lot of work for the database.
Always replace it with NOT EXISTS.
As Fred already pointed out, this is the same for all DBMSes.

1 in (1,2,null) -> 1=1 or 1=2 or 1=null -> true or false or unknown -> true

1 not in (1,2,null) -> 1<>1 and 1<>2 and 1<>null -> true and false and unknown -> unknown

Dieter
Enthusiast

Re: NOT in

Hi Dnoeth/Fred,

My understanding of above is as follows:
NOT IN will take unknown comparisions and make all unknown
it is a set subtraction operator in case of NOT IN
(1,2,3)-(1,2,?)
we cannot say that 3 is equal to null ( may be or may be not)
i) if 3=? than result is no rows
ii)if 3!= null 3 will be output
so result is unknown
so answer set will return nothing.....

In explanation given by you.....

1 not in (1,2,null) -> 1<>1 and 1<>2 and 1<>null -> true and false and unknown -> unknown

it means that NOT IN====AND operation
IN=======OR operation...

i.e
But can u explain how will following operate
(1,2,3) not in (1,2) ->

Also while in case of NOT EXISTS

(1,2,3)-(1,2,?)
NOT exists will search for matching row for 1
2
3
when it will come to 3...it will see unknown comaprisions......but here it is neglecting those resulting in 3 to be present in answer set.......

Please correct me if i am wrong......
Junior Contributor

Re: NOT in

Any comparison to NULL results in UNKNOWN (the only way to check for NULLs is IS [NOT] NULL).

A "set subtraction operator" like EXCEPT would return (3) for (1,2,3) EXCEPT (1,2,?), but NOT IN follows different rules.

NOT EXISTS again follows different rules, instead of three-way logic it applies a two-way logic where UNKNOWN is treated as FALSE.

If you check explain you'll see the phrase "where unknown comparison will be ignored" indicating that.

Dieter
Enthusiast

Re: NOT in

Dieter,

Thanks a lot for your explanations and the amount of Knowledge you are transferring via this forum.

Prakar,

Just to add to this discussion...
Hope,we can use coalesce function to overcome this scenario as below

select * from Table_a where coalesce(col_a,'x') not in (select coalesce (col_b,'x') from Table_b);

Cheers:-)