Equality of Tables in SQL Part II

[WWGD]

Hi all,
I think I found the answer to determine whether two SQL tables are equal. Symmetric difference of sets:

A(Symmetric Difference) B = ## A \cup B - (A\cap B) ##. One can show that sets A,B are equal iff
A (Symmetric Difference) B =A (=B, of course)

How does one implement this in the language of MS SQL?

For normalized tables, one can use 'EXCEPT' or 'INTERSECT' , but this does not work for non-normalized tables with repeated records.

 

[cpscdave]

If you do the following query:

SELECT pkey FROM tableA WHERE  pkey in (SELECT pkey FROM tableB where 1)

it should give you (A∩B)

and since [A - (A∩B)] U [B -(A∩B)] is equal to AUB -(A∩B)

SELECT pkey FROM tableA WHERE pkey NOT IN (SELECT pkey FROM tableA WHERE  pkey in (SELECT pkey FROM tableB where 1)) UNION
SELECT pkey FROM tableB WHERE pkey NOT IN (SELECT pkey FROM tableA WHERE  pkey in (SELECT pkey FROM tableB where 1))

Should give you what you're looking for.

I'm sure there will be easier ways however :)

*EDIT* that's MySQL not MS SQL. Not sure what the difference would be :) I can't read apparently

 

[Pepper Mint]

You may prefer use of UNION or UNION ALL as they both look common to ## \cup ## and ##\cap ## in maths. Something like this

SELECT*FROM (
SELECT * FROM TABLE A
UNION ALL
SELECT * FROM TABLE B
);
IF@@ROWCOUNT=0
BEGIN
PRINT 'Equal'
END

Variations of this use of union include checks for EXISTS or NOT EXISTS of column data, which may work better in terms of performance and practicality only in case the number of columns is small.

So the mathematical formula to check for 2 MSSQL tables's equality in my case would be
|R|=|A## \cup ## B| where |R| denotes the number of elements in R
if |R|>0 then A!=B or A=B otherwise.