Comparing two SQLite tables for diffs

Question

I have two SQLite tables I want to compare. To set up the tables:

CREATE TABLE A (Value);
CREATE TABLE B (Value);

INSERT INTO A VALUES (1);
INSERT INTO A VALUES (1);

INSERT INTO B VALUES (2);
INSERT INTO B VALUES (1);

The closest I got with comparing two tables is using the SQL below:

SELECT 'A' AS Diff, *
FROM (SELECT * FROM A EXCEPT SELECT * FROM B)
UNION ALL
SELECT 'B' AS Diff, *
FROM (SELECT * FROM B EXCEPT SELECT * FROM A)

The result I want is

A; 1
B; 2

However, I only get

B; 2

because the EXCEPT keyword removes all 1's coming from Table A regardless of how many 1's there are in Table B.

How should I be comparing the two tables?

Answer 1

Can you try using -

select Distinct A.Value as Avalue,B.Value as Bvalue from a inner join B on A.Value <> B.Value

This should give you a result -

AValue  |  BValue
  1     |     2

I used a distinct because your table doesn't have primary keys or uniques defined and so there is a possiblity this query without a Distinct would display duplicates.

You can also try using some free database compare tools that are available for complex comparisons. There are tools that compare tables, procedures etc. These also generate scripts for the differences. But, I have never just such a tool for sql lite. So, I am not sure if such tools are availabe for sql lite.

Answer 2

I found out the answer. It's a lot of code if the tables have multiple columns, but I can just programmatically generate the SQL using the same pattern.

/* Create Example Data */       
CREATE TABLE A (TheValue);      
CREATE TABLE B (TheValue);      
INSERT INTO A VALUES (1);       
INSERT INTO A VALUES (1);       
INSERT INTO B VALUES (2);       
INSERT INTO B VALUES (1);       

/* Format data */       
CREATE TABLE TmpA (Id INTEGER PRIMARY KEY, TheValue);       
CREATE TABLE TmpB (Id INTEGER PRIMARY KEY, TheValue);       
INSERT INTO TmpA (TheValue) SELECT * FROM A ORDER BY TheValue;      
INSERT INTO TmpB (TheValue) SELECT * FROM B ORDER BY TheValue;      
CREATE INDEX idx_TmpA ON TmpA (TheValue ASC);       
CREATE INDEX idx_TmpB ON TmpB (TheValue ASC);       

/* Result */        
SELECT 'A' AS Diff, *       
FROM        
(       
        SELECT Id - (SELECT Min(Id) FROM TmpA AS A2 WHERE A2.TheValue = TmpA.TheValue) AS SubNum, TheValue
        FROM TmpA
        EXCEPT
        SELECT Id - (SELECT Min(Id) FROM TmpB AS A2 WHERE A2.TheValue = TmpB.TheValue) AS SubNum, TheValue
        FROM TmpB
)       
UNION ALL       
SELECT 'B' AS Diff, *       
FROM        
(       
        SELECT Id - (SELECT Min(Id) FROM TmpB AS A2 WHERE A2.TheValue = TmpB.TheValue) AS SubNum, TheValue
        FROM TmpB
        EXCEPT
        SELECT Id - (SELECT Min(Id) FROM TmpA AS A2 WHERE A2.TheValue = TmpA.TheValue) AS SubNum, TheValue
        FROM TmpA
)