Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
learning_grsql
Posting Yak Master
230 Posts |
 Posted - 2013-10-10 : 06:54:05
|
I was trying to find column3 from table1 where the values of column3 did not exist in column5 of table2.This is the query first I tried but i got blank result. (0 rows affectd)Select column3 from table1Where column3 not in (select column5 from table2 where date between ‘20130801’ and ‘20130805’) Then I changed it to as below and it worked fine. Select column3 from table1Where column3 not in (select column5 from table2 where column5 is not null and date between ‘20130801’ and ‘20130805’) I wonder what was the problem and what really "is not null" did in it to sort out the problem. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-10-10 : 08:10:32
|
This is because there are null values in column5 of table2. When you have a NULL on either or both sides of a comparison operator, the result is unknown. One way to think of your query is as follows. Suppose you 3 rows in table2 with column5 being 10, 11 and NULL. Then, your query is equivalent to:SELECT column3 FROM Table1WHERE column3 <> 10 AND column3 <> 11 AND column3 <> NULL The comparison column3 <> NULL results in UNKNOWN, which means the WHERE clause is not true. And so you get nothing back from the query. |
 |
|
|
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2013-10-10 : 14:33:46
|
| Many Thanks James K. It is clear now. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-10-10 : 16:32:55
|
| You are very welcome - glad to help. |
|
|
|
|
|
|
is not null