CODEWITHSUNDEEP
sql
user2080797
user2080797

Reputation:

Finding differences between row in different table with same key

I have two tables, that are rather large (about 40 columns), they are filled with mainly integer values, from calculation results. One table is the result of the most recent calculation run, and the other is a stored historical calculation for history. Both tables have the same primary key values.

Does anyone know if it possible to to compare these tables, based on primary key, for differences? - or also for exact matches? I realise that I could compare every single column, but with 40 columns, thats a long statement and easy to make mistakes if I need to come back and change something later.

For examples sake (with just 5 columns) I could:

SELECT *
FROM kalk_results
INNER JOIN kalk_history
ON kalk_results.id1 = kalk_history.id1
AND kalk_results.id2 = kalk_history.id2
WHERE kalk_results.result_1 <> kalk_history.result_1
OR kalk_results.result_2 <> kalk_history.result_2
OR kalk_results.result_3 <> kalk_history.result_3
OR kalk_results.result_4 <> kalk_history.result_4
OR kalk_results.result_5 <> kalk_history.result_5

Upvotes: 0

Views: 86

Answers (1)

jarlh
jarlh

Reputation: 44696

To return rows found in both tables use INTERSECT:

select * from kalk_results
intersect
select * from kalk_history

To return rows from kalk_results, that are not found in kalk_history, use EXCEPT:

select * from kalk_results
except
select * from kalk_history

Upvotes: 1

Related Questions