Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Equality of Tables in SQL Part II

Tags:
  1. Apr 25, 2016 #1

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    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.
     
  2. jcsd
  3. Apr 25, 2016 #2
    If you do the following query:
    Code (Text):

    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 (Text):

    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
     
  4. Apr 25, 2016 #3
    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.
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook

Have something to add?
Draft saved Draft deleted