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?

Your table design is a bit too simple. Introduce another column that will actually cause your rows with values of 1 to be distinct otherwise you won't get your result. — Paul Sasik, Oct 31 '10 at 04:52
Duplicate rows are bad, even though they're accepted in SQL. The theoretical database model (relational model) doesn't allow duplicates, since relations (tables) contain a set. But SQL is not precisely relational, so it's accepted. You'll face that kind of hardship though. Another thing is ordering: supposing your table rows have any kind of intrinsic order (like the order they were inserted) leads to problems. — André Chalella, Aug 14 '23 at 12:54

score 1 · Answer 1 · answered Oct 31 '10 at 05:34

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.

score 1 · Answer 2 · answered Nov 14 '10 at 19:11

1

You could also simply use a tool like DiffKit:

www.diffkit.org

answered Nov 14 '10 at 19:11

score 0 · Answer 3 · answered Oct 31 '10 at 06:02

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
)

sarh · Answer 4 · 2023-02-07T15:53:20.807

In case if it is needed just to observe changes, you can use KS DB Merge Tools for SQLite (I am the author of that tool):

Unfortunately that's the screenshot from the paid Pro version. There is also a Free version available but it can not compare different tables, however it can compare the same table between different database files.

In case if it is needed to build SQL-based solution, then the general approach should be the full outer join which can return both common/matched records and records missing in any of tables. It is not supported by SQLite but can be emulated in a different ways. However it is not applicable in this concrete case because these tables have no primary key.

Comparing two SQLite tables for diffs

4 Answers4