Odd select behavior
-
This is odd. Maybe someone can shed some light. I am trying to see all the emails from t_Quote table that aren't in t_Contact table.
When I select email from t_Quote where email = 'abc@abc.com' I get a row returned.
When I select email from t_Contact where email = 'abc@abc.com' I get no rows returned.
So this paticular email is in one table and not in the other, which is the way it should be.
However, when I run this:
select email from t_Quote where email not in (select email from t_Contact)
I get nothing.
So I ran this to make sure there are some emails that are in t_Quote that aren't in t_Contact.
select a.email,b.email from t_Quote a FULL JOIN t_Contact b on a.email = b.email
There are numerous emails that are in both tables and numerous emails that are only in t_Quote.
This is a basic statement and for the life of me I don't know why this isn't grabbing the correct data. Both datatypes are the same type and length. Has anyone seen this before?
-
if the email column in tContact is a nullable column and those entries are null, this is the expected result.
Try this:
select q.email, c.email
from t_Quote q
LEFT OUTER JOIN t_contact c
on q.email = c.email
where
c.email IS NULL
See this article:
http://www.sqlservercentral.com/columnists/mcoles/fourrulesfornulls.asp
for an excellent discussion on NULL behavior.
-
Pam beat me to it, but here's something to run to see the issue. As Pam says, this is entirely normal behaviour. You just need to be aware of nulls!
declare @t_Quote table (email varchar(20))
insert @t_Quote
select 'abc@abc.com'
union all select 'xyz@abc.com'
declare @t_Contact table (email varchar(20))
insert @t_Contact
select 'xyz@abc.com'
select email from @t_Quote where email not in (select email from @t_Contact) --works!
insert @t_Contact
select null
select email from @t_Quote where email not in (select email from @t_Contact) --doesn't work!
select email from @t_Quote where email not in (select email from @t_Contact where email is not null) --works!
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
I thought it might be a NULL issue but didn't explore it. Will do now. Thanks for the replies.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply