All Forums Database
AROLD001 3 posts Joined 12/15
13 Jul 2016
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
 

sagar_ 3 posts Joined 05/14
13 Jul 2016

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

CarlosAL 512 posts Joined 04/08
14 Jul 2016

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.

M.Saeed Khurram 544 posts Joined 09/12
14 Jul 2016

Try this:

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

 

Khurram

AROLD001 3 posts Joined 12/15
14 Jul 2016

Thanks that clears the air!

You must sign in to leave a comment.