NOT IN and nested query

Database

NOT IN and nested query

Hi Developers,

I have consistently used NOT IN on hard-coded static list of values in Teradat SQL Assistant and it has worked without any issues.

Sel distinct col1

from table1

where col1 NOT IN ('ABC', 'XYZ');

However recently I had to modify the query to replace the hard coded list of values with another nested query to deal with subset.

Sel distinct col1

from table1

where col1 NOT IN (Sel distinct col11 from table11);

This syntax does not work and returns null result set! It is expected to return the difference between the two tables.

Its equivalent does return valid data set:

Sel distinct col1 from table1

minus

Sel distinct col11 from table11;

Both Col1 and Col11 is of varchar data type.

So I would like to understand if Teradata SQL Assist does not allow the syntax listed first. Is it dependent on version of SQL or tool being used?

Thanks,

Dhanashree ARole

4 REPLIES
Fan

Re: NOT IN and nested query

Your subquery is returning NULL values, causing the entire query to fail.(i.e COL11 in table11 has NULL values)

The below query should return what you are expecting.

Sel distinct col1

from table1

where col1 NOT IN (Sel distinct col11 from table11 where col11 is not null);

The reason why above NOT IN subquery is not working:

* col11 NOT IN ('y','n',NULL) is same as  ( col11='y' or col11='n'  or col11=NULL)

* Is the same as : true AND true AND Unknown (Which is false)

Thanks

Sagar

Junior Supporter

Re: NOT IN and nested query

Hi.

If you get NULLs from table11.col11 the NOT IN will not work.

You can try NOT EXISTS instead or filter the NULLs in the subquery.

BTW: the DISTINCT is irrelevant for the subquery.

HTH.

Cheers.

Carlos.

Enthusiast

Re: NOT IN and nested query

Try this:

Sel distinct col1
from table1
where col1 NOT IN
(Sel col11 from table11 where col11 is not null group by 1);
Khurram

Re: NOT IN and nested query

Thanks that clears the air!