CODEWITHSUNDEEP
sqlsql-server
ddsprasad
ddsprasad

Reputation: 82

How do I compare 2 tables' records?

I have 2 tables each table has 2 count of group by records , I need to compare those records are equal or not .

Table 1 result: set after count(input),group by (input)

    input count
    0     10
    1     122


Table 2 result set

    input count
    0      10
    1      122

I need to compare and see 2 tables has equal count. Any thoughts?

Upvotes: 1

Views: 98

Answers (2)

CRAFTY DBA
CRAFTY DBA

Reputation: 14915

Please read the MSDN entry on except and intersect. http://technet.microsoft.com/en-us/library/ms188055.aspx

Except is an order specific operand. Thus, Abe's answers is partially right and partially wrong.

Below is a test in [tempdb] with tables [a] and [b] with the same records.

EXCEPT returns any distinct values from the left table that are not also found on the right table.

Thus an empty set is returned in our first compare since all records in table [a] are in table [b].

-- use tempdb
use tempdb;
go

-- table a
create table a
(
    a_input int,
    a_count int
);

-- table b
create table b
(
    b_input int,
    b_count int
);

-- add data
insert into a values (0,10), (1,122);
insert into b values (0,10), (1,122);

-- any differences
select * from a
except 
select * from b;

Lets add a new record to the right hand side of the compare. Again, the result set is empty. However, the tables are not the same. Table [b] has one more record.

-- add to right side
insert into b values (2,20);

-- any differences
select * from a
except 
select * from b;

The correct solution is to take the union of two compares. Left compared to right and right compared to left. 

-- Left side
select * from 
(
  select * from a
  except 
  select * from b
) lcompare

union

-- Right side
select * from
(
  select * from b
  except 
  select * from a
) rcompare;

If the result set is not empty, there are differences.

A better way to do this is to union the two tables into one data set, group by every column, and get counts that are not = 2.

The enclosed article has dynamic TSQL to do this.

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

I would think this is faster since it does one union and a group by. The first solution does two joins to find any mismatched row, then a union.

Again, the difference will only be seen in large record sets.

Good luck.

Upvotes: 1

Abe Miessler
Abe Miessler

Reputation: 85036

Try this:

Select * from Table1
Except
Select * from Table2

If nothing is returned then they are equal.

Upvotes: 0

Related Questions