CODEWITHSUNDEEP
sql
PJW
PJW

Reputation: 5397

Issue with 'NOT IN' statement in SQL

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.

Upvotes: 1

Views: 207

Answers (4)

Igor Korkhov
Igor Korkhov

Reputation: 8558

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?)

Upvotes: 0

gdoron
gdoron

Reputation: 150253

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

Upvotes: 0

naresh
naresh

Reputation: 2113

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

Upvotes: 1

cichy
cichy

Reputation: 10634

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

Upvotes: 1

Related Questions