Help with pretty simple stuff - 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 2005 Forums

Transact-SQL (2005)

Help with pretty simple stuff

Author

Topic

perate
Starting Member

2 Posts

Posted - 2009-03-16 : 19:14:33

Hey, i have this "in" and "not in" nested select statement somehow wrongly written


SELECT COUNT ([NUMERO IBS]) FROM T0606;

select COUNT([NUMERO IBS]) from t0606
where [NUMERO IBS]IN
(select [NUMERO IBS]  from
t0607);

select COUNT([NUMERO IBS]) from t0606
where [NUMERO IBS] NOT in
(select [NUMERO IBS]  from
t0607);

For some reason I cant understand the first query returns 20637 , the second one 20423 and the third one on 0. Why is this? shouldn't it be the difference between the first two?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-16 : 19:34:00

First query tells you there are 20,637 records in table t0606.

Second query tells you which records do exist in table t0606 AND also in table t0607.
There are 20,423 matches.

Third query tells you which records do exist in table t0606 AND not exist in table t0607.
There are 0 matches.

The difference in 214 record can be duplicate records over [Numero IBS] column.

Post the result of these queries

-- 1
SELECT TOP 5 [Numero IBS], COUNT(*) AS Items
FROM t0606 GROUP BY [Numero IBS] HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC

-- 2
SELECT COUNT(DISTINCT [Numero IBS]) FROM t0606

-- 3
SELECT TOP 5 [Numero IBS], COUNT(*) AS Items
FROM t0607 GROUP BY [Numero IBS] HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC

-- 4
SELECT COUNT(DISTINCT [Numero IBS]) FROM t0607

E 12°55'05.63"
N 56°04'39.26"

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-03-16 : 19:48:41

If you have nulls in t0607, another reason could be the funny 3-valued logic used by SQL. (Null is not equal to anything; then again, it could be in some cases) The following queries illustrate this:

-- declare two tables @t1 and @t2
declare @t1 table ( id1 int );
declare @t2 table ( id2 int );

-- insert some data into @t1
insert into @t1 (id1) values (1);
insert into @t1 (id1) values (2);
insert into @t1 (id1) values (3);

-- insert some data into @t2
insert into @t2 (id2) values (1);

-- now select the counts. you get what you would expect.
select count(id1) from @t1 where id1 not in (select id2 from @t2)

-- insert a null into @t2.
insert into @t2 (id2) values (null);

-- and do the count again.  what you get is not probably what you expected.
select count(id1) from @t1 where id1 not in (select id2 from @t2)

-- exclude nulls and do the count. you get what you would expect.
select count(id1) from @t1 where id1 not in (select id2 from @t2 where id2 is not null)

perate
Starting Member

2 Posts

Posted - 2009-03-17 : 09:39:43

Hey! Thaks for answering so fast! My results to the queries were:


SELECT TOP 5 [Numero IBS], COUNT(*) AS Items
FROM t0606 GROUP BY [Numero IBS] HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC

173231 107
802892 95
170689 51
171143 49
175638 38

SELECT COUNT(DISTINCT [Numero IBS]) FROM t0606

11151

SELECT TOP 5 [Numero IBS], COUNT(*) AS Items
FROM t0607 GROUP BY [Numero IBS] HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC

802892 98
173231 66
171143 49
175638 38
807301 36

SELECT COUNT(DISTINCT [Numero IBS]) FROM t0607

11162

select count (*) from t0607 where isnull([Numero IBS],0)=0

2

There are 2 nulls in the table

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-17 : 14:19:52

Is it just a coincidence that the 214 records before is twice the number here

173231 107

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources