Selecting all records not present in another table

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

General SQL Server Forums

New to SQL Server Programming

Selecting all records not present in another table

Author

Topic

kennethdm
Starting Member

2 Posts

Posted - 2010-05-20 : 08:37:37

Hello everyone, I'm not exactly new new to the wonderful world of SQL, but I do lack some guidance resulting in me hitting brick walls every so often when I try to write a more complicated SQL query.

I just hit another wall with the following issue: I have a temporary memory table which contains a number of file ids, on the other side i have a "physical" table with log entries for these files.
What I am trying to do is select all the logs which are -not- present in the memory table. To clarify this a bit better, here is a simple structure outline:

Memory table (@errorwarnings):
fileid
------
1
2
4
6

Physical table (ds_log):
logid (primary key)
-----
1
2
3
4
5
6

fileid
------
2
2
3
2
5
4

as a result, i would only need to receive logids 3 and 5, since these logs' fileid aren't present in the memory table.
I've used all kinds of approaches, but none seem to perform the way I would like them to.
Please note that the physical table contains a few 100k of rows, so performance is a must.

An example of one of many solutions I tried so far:


select logs.logid
from ds_log logs
where not exists
(
	select *
	from @errorwarnings err
	where logs.fileid = err.fileid
)

Would anyone be so kind as to put me on the right tracks again?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-05-20 : 08:47:01

select logs.logid
from ds_log logs
where logid not in

(select fileid from @errorwarnings)

Jim

Everyday I learn something that somebody else already knew

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-05-20 : 08:50:59

Use Left Join:

select l.logid
from ds_log l left join @errorwarnings e
on l.logid = e.fileid
where
e.fileid IS NULL

Harsh Athalye
http://www.letsgeek.net/

kennethdm
Starting Member

2 Posts

Posted - 2010-05-20 : 09:09:33

@Jimf

Yeah, the not in method is one i tested myself, however it is very slow due when you're working with the amount of data I am.

@Harsh

Yup, I tried that one as well, also pretty slow due to the amount of data, but at least faster than the Not in or Not exists alternative.

I was hoping someone would know an even better way though, that I might be overlooking here :)

Thanks for the fast replies none the less

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-05-20 : 09:44:19

You may try changing in-memory table to local temp table. In my experience, it proves to be much faster than memory table when volume of data is large.

Harsh Athalye
http://www.letsgeek.net/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources