SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 is not null
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

learning_grsql
Posting Yak Master

230 Posts

Posted - 10/10/2013 :  06:54:05  Show Profile  Reply with Quote
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 table1
Where 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 table1
Where 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
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 10/10/2013 :  08:10:32  Show Profile  Reply with Quote
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 Table1
WHERE 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.
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 10/10/2013 :  14:33:46  Show Profile  Reply with Quote
Many Thanks James K. It is clear now.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 10/10/2013 :  16:32:55  Show Profile  Reply with Quote
You are very welcome - glad to help.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000