show data from one table but not in another

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)

show data from one table but not in another

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-07-02 : 10:13:23

hello,
I am trying the query :

select id from table1 where id2 not in ( select id2 from table2)

but it runs forever.

is this the way to show data that exists in one table but not in the other.
?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-02 : 10:21:35

Use NOT EXISTS

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-07-02 : 10:22:29

select id
from table1 a
where not exists (select 1 from table2 b where a.id2 = b.id2)

or

select id
from table1 a left join table2 b
on a.id2 = b.id2
where b.id2 is null

sahu74
Posting Yak Master

100 Posts

Posted - 2004-07-06 : 11:42:56

I think you must have fixed the issue but a small change to your query should fix the problem.


select id from table1 where id not in ( select id2 from table2)

instead  of

select id from table1 where id2 not in ( select id2 from table2)

PKS.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources