IN CLause Subquery Issue - 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)

IN CLause Subquery Issue

Author

Topic

miranwar
Posting Yak Master

125 Posts

Posted - 2007-03-15 : 16:28:02

I have been archiving data and have been doing some counts before and after. before I ran a delete job i obtained the couts as follows:
select count(*) from visitor_card where K_SO_ID in (select K_SO_id from sign_ons where k_event_id = 100465) which gave me 2719 rows.

After I deleted the data I ran the same query again which gave me 2704 a differnce of 15. However if i run the following JOIN query on the archive database :

select count (*) from visitor_card v , sign_ons s where s.K_so_id = v.K_So_Id and K_Event_id = 100465

I get the correct result of 2719. Cansome tell me why the use of the In a subquery may yield incorrect results for me. I would like to understand.

Thanks

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-15 : 17:28:56

Can there be multiple sign ons with the same K_SO_ID for an event? If so then those queries could give different results, for example

create table #visitor_card
(K_SO_ID int)
create table #sign_ons
(K_SO_ID int, k_event_id int)
insert #visitor_card
select 1 union all
select 2 union all
select 3
insert #sign_ons
select 1, 100465 union all
select 1, 100465 union all
select 2, 100465 union all
select 2, 100465 union all
select 2, 100465 union all
select 3, 100465

select count(*) from #visitor_card where K_SO_ID in (select K_SO_id from #sign_ons where k_event_id = 100465)
select count (*) from #visitor_card v , #sign_ons s where s.K_so_id = v.K_So_Id and K_Event_id = 100465

drop table #visitor_card
drop table #sign_ons

Also, you said However if i run the following JOIN query on the archive database :. Are you saying that you ran the join query on the same or a different database? If different, isn't it just that the data is different?

miranwar
Posting Yak Master

125 Posts

Posted - 2007-03-15 : 18:29:37

thanks but I ra the query with the IN clauseon the prod database before I did the archive. those counts reconcile with the quer that does a join.

miranwar
Posting Yak Master

125 Posts

Posted - 2007-03-15 : 18:32:21

the archive database contains the exact tables as in the query but no indexes. could it be that the tables in the archive db are heaps.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-16 : 13:27:29

The results of a query won't be different based on the presence or absence of indexes. I think your data may be different in the two databases. Just do a simple
SELECT count(*) FROM visitor_card
SELECT count(*) FROM sign_ons
to see if there are the same number of rows in each table right before you run the same JOIN or IN query in each database.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources