# Thread: Table Comparison

1. Registered User
Join Date
Jan 2003
Posts
126

## Unanswered: Table Comparison

I have two tables that are the same. The only difference is the amount of data in one vs. the other.

I know I have 1469186 rows in table A I also know there is 1463219 rows in table b, there is a difference of 5967 rows, and I want to see what those rows are, how can I write a query to figure it out?

Thanks,

Ken

2. Registered User
Join Date
Feb 2002
Location
Houston, TX
Posts
809
crude but usabel...

select a.*
from TableA a
left join TableB b on a.keycol = b.keycol
where b.keycol is null

select b.*
from TableB b
left join TableA a on b.keycol = a.keycol
where a.keycol is null

3. Registered User
Join Date
Jan 2003
Posts
126
Thanks for the reply!!!!

I used:

Code:
```select a.*
from rg_ba_orders a
left join rg_ba_orders1 b on a.[order number] = b.[order number]
where b.[order number] is null

select b.*
from rg_ba_orders1 b
left join rg_ba_orders a on b.[order number] = a.[order number]
where a.[order number] is null```
But it returned nothing? Did I not apply something right?

Ken

4. Registered User
Join Date
Feb 2002
Location
Houston, TX
Posts
809
off hand I would say it was okay...

Code:
```create table #rg_ba_orders([order number] int, [order desc] varchar(10))
create table #rg_ba_orders1([order number] int, [order desc] varchar(10))

insert into #rg_ba_orders values(1,'A')
insert into #rg_ba_orders values(2,'B')

insert into #rg_ba_orders values(3,'C')
insert into #rg_ba_orders values(4,'D')
insert into #rg_ba_orders1 values(3,'C')
insert into #rg_ba_orders1 values(4,'D')

insert into #rg_ba_orders1 values(5,'E')
insert into #rg_ba_orders1 values(6,'F')

select 'A' as 'Table',a.*
from #rg_ba_orders a
left join #rg_ba_orders1 b on a.[order number] = b.[order number]
where b.[order number] is null
union
select 'B' as 'Table',b.*
from #rg_ba_orders1 b
left join #rg_ba_orders a on b.[order number] = a.[order number]
where a.[order number] is null
order by 1,2```

have you looked at a few rows to see if the keys columns line up?

5. Registered User
Join Date
Jan 2003
Posts
126
I think that is part of the problem.

Is there a way to select non distinct rows? I was thinking if I could I might be able to use a union query.

Both tables don't have a primary key.

Ken

6. Registered User
Join Date
Feb 2002
Location
Houston, TX
Posts
809
add to the prior example...

Code:
```select 'In B Not In A' as 'Info', t1.* from (select * from #rg_ba_orders union select * from #rg_ba_orders1) t1
left join #rg_ba_orders a on t1.[order number] = a.[order number] and t1.[order desc] = a.[order desc]
where a.[order number] is null
union
select 'In A Not In B' as 'Info', t1.* from (select * from #rg_ba_orders union select * from #rg_ba_orders1) t1
left join #rg_ba_orders1 a on t1.[order number] = a.[order number] and t1.[order desc] = a.[order desc]
where a.[order number] is null
order by 1,2```
Not the best solution but I think you get the idea

7. Registered User
Join Date
Jan 2003
Posts
126
This also works, but for some reason it doens't work on the two tables in question:

Code:
```select * from B
WHERE [Order Number] not
IN (SELECT [Order Number]
FROM A)```
I have no idea why it will not select the extra rows. It continues to return nothing.

I'll keep trying and post back when I find the answer.

8. Registered User
Join Date
Feb 2002
Location
Houston, TX
Posts
809
Post the DDL for both tables, could be something small is being over looked.

9. Registered User
Join Date
Jan 2003
Posts
126
What is DDL? and I'll post it!

10. Registered User
Join Date
Jan 2003
Posts
126

## DOH

Well, I found the problem, table 1 had duplicates in it, which it should not of.

I ran:

Code:
```select count(distinct [order number]) from rg_ba_orders
select count(distinct [order number]) from rg_ba_orders1```
and the counts where the same. They were duplicated and not unique rows due to a rounding error on some of the values in one of the tables.

I learned a lot though, and there is definetly more then one way to skin a problem.

Tell me what DDL is so I know!

Thanks for all your help!!

Ken

11. Registered User
Join Date
Feb 2002
Location
Houston, TX
Posts
809
from BOL:

The SQL language has two main divisions: Data Definition Language (DDL), which is used to define and manage all the objects in an SQL database, and Data Manipulation Language (DML), which is used to select, insert, update, and delete data in the objects defined using DDL. The Transact-SQL DDL used to manage objects such as databases, tables, and views is based on SQL-92 DDL statements, with extensions. For each object class, there are usually CREATE, ALTER, and DROP statements, such as CREATE TABLE, ALTER TABLE, and DROP TABLE. Permissions are controlled using the SQL-92 GRANT and REVOKE statements, and the Transact-SQL DENY statement.

12. Registered User
Join Date
Mar 2003
Location
Bucharest, Romania
Posts
80

## Re: Table Comparison

If you still want to know what exact rows are duplicated, try this:

select A.* from A join
(select [order number] from A group by keycol having count([order number])>1) T1
on A.[order number]=T1.[order number] order by A.[order number]

if the key column of you table, are in fact two or more columns:

select A.* from A join
(select keycol1,keycol2 from A group by keycol1,keycol2 having count(*)>1) T1
on A.keycol1=T1.keycol1 and A.keycol2=T1.keycol2 order by A.keycol1,A.keycol2

if the key column is of type uniqueidentifier (GUID datatype in SQLServer) you cannot use the syntax count(GUIDcol), instead use count(convert(char(38),GUIDcol)

IONUT

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•