Compare Two Tables and List the Difference
MySQL Query to compare two tables and provide the difference
The following SQL query will list the difference
SELECT `year`, `icd9` FROM `table1`
WHERE `year` = 2018
AND NOT EXISTS (
SELECT 1
FROM `table2`
WHERE
LEFT(`table1`.`icd9`, 3) = LEFT(`table2`.`icd9`, 3)
AND
`table1`.`year` = `table2`.`year`
);
Example:
-- SQL Query to Compare ICD9s in Two Tables for a Specific Year
SELECT
`year`,
`icd9`
FROM
`basilicata_asl_matera`
WHERE
`year` = 2018
AND NOT EXISTS (
SELECT
1
FROM
`capitolo_icd9_frequency_distribtuion_table_2018`
WHERE
LEFT(`basilicata_asl_matera`.`icd9`, 3) = LEFT(`capitolo_icd9_frequency_distribtuion_table_2018`.`icd9`, 3)
AND
`basilicata_asl_matera`.`year` = `capitolo_icd9_frequency_distribtuion_table_2018`.`year`
);
To flag the difference between the two tables:
Insert a column named flag at the beginning of your table. The column's data type should be VARCHAR(1) with a default value set to n. All cells in this column will initially contain the value n.
ALTER TABLE your_table_name
ADD COLUMN flag VARCHAR(1) DEFAULT 'n' NOT NULL FIRST;
To significantly improve performance, create indexes on the columns used for matching in both tables. Once the indexes are in place, execute the provided query to update the flag column accordingly. After the update completes, search the flag column for the letter n; this will return all unmatched rows. This approach leverages indexing to speed up comparisons and ensures an efficient way to identify discrepancies between the tables:
UPDATE table1 t1
JOIN table2 t2 ON t1.match_column = t2.match_column
SET t1.flag = 'y';
Topic last reviewed: 08/04/26