SQL - return differences between rows (in two different tables) but ONLY if row ID exists in both tables

1
votes

I have two tables that contain two potential differences which I'm trying to pick up on - rows that exist only in one of the tables, and rows that exist in both (sharing a common ID) but having different values for one of the columns (columns are exactly the same in both tables).

CREATE TABLE "MyTable1" (ID INTEGER, FIRST_NAME TEXT, DOB DATE);
INSERT INTO MyTable1 VALUES (1, "Tom", "01-02-18");
INSERT INTO MyTable1 VALUES (2, "Dick", "02-02-18");
INSERT INTO MyTable1 VALUES (3, "Larry", "03-02-18");
INSERT INTO MyTable1 VALUES (4, "Jebroni", "04-02-18");

CREATE TABLE "MyTable2" (ID INTEGER, FIRST_NAME TEXT, DOB DATE);
INSERT INTO MyTable2 VALUES (1, "Tom", "01-02-18");
INSERT INTO MyTable2 VALUES (2, "Dick", "02-02-18");
INSERT INTO MyTable2 VALUES (3, "Barry", "03-02-18");

I can return IDs in MyTable1 not present in MyTable2:

SELECT MyTable1.*
FROM MyTable1
WHERE MyTable1.ID NOT IN (SELECT MyTable2.ID FROM MyTable2)

Returns what I'm after:

ID      FIRST_NAME      DOB
"4"     "Jebroni"       "04-02-18"

For the second part I want to compare values of each column for rows sharing a common ID.

SELECT 'TABLE1' AS SRC, MyTable1.*
FROM (
        SELECT * FROM MyTable1
        EXCEPT
        SELECT * FROM MyTable2
    ) AS MyTable1
UNION ALL
SELECT 'TABLE2' AS SRC, MyTable2.*
FROM (
        SELECT * FROM MyTable2
        EXCEPT
        SELECT * FROM MyTable1
    ) AS MyTable2

This returns more than what I'm after - rows that exist in one table and not the other:

SRC         ID      FIRST_NAME  DOB
"TABLE1"    "3"     "Larry"     "03-02-18"
"TABLE1"    "4"     "Jebroni"   "04-02-18"
"TABLE2"    "3"     "Barry"     "03-02-18"

How should I tweak my last query so that the result is instead:

SRC         ID      FIRST_NAME  DOB
"TABLE1"    "3"     "Larry"     "03-02-18"
"TABLE2"    "3"     "Barry"     "03-02-18"

I.e. restrict what's returns on the basis of the ID being present in both tables?

2
sqlsqlite

2 Answers

1
votes

Restrict the first set of rows to those with a matching ID in the other table:

SELECT 'TABLE1' AS SRC, *
FROM (
        SELECT * FROM MyTable1 WHERE ID IN (SELECT ID FROM MyTable2)
        EXCEPT                 -------------------------------------
        SELECT * FROM MyTable2
    )
UNION ALL
SELECT 'TABLE2' AS SRC, *
FROM (
        SELECT * FROM MyTable2 WHERE ID IN (SELECT ID FROM MyTable1)
        EXCEPT                 -------------------------------------
        SELECT * FROM MyTable1
    );
1
votes

I would just use join:

select *
from MyTable1 t1 join
     MyTable2 t2
     on t1.id = t2.id
where t1.firstname <> t2.firstname or t1.dob <> t2.dob;

I don't think set-based operations will do exactly what you want. But you can add to your query:

where id in (select id from mytable1 t1
             intersect
             select id from mytable2 t2
            )

This will limit results to ids in both tables and you don't have to list out the rest of the columns.