Issue with 'NOT IN' statement in SQL

Question

Can anyone please point out what is wrong with the following SQL statement:

SELECT DiaryType 
FROM tblDiaryTypes 
WHERE DiaryType NOT IN (SELECT NextDiary 
                        FROM tblActionLinks 
                        WHERE HistoryType = 'Info Chased');

Now the nested SELECT statement currently returns NULL because there are initially no entries in tblActionLinks, and I am wondering if that is the issue.

The outer SELECT statement if executed on its own does return all the Diary Types from tblDiaryTypes as expected. But when I add the nested SELECT statement to exclusde certain values, then the overall SQL statement returns empty!

Does this have something to do withthe fact that tblActionLinks is currently empty? If so, how can I amend my SQL statement to handle that possibility.

See http://stackoverflow.com/questions/129077/sql-not-in-constraint-and-null-values - when ansi_nulls is on this will return no results. Try turning ansi_nulls off, and you should get results. — Hecksa, Jan 31 '12 at 10:14

score 1 · Accepted Answer · answered Jan 31 '12 at 10:13

1

For SQL SERVER (you didn't specified sql engine) try with:

SELECT ISNULL(NextDiary, 0)  ...

When no rows found all value is null then it will return 0

answered Jan 31 '12 at 10:13

cichy

10,464
4
26
36

score 1 · Answer 2 · answered Jan 31 '12 at 10:16

1

Are you sure there are no entries currently in tblActionLinks? If there are no entries in tblActionLinks, then outer query should return all records

answered Jan 31 '12 at 10:16

naresh

2,113
20
32

gdoron · Answer 3 · 2012-01-31T10:27:21.363

0

Does this have something to do withthe fact that tblActionLinks is currently empty?

Yes... NULL doesn't being handled so good in SQL, Comparing a value to NULL is undifned try give for null a flag value like -999:

SELECT DiaryType 
FROM tblDiaryTypes 
WHERE DiaryType NOT IN (SELECT NVL(NextDiary, -999) -- <===
                        FROM tblActionLinks 
                        WHERE HistoryType = 'Info Chased');

NVL(NextDiary, -999) means that if NextDiary IS NULL, replace the value with -999

docs

edited Jan 31 '12 at 10:27

answered Jan 31 '12 at 10:15

gdoron

147,333
58
291
367

What do you mean by saying that NULL isn't handled so good in SQL? For instance, the (documented) fact that `NULL = NULL` doesn't evaluate to TRUE, as some people might expect doesn't indicate that it is hanled not so good. – Igor Korkhov Jan 31 '12 at 10:23
@IgorKorkhov. Well I think that `NULL = NULL` is true. if you could choose the NULL behaviour you would choose it to be equal... just like in `C#, java` etc' – gdoron Jan 31 '12 at 10:26
NULL in SQL (as well as in relational algebra) means unknown, and you can't compare unknowns, hence the behaviour. – Igor Korkhov Jan 31 '12 at 10:32

score 0 · Answer 4 · answered Jan 31 '12 at 10:40

I would rewrite your query the following way:

SELECT DiaryType 
FROM tblDiaryTypes 
WHERE NOT EXISTS (SELECT NextDiary 
                         FROM tblActionLinks 
                         WHERE HistoryType = 'Info Chased'
                           AND NextDiary = DiaryType)

This ensures proper behaviour irrespective of ANSI_NULLS setting and you don't have to worry about properly choosing the magic value returned by ISNULL(NextDiary, 0) (what if you have DiaryType equal to 0 in tblDiaryTypes?)

Issue with 'NOT IN' statement in SQL

4 Answers4