Different results - Not Exists Vs Not In

The following example results to differnet results depending upon whether I'
m
using Not exists or Not in. I'm sdoing the same thing in 2 different ways.
Is there an explanation for this or is this a bug in SQL Server?
Current Version SQL 2000 SP3a
Example:
Declare @.temp1
table (
id int
)
Insert into @.temp1 (id) values (1)
Insert into @.temp1 (id) values (2)
Insert into @.temp1 (id) values (3)
Insert into @.temp1 (id) values (4)
-- Expected anawer 101,102,103,104
-- returns correct
Select
*
From
@.temp1 t1x
Where (100+t1x.id) NOT IN ( Select
t1y.id
from
@.temp1 t1y )
-- returns "NOTHING"
Select
*
From
@.temp1 t1x
Where
NOT EXISTS (Select
t1y.id
from
@.temp1 t1y
Where
t1y.id != (100+t1x.id) )Change the comparison expression from second query.

> Where
> t1y.id != (100+t1x.id) )
Where
t1y.id = (100+t1x.id) )
AMB
"Core" wrote:

> The following example results to differnet results depending upon whether
I'm
> using Not exists or Not in. I'm sdoing the same thing in 2 different ways.
> Is there an explanation for this or is this a bug in SQL Server?
> Current Version SQL 2000 SP3a
> Example:
> Declare @.temp1
> table (
> id int
> )
> Insert into @.temp1 (id) values (1)
> Insert into @.temp1 (id) values (2)
> Insert into @.temp1 (id) values (3)
> Insert into @.temp1 (id) values (4)
> -- Expected anawer 101,102,103,104
> -- returns correct
> Select
> *
> From
> @.temp1 t1x
> Where (100+t1x.id) NOT IN ( Select
> t1y.id
> from
> @.temp1 t1y )
> -- returns "NOTHING"
> Select
> *
> From
> @.temp1 t1x
> Where
> NOT EXISTS (Select
> t1y.id
> from
> @.temp1 t1y
> Where
> t1y.id != (100+t1x.id) )
>|||I think you intended your second example to be:
Select
*
From
@.temp1 t1x
Where
NOT EXISTS (Select
t1y.id
from
@.temp1 t1y
Where
t1y.id = (100+t1x.id) )
Result:
id
1
2
3
4
(4 row(s) affected)
However, the two queries are still not logically equivalent. Insert a
NULL in the table and you'll see what I mean.
David Portas
SQL Server MVP
--|||Thanks Alejandro,
I feel stupid. I had starred at this problem for 30 minutes.
"Alejandro Mesa" wrote:
> Change the comparison expression from second query.
>
> Where
> t1y.id = (100+t1x.id) )
>
> AMB
> "Core" wrote:
>|||I have been there too.
AMB
"Core" wrote:
> Thanks Alejandro,
> I feel stupid. I had starred at this problem for 30 minutes.
>
> "Alejandro Mesa" wrote:
>