all records in table that are 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

General SQL Server Forums

New to SQL Server Programming

all records in table that are not in another

Author

Topic

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2011-05-26 : 13:22:20

Hi -

I apologize for asking this again, but I am just not getting it.

I have two tables - table 1 includes all the members from December 2010 and table 2 includes all the members from January 2011. I want to extract only those that are new, or those members that exist in January 2011 but not in December 2010. Can anyone help?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-05-26 : 13:31:25

Do you mean this thread? Your response indicates that you "got it". Please provide your attempted code as you did before.

EDIT:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Be One with the Optimizer
TG

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2011-05-26 : 13:36:53

Yes, that is it. Can you help with not in???

p1 - select distinct memberid
into ##tempJan11_MM_M
from ##tempJan11_MM

p0 - select distinct memberid
into ##tempDec10_MM_M
from ##tempDec10_MM

select p1.memberid
from ##tempJan11_MM_M p1 left join ##tempDec10_MM_M p0 on p1.memberid = p0.memberid

quote:
Originally posted by TG

Do you mean this thread? Your response indicates that you "got it". Please provide your attempted code as you did before.

Be One with the Optimizer
TG

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-26 : 13:40:17

You left out the where clause in the original thread and said the query didn't work, you leave it out in this post and wonder why the query didn't work. Maybe your question should be, why does the where clause make the query work?

Jim

Everyday I learn something that somebody else already knew

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-05-26 : 14:00:59

As Jim alluded in the linked thread there are multiple ways to accomplish this. If you want to learn then study/understand these examples and also get used to using Books Online.

While you're looking at these try also looking at statistics and execution plan so you can figure out which is best for your scenario.


--create our sample tables
select id into #d from sysobjects where id < 100
select id into #j from sysobjects where id > 50

--to show the execution plan
--set showplan_text on

--to show the IO statistics
--set statistics io on

--set showplan_text off
--set statistics io off

--left outer join
select j.id
from   #j j
left outer join #d d
       on d.id = j.id
where  d.id is null

--not exists
select j.id
from   #j j
where  not exists 
       (select 0 from #d d where d.id = j.id)
       
--not in
select j.id
from   #j j
where  j.id not in (select id from #d)

--except
select id from #j
except
select id from #d

Be One with the Optimizer
TG

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2011-05-26 : 14:12:08

I am not sure I follow this. What do you mean "I left the where clause out?" My question still is, how do I build the query??

quote:
Originally posted by jimf

You left out the where clause in the original thread and said the query didn't work, you leave it out in this post and wonder why the query didn't work. Maybe your question should be, why does the where clause make the query work?

Jim

Everyday I learn something that somebody else already knew

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2011-05-26 : 14:20:20

Thanks TG!!

quote:
Originally posted by TG

As Jim alluded in the linked thread there are multiple ways to accomplish this. If you want to learn then study/understand these examples and also get used to using Books Online.

While you're looking at these try also looking at statistics and execution plan so you can figure out which is best for your scenario.

--create our sample tables
select id into #d from sysobjects where id < 100
select id into #j from sysobjects where id > 50

--to show the execution plan
--set showplan_text on

--to show the IO statistics
--set statistics io on

--set showplan_text off
--set statistics io off

--left outer join
select j.id
from #j j
left outer join #d d
on d.id = j.id
where d.id is null

--not exists
select j.id
from #j j
where not exists
(select 0 from #d d where d.id = j.id)

--not in
select j.id
from #j j
where j.id not in (select id from #d)

--except
select id from #j
except
select id from #d

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources