Comparing two tables. - 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

General SQL Server Forums

New to SQL Server Programming

Comparing two tables.

Author

Topic

Avalanche
Starting Member

3 Posts

Posted - 2007-08-24 : 04:33:50

I'm having a bit of a brain melt.

I've got two identical tables (except for the names) and I need to run a query that outputs the rows that aren't in the primary table.

To clarify table one has 38,450 records and table two has 30,703. I need to output the records that are in table one but not table two.

Everything I've tried keeps returning the records that are in it. They have a ID as primary key and a userid field which is what I want to be able to list.

Any ideas?

Thanks.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2007-08-24 : 04:41:20

pls. post the tables structure. which names are different?

Avalanche
Starting Member

3 Posts

Posted - 2007-08-24 : 04:46:39

quote:
Originally posted by slimt_slimt

pls. post the tables structure. which names are different?

Sorry.

T-1
ID
USERID
EMAIL

T-2
ID
USERID
EMAIL

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2007-08-24 : 04:55:53

select t-1.id
from t-1
join t-2
on t-1.id <> t-2.id

it should work

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2007-08-24 : 05:33:16

or you can solve it like this

select t-1.id
from t-1
inner join t-2
on t-1.id = t-2.id

it will write the id from table1 (assuming there are all ID) that are also in table2 - mainly duplicates

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-24 : 05:44:55

http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx

Madhivanan

Failing to plan is Planning to fail

Avalanche
Starting Member

3 Posts

Posted - 2007-08-24 : 05:55:45

Just out of interest how long should this take to run...it;s been going for half an hour now...

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-24 : 06:19:02

Avalanche,
I suspect you missed a join, making your query a cartesian product (38,450 * 30,703)

select * from t1
left join t2
on
t1.id = t2.id
and t1,userid = t2.userid

where t2.id is null

Jim

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-24 : 06:47:07

Please post proper sample data and expected output.

For example:
What happens when Table1 ID is 10 and userid is 5, and Table2 ID is 10 but userid is 7?

Please post proper sample data to cover all bases and scenarios.
Then also post expected output based on the sample data given to us.

E 12°55'05.25"
N 56°04'39.16"

pootle_flump

1064 Posts

Posted - 2007-08-24 : 08:14:07

quote:
Originally posted by slimt_slimt

select t-1.id
from t-1
join t-2
on t-1.id <> t-2.id

it should work

This won't work and would run for hours too.

Use Jeff's code (linked to by Madhi). Much more flexible.

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-08-24 : 09:31:53

Why not just do this:

SELECT * FROM T1
WHERE ID NOT IN
(
SELECT ID FROM T2
)

Or did I miss something that requires this to be more complex?

pootle_flump

1064 Posts

Posted - 2007-08-24 : 15:04:06

quote:
Originally posted by Zoroaster

Or did I miss something that requires this to be more complex?

Nope. But IN() is very often less efficient than a join or EXISTS.

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-08-24 : 15:16:37

quote:
Originally posted by pootle_flump

quote:
Originally posted by Zoroaster

Or did I miss something that requires this to be more complex?
Nope. But IN() is very often less efficient than a join or EXISTS.

I understand, although doesn't SQL Server convert this to a join anyway on the fly? If so does it actually have any impact to efficiency or is it just a best practice sensibility?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources