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
 Database Design and Application Architecture
 odd query result
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

johnd2323
Starting Member

USA
1 Posts

Posted - 11/21/2013 :  13:46:03  Show Profile  Reply with Quote
hi,
i'm a veteran sql developer yet i'm finding a seemingly simple query brutally difficult. I have two tables, both of which have fieldx in it, both as varchar(30). Table1 has a record with a fieldx value of 'smith'. Table2 has no records with a fieldx value of 'smith'. however, when I run this query:
select * from table1 where fieldx not in ( select fieldx from table2 )

I get an empty result set! the only thing I can think is that there's something like a global setting in sql i'm unaware of that is set to a value that could account for this.
any idea why I wouldn't get the expected result set with a record in it?
thank you kindly.
- john

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 11/21/2013 :  13:59:40  Show Profile  Reply with Quote
This works for me:

;with table1 (fieldx) as (select 'smith')
,table2 (fieldx) as (select 'jones')
select * 
from table1 
where fieldx not in ( select fieldx from table2 )

OUTPUT:
fieldx
------
smith

No global setting. I assume you've confirmed 'smith' is not in table2?
select * from table2 where fieldx = 'smith'

Is the collation case insesitive? does ether table's smith value contain leading or trailing whitespace?



Be One with the Optimizer
TG
Go to Top of Page

mhorseman
Starting Member

United Kingdom
25 Posts

Posted - 11/22/2013 :  03:40:29  Show Profile  Reply with Quote
Have you got any NULLs in Table2?

Amending TG's query a little to have a null, I get no output.

;with table1 (fieldx) as (select 'smith')
,table2 (fieldx) as (select 'jones' union select null)
select *
from table1
where fieldx not in ( select fieldx from table2 )


Mark
Go to Top of Page

mhorseman
Starting Member

United Kingdom
25 Posts

Posted - 11/22/2013 :  03:40:57  Show Profile  Reply with Quote
Have you got any NULLs in Table2?

Amending TG's query a little to have a null, I get no output.

;with table1 (fieldx) as (select 'smith')
,table2 (fieldx) as (select 'jones' union select null)
select *
from table1
where fieldx not in ( select fieldx from table2 )


Mark
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/22/2013 :  07:13:47  Show Profile  Reply with Quote
quote:
Originally posted by johnd2323

hi,
i'm a veteran sql developer yet i'm finding a seemingly simple query brutally difficult. I have two tables, both of which have fieldx in it, both as varchar(30). Table1 has a record with a fieldx value of 'smith'. Table2 has no records with a fieldx value of 'smith'. however, when I run this query:
select * from table1 where fieldx not in ( select fieldx from table2 )

I get an empty result set! the only thing I can think is that there's something like a global setting in sql i'm unaware of that is set to a value that could account for this.
any idea why I wouldn't get the expected result set with a record in it?
thank you kindly.
- john


is your collation case sensitive?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 11/22/2013 :  10:15:16  Show Profile  Reply with Quote
If it is because of the NULLs then change your query to this:

select t1.*
from table1 t1
left outer join table2 t2 on t2.fieldx = t1.fieldx
where t2.fieldx is null


Be One with the Optimizer
TG
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.09 seconds. Powered By: Snitz Forums 2000