November 9, 2008 at 5:30 pm
Hi,
I am having problems with my queries which I definitely think is because of null values. This is what I want to do:
Find out a list of phone numbers that are in one table but not in the other:
I wrote something like this:
select phonenumber
from ttemp
where phonenumber
not in
(
select ('7'+phonenumber)
from tpphone
where phonecountrycode = '64'
and phoneareacode = '07'
)
But it didnt return any records.
Now when I try the same as:
select phonenumber
from ttemp
where phonenumber
in
(
select ('7'+phonenumber)
from tpphone
where phonecountrycode = '64'
and phoneareacode = '07'
)
It returned some records.
The phonenumber column are permitted to have null values.
What could be the possible explanation for this behaviour 🙁
November 10, 2008 at 3:40 am
Hello
Try using proper join syntax, it will help to understand what's going on and will also give you a better insight into your data:
-- this will return all rows from t and matching rows from p1
SELECT t.phonenumber, p1.phonenumber
FROM ttemp t
LEFT JOIN tpphone p1
ON '7'+p1.phonenumber = t.phonenumber
AND p1.phonecountrycode = '64'
AND p1.phoneareacode = '07'
--WHERE p1.phonenumber IS NOT NULL -- match: equivalent to full join
--WHERE p1.phonenumber IS NULL -- no match
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 10, 2008 at 5:03 pm
You are correct, it is the existence of a null from your subquery that is causing your query to return no rows. It is easy enough to fix by adding a where clause to your subquery as shown in the example at the bottom. You could also test by using a left join (also shown in the example).
The NULL causes you a problem with NOT IN because NOT IN essentially does a series of equality tests. Don't think of NULL as a value, even an empty one like ''. Think of it as a state of the column or variable. Null is the absence of a value.
Testing if something is equal to NULL is not the same thing as testing whether something is NULL. When you test to see if something is equal to (or not equal to) NULL you never get a "true" condition as the following illustrates.
declare @phone varchar(10)
set @phone = null
select@phone
,case when @phone = '1234567890' then 'True' else '' end as 'Equals 890'
,case when @phone != '1234567890' then 'True' else '' end as 'Not Equal 890'
,case when @phone = '1234567891' then 'True' else '' end as 'Equals 891'
,case when @phone = null then 'True' else '' end as 'Equals Null'
,case when @phone != null then 'True' else '' end as 'Does Not Equal Null'
,case when @phone IS null then 'True' else '' end as 'Is Null'
Hope the explanation helped.
Bob
---------------------------------------------------------------------------------------------------------------
-- Examples
---------------------------------------------------------------------------------------------------------------
declare @sample1-2 table (phone varchar(10))
declare @sample2 table (phone varchar(10))
insert into @sample1-2
select nullunion all
select 1234567890union all
select 1234567891union all
select 1234567892
insert into @sample2
select 1234567890union all
select 1234567891union all
select 1234567893
-- your use of subquery
select * from @sample2
where phone not in (select * from @sample1-2)
-- subquery corrected
select * from @sample2
where phone not in (select * from @sample1-2 where phone is not null)
-- use of left join
select s2.*
from @sample2 s2
left join @sample1-2 s1 on s1.phone = s2.phone
where s1.phone is null
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply