To compare two tables in db2

Sajeev · #1 (**permalink**) 08-02-11, 06:31

what is the query to compare values in two different tables which contains same column name and datatype?

Eg:
TAB1
ID COL1 COl2 Col3
1 A A A
2 B B B
3 C C C
4 A A A

TAB2
ID COL1 COl2 Col3
1 A A A
2 B B B
3 C D C
4 A A A

Output should be :

ID COL1 COl2 Col3
3 C C C
3 C D C

przytula_guy · #2 (**permalink**) 08-02-11, 06:49

with replication there is a utility shipped asntdiff for this purpose
have a look at this in info center
otherwise regular sql and joins.....

tonkuma · #3 (**permalink**) 08-02-11, 06:54

What information do you want?
Depending on the information you want,
you can use EXCEPT, NOT EXISTS, NOT IN, so on.

Peter.Vanroose · #4 (**permalink**) 08-02-11, 08:32

Code:

SELECT 'Only in A:', count(*)
FROM  (SELECT * FROM A EXCEPT ALL SELECT * FROM B)
UNION ALL
SELECT 'Only in B:', count(*)
FROM (SELECT * FROM B EXCEPT ALL SELECT * FROM A)

Sajeev · #5 (**permalink**) 08-03-11, 04:10

Thanks for ur reply..
I got this doubt solved with this query.. Peter's query was also working fine..

SELECT ID,COL1,COl2,COl3,count(*)
FROM
(

SELECT A.ID, A.COL1,A.COL2,A.COL3
FROM TAB1 A

UNION ALL

SELECT B.ID, B.COL1,B.COL2,B.COL3

FROM TAB2 B
) TMPTBL
GROUP BY
ID,COL1,COL2,COl3

HAVING COUNT(*) <> 2
ORDER BY ID

HostingBee · #6 (**permalink**) 08-03-11, 09:27

Quote:

Originally Posted by tonkuma

What information do you want?
Depending on the information you want,
you can use EXCEPT, NOT EXISTS, NOT IN, so on.

Sure. All in Exccept

Sajeev · #7 (**permalink**) 08-04-11, 01:16

But with EXCEPT u may get duplicates.

tonkuma · #8 (**permalink**) 08-04-11, 01:44

From Peter's example...

Quote:

Code:

SELECT 'Only in A:', count(*)
FROM  (SELECT * FROM A EXCEPT ALL SELECT * FROM B)
UNION ALL
SELECT 'Only in B:', count(*)
FROM (SELECT * FROM B EXCEPT ALL SELECT * FROM A)

remove marked parts...

Code:

SELECT 'Only in A:', count(*)
FROM  (SELECT * FROM A EXCEPT ALL SELECT * FROM B)
UNION ALL
SELECT 'Only in B:', count(*)
FROM (SELECT * FROM B EXCEPT ALL SELECT * FROM A)

then, you'll get an answer.

Code:

(SELECT * FROM A EXCEPT ALL SELECT * FROM B)
UNION ALL
(SELECT * FROM B EXCEPT ALL SELECT * FROM A)

Internet Services

Web Development

Digital Marketing

Consumer Tech