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?
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
);
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments