asked on

diff between 2 idential tables

Hi

Wanted to compare two identical oracle tables - traditionally I would do something like this

select col1..col4
from tableA
minus
select col1...col4
from tableB
union all
select col1..col4
from tableB
minus
select col1...col4
from tableA

can this be written in ANSI SQL way like this - can you verify the code and tell me if this will work

select col1..col4 from
(
select col1..col4 from tableA
) old_table
full outer join
(
select col1..col4
from tableB
) new_table
on (old_table.col1 = new_table.col1 and old_table.col5 = new_table.col5)
group by col1..col4
--having old_table.col3 <> new_table.col3 --commented since I am trying to find out what column values are different

Which sql tells exactly what is in tableA not in TableB and vice versa - in tableB not in tableA

I need to get this today and any help appreciated-

Christoffer Swanström

Your first version using MINUS works fine. Why would you want to write it using a full outer join?

The second version using FULL OUTER JOIN could look like this:

select old_table.col1, old_table.col2, old_table.col3, old_table.col_4,  
new_table.col1, new_table.col2, new_table.col3, new_table.col_4
from
(
select col1..col4 from tableA
) old_table
full outer join
(
select col1..col4
from tableB
) new_table
on (old_table.col1 = new_table.col1 and old_table.col2 = new_table.col2 and old_table.col3 = new_table.col3 and old_table.col4 = new_table.col4)
where old_table.col1 is null or new_table.col1 is null

Open in new window

This assumes that none of the columns has NULLs

Sean Stuber

you can use the minus/union version to identify them simply by adding a column to act as an identifier

select 'A only', col1..col4
from tableA
minus
select 'A only',col1...col4
from tableB
union all
select 'B only',col1..col4
from tableB
minus
select 'B only',col1...col4
from tableA

mahjag

ASKER

columns have null - sorry not to mention that -

I tried the column identifier for some reasons the rows got doubled when I added identifier - I am looking into that now

mahjag

ASKER

first of all the count of tables tableA and tableB are different - I want to find out all the rows that are in A and not in B and all rows in B not in A - will that account for the difference .

Sean Stuber

which "that" are you referring to?

the minus/union with the identifier column will work

using an outer join can work too but, I generally use the minus/union method

Christoffer Swanström

Then use the suggestion from sdstuber, it will show you all the rows that are different or missing from either table. The difference in row counts can be accounted for as follows (using some made up numbers):

row count table a: 1000
row count table b: 950

rows in table a missing from b: 100
rows in table b missing from a: 50

rows in common in a and b: 900

mahjag

ASKER

I mean the minus and union - traditional way -

Also first I want to address the count -

which query will identify from my table row count -

row count table a: 50000
row count table b: 49952
--48 diff rows

which query will identify me the difference of 48 rows - can you please help to pont out ?

mahjag

ASKER

There are about 52 columns in each tableA and TableB and some of the columns like created by created date and last updated by last updated date are going to be different - do you want me to list everything other than who updated/created columns?

Sean Stuber

you tell us.

do you want all 52 columns displayed? If so, then include all 52 columns.

If you only want to compare the created/updated columns, then only include those.

and yes, the minus/union with identifier column will handle row count differences.

if A has 7 rows and B has 5 rows with 3 of the rows in each being the same you'll see something like this...

A only, col1, col2, col3...
A only, col1, col2, col3...
A only, col1, col2, col3...
A only, col1, col2, col3...
B only, col1, col2, col3...
B only, col1, col2, col3...

if you want numeric counts of the values, then count the results of that query and group by the identifier column (or partition if you want to use analytic instead of aggregate)

Christoffer Swanström

The net difference is 48 rows. As I showed in my example, that net difference could be made up of any combination of differences, such as 48 rows that exist in A but not in B, or 148 rows that exist in A but not B plus 100 rows that exist in B but not A.

I think the easiest way is to use the MINUS logic. With the additional field in the query from sdstuber you will see which of the returned rows exist only in A or B.

Sean Stuber

The simplest answer to all of your question is - just try them.
It's really pretty simple.

If they don't work, post what you tried and we can help fix your syntax.

awking00

You can also use -
select * from tableA
union
select * from tableB
minus
(select * from tableA
intersect
select * from tableB)

Sean Stuber

the problem with the union/minus/intersect method is you can't add an identifier column to tell which rows are from A and which are from B

mahjag

ASKER

I get this list from the traditional what is in A not in B and vice-versa

A only, col1, col2, col3...
A only, col1, col2, col3...
A only, col1, col2, col3...
A only, col1, col2, col3...
B only, col1, col2, col3...
B only, col1, col2, col3...

but how can I say which col is different - by eyeballing all the rows?

Christoffer Swanström

Yes, you would probably want to order the rows by the primary key (if there is one), then inspect the rows to find the differences.

Of course you could write a query that identifies the columns that are different, but that would be quite tedious if you have 52 columns. When there are relatively few rows that are different you are probably faster with a visual inspection.

Sean Stuber

>>> but how can I say which col is different

there is no function in Oracle to explicitly identify the columns themselves.

You try something really complicated like doing an UNPIVOT so you could identify each column as a row and then do a diff check on those. but that's sort of outside the scope of this question.

mahjag

ASKER

there is going to be more delta and eye ball is not going to work - if there is no solution in oracle then I need something in excel to write a vb function that would just highlight the columns to be different.

Sean Stuber

there is a solution in Oracle

comparing rows is easy that's done.

to compare columns, first to convert them to rows then apply the same methods above

mahjag

ASKER

Hi Sdstuber

can you post the way to do that - I feel that is still in the scope of the questions - I am cutting and pasting my original askfrom top -
***
--having old_table.col3 <> new_table.col3 --commented since I am trying to find out what column values are different

Sean Stuber

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#CHDJBHHI

mahjag

ASKER

Thanks Sdstuber for the link - how does that work - can you write a sample code to verify -

mahjag

ASKER

from your example above - A only, col1, col2, col3...
A only, col1, col2, col3...
A only, col1, col2, col3...
A only, col1, col2, col3...
B only, col1, col2, col3...
B only, col1, col2, col3...

how will unpivot show which col value is different?

Sean Stuber

>>> how will unpivot show which col value is different?

it won't by itself.

first you unpivot, so every column is a row.

then you do the same technique above as you would with any other row comparisons

mahjag

ASKER

example to use unpivot please -

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial