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