Not in VS Not Exists - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Not in VS Not Exists

Author

Topic

1fred
Posting Yak Master

158 Posts

Posted - 2003-01-28 : 11:20:17

I have a table that contains 261 rows. If I run this query :

select * from lSentT
where sent_key in (select sent_key from savisitt )
order by sent_key
It returns 69 rows, which is the right number.

Then I run this query that should returns me 192 rows.
select * from lSentT
where sent_key not in (select sent_key from savisitt )
order by sent_key
But it returns 0 rows

If I run this query, to me it is the same, but with this one I have my 192 rows... why?
select * from lSentT a
where where not exits (select sent_key from savisitt b where a.sent_key = b.sent_key )
order by sent_key

Why the query with the IN is working while the one with the NOT IN fails to return any results and the NOT EXISTS returns exactly what I need. This is the only table in my database that is doing that abnormal behavior.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-28 : 11:36:02

What you get when you do this:

select Count(Distinct(sent_key))
from savisitt

1fred
Posting Yak Master

158 Posts

Posted - 2003-01-28 : 11:46:13

I get 69

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-01-28 : 11:51:10

Probably a NULL in savisitt.sent_key
Like this:


create table #a (a int)
create table #b (b int)

insert into #a values (1)
insert into #a values (2)
insert into #a values (3)
insert into #a values (4)
insert into #a values (NULL)
insert into #a values (5)

insert into #b values (1)
insert into #b values (1)
insert into #b values (NULL)
insert into #b values (4)
insert into #b values (5)

go

select * from #a
where a in (select b from #b)

select * from #a
where a not in (select b from #b)

select * from #a
where not exists (select * from #b where a = b)

go

drop table #a
drop table #b

Edited by - Arnold Fribble on 01/28/2003 11:51:40

1fred
Posting Yak Master

158 Posts

Posted - 2003-01-28 : 12:01:31

You're right, thanks a lot! any logic reason why a null cause problems to that kind of query?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-01-28 : 12:24:54

NULL's are SPECIAL.....so special that NULL is NOT equal to NULL, in fact NULL is not equal to anything, because NULL means absence of any value, rather than the presence of a 'special value'

and thus "fielda IN (NULL)" won't work...and neither will "fielda NOT IN (NULL)"

there are more detailed reasonings around here in other recent topics.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-01-28 : 12:29:15

It's the way NOT IN is defined to work.
Essentially, it's because all the <> comparisons are AND'ed together, so the UNKNOWN value returned by the comparisons with NULL will cause the result of the AND'ed expression to be UNKNOWN, and NOT IN only includes the rows that yield TRUE.

malamohan
Starting Member

34 Posts

Posted - 2003-01-29 : 09:16:10

Hey this also works.
select * from #a
where a not in (select b from #b WHERE A=B)

mala

1fred
Posting Yak Master

158 Posts

Posted - 2003-01-29 : 09:49:55

Yeah thanks, since the null is my problem, I'll just remove them from my second query.

select * from LSentT where sent_key not in(select sent_key from savisitT where sent_key is not null)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources