Equality of Tables in SQL Part II

  • Thread starter WWGD
  • Start date
  • Tags
    sql
  • #1
WWGD
Science Advisor
Gold Member
5,421
3,685
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.
 

Answers and Replies

  • #2
402
120
If you do the following query:
Code:
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)

Code:
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* thats MySQL not MS SQL. Not sure what the difference would be :) I can't read apparently
 
  • Like
Likes WWGD and Pepper Mint
  • #3
87
138
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
);
[email protected]@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.
 
  • Like
Likes WWGD

Related Threads on Equality of Tables in SQL Part II

  • Last Post
Replies
7
Views
988
Replies
1
Views
804
Replies
3
Views
1K
  • Last Post
Replies
8
Views
1K
  • Last Post
Replies
4
Views
1K
  • Last Post
Replies
4
Views
3K
Replies
4
Views
1K
  • Last Post
Replies
4
Views
2K
  • Last Post
Replies
2
Views
3K
  • Last Post
Replies
3
Views
7K
Top