CODEWITHSUNDEEP
sqlteradata
Shaz
Shaz

Reputation: 13

NOT IN Select statement returning 0 values

I have data with variables ID and Code. Sample data shown below:

ID   Code
abc  00
def  00
ghi  00
def  23
jkl  00
mno  20 
pqr  24

ID can have multiple values of different codes. I am trying to work out all possible combinations of code per ID (there are 15 in total)

My code:

select id, code from data.test 
where id in (select id from data.test where code = 00 )
and id in ('abc','def','ghi','jkl','mno','pqr')

works fine (returning 4 rows) however this code:

select id, code from data.test 
where id not in (select id from data.test where code = 23)
and id in ('abc','def','ghi','jkl','mno','pqr')

returns 0 rows? It should return 3 rows.

I am using Teradata SQL Assistant version 15.00.0.02.

Any help would be appreciated.

Upvotes: 1

Views: 238

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270733

One reason why not in with a subquery fails is because one of the values (id in your case) is NULL. In this case, the NOT IN expression never returns true. One way to fix this:

select id, code
from data.test 
where id not in (select id from data.test where code = 23 and id is not null) and
      id in ('abc', 'def', 'ghi', 'jkl', 'mno', 'pqr')

Another way is to use NOT EXISTS:

select t.id, t.code
from data.test t
where not exists (select 1 from data.test t2 where t.code = 23 and t2.id = t.id) and
      t.id in ('abc', 'def', 'ghi', 'jkl', 'mno', 'pqr')

Upvotes: 3

Related Questions