NOT IN / NOT EXISTS

8 Sep 2005 10:11 AM

marcmc

This is driving me crazy. The following two statements give a count of 1 and
0 respectively. The third statement gives me a 0 count when using NOT IN as
the subquery and 1 when using IN subquery. Shouldn't it be the other way
round?

select count(*) from Table1 where Column1 = '1234567'    -- 1 Row
select count(*) from Table2 where Column2 = '1234567'    -- 0 Row

SELECT     count(*)
FROM     Table1(nolock)
WHERE    Column1 = '1234567'
AND          Column1 NOT IN (SELECT column2 FROM Table2(nolock)) -- 0 Rows

SELECT     count(*)
FROM     Table1(nolock)
WHERE    Column1 = '1234567'
AND          Column1 IN (SELECT column2 FROM Table2(nolock)) -- 1 Rows

8 Sep 2005 10:24 AM

marcmc

It seems it's because Column1 & Column1 are slightly different names but
carry the same data. How else can this be done?

Show quote

"marcmc" wrote:

> This is driving me crazy. The following two statements give a count of 1 and
> 0 respectively. The third statement gives me a 0 count when using NOT IN as
> the subquery and 1 when using IN subquery. Shouldn't it be the other way
> round?
>
> select count(*) from Table1 where Column1 = '1234567'    -- 1 Row
> select count(*) from Table2 where Column2 = '1234567'    -- 0 Row
>
> SELECT     count(*)
> FROM     Table1(nolock)
> WHERE    Column1 = '1234567'
> AND          Column1 NOT IN (SELECT column2 FROM Table2(nolock)) -- 0 Rows
>
> SELECT     count(*)
> FROM     Table1(nolock)
> WHERE    Column1 = '1234567'
> AND          Column1 IN (SELECT column2 FROM Table2(nolock)) -- 1 Rows
>
>
>

8 Sep 2005 10:28 AM

marcmc

It seems it's because Column1 & Column2 are different data types. How else
can this be done?

Show quote

"marcmc" wrote:

> It seems it's because Column1 & Column1 are slightly different names but
> carry the same data. How else can this be done?
>
> "marcmc" wrote:
>
> > This is driving me crazy. The following two statements give a count of 1 and
> > 0 respectively. The third statement gives me a 0 count when using NOT IN as
> > the subquery and 1 when using IN subquery. Shouldn't it be the other way
> > round?
> >
> > select count(*) from Table1 where Column1 = '1234567'    -- 1 Row
> > select count(*) from Table2 where Column2 = '1234567'    -- 0 Row
> >
> > SELECT     count(*)
> > FROM     Table1(nolock)
> > WHERE    Column1 = '1234567'
> > AND          Column1 NOT IN (SELECT column2 FROM Table2(nolock)) -- 0 Rows
> >
> > SELECT     count(*)
> > FROM     Table1(nolock)
> > WHERE    Column1 = '1234567'
> > AND          Column1 IN (SELECT column2 FROM Table2(nolock)) -- 1 Rows
> >
> >
> >

8 Sep 2005 10:40 AM

marcmc

Yet this will work!!!!

create table marc(name varchar(15))
create table marc1(name1 char(19))
insert into marc values('marc')

select * from marc where name = 'marc'
select * from marc1 where name1 = 'marc'

SELECT     *
FROM     marc
WHERE     name = 'Marc'
AND    name NOT IN (SELECT name1 FROM marc1)

8 Sep 2005 11:03 AM

Tom Moreau

You may have a NULL in Column2. That's a known trap. Use NOT EXISTS:

SELECT count(*)
FROM Table1(nolock)
WHERE Column1 = '1234567'
AND NOT EXISTS
(SELECT * FROM Table2(nolock)
WHERE Table2.Column2 = Table1.Column1)

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..

"marcmc" <mar***@discussions.microsoft.com> wrote in message
news:C44E559B-AC80-4B2D-B419-279DE951B7B1@microsoft.com...

This is driving me crazy. The following two statements give a count of 1 and
0 respectively. The third statement gives me a 0 count when using NOT IN as
the subquery and 1 when using IN subquery. Shouldn't it be the other way
round?

select count(*) from Table1 where Column1 = '1234567'    -- 1 Row
select count(*) from Table2 where Column2 = '1234567'    -- 0 Row

SELECT count(*)
FROM Table1(nolock)
WHERE Column1 = '1234567'
AND          Column1 NOT IN (SELECT column2 FROM Table2(nolock)) -- 0 Rows

SELECT count(*)
FROM Table1(nolock)
WHERE Column1 = '1234567'
AND          Column1 IN (SELECT column2 FROM Table2(nolock)) -- 1 Rows

8 Sep 2005 11:11 AM

Uri Dimant

Or
SELECT *
FROM Table1
WHERE Column1 = '1234567'
AND Column1 NOT IN (SELECT column2 FROM Table2 WHERE column2 IS NOT NULL)

Show quote

"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:e9hmtTGtFHA.3908@tk2msftngp13.phx.gbl...
> You may have a NULL in Column2. That's a known trap. Use NOT EXISTS:
>
> SELECT count(*)
> FROM Table1(nolock)
> WHERE Column1 = '1234567'
> AND NOT EXISTS
>          (SELECT * FROM Table2(nolock)
>            WHERE Table2.Column2 = Table1.Column1)
>
>
> --
>   Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON   Canada
> www.pinpub.com
> .
> "marcmc" <mar***@discussions.microsoft.com> wrote in message
> news:C44E559B-AC80-4B2D-B419-279DE951B7B1@microsoft.com...
> This is driving me crazy. The following two statements give a count of 1
> and
> 0 respectively. The third statement gives me a 0 count when using NOT IN
> as
> the subquery and 1 when using IN subquery. Shouldn't it be the other way
> round?
>
> select count(*) from Table1 where Column1 = '1234567'    -- 1 Row
> select count(*) from Table2 where Column2 = '1234567'    -- 0 Row
>
> SELECT count(*)
> FROM Table1(nolock)
> WHERE Column1 = '1234567'
> AND          Column1 NOT IN (SELECT column2 FROM Table2(nolock)) -- 0
> Rows
>
> SELECT count(*)
> FROM Table1(nolock)
> WHERE Column1 = '1234567'
> AND          Column1 IN (SELECT column2 FROM Table2(nolock)) -- 1 Rows
>
>
>

8 Sep 2005 2:52 PM

marcmc

got it a cleaner way, thx though