These two queries are doing a two-way comparison between the procedure or diagnostic data table (proc_united_kingdom) and the hospital table (hospitals_united_kingdom) based on the column serial. They help you find which serial numbers exist in one table but not the other.

-- Gets every unique serial from proc_norway_all

-- Checks whether that serial exists in the hospitals table

-- Returns only those that don’t appear in hospitals_norway


SELECT DISTINCT p.serial

FROM `proc_united_kingdom` AS p

WHERE p.serial NOT IN (

    SELECT h.serial

    FROM `hospitals_united_kingdom` AS h

);


-- Reverse comparison

SELECT DISTINCT h.serial

FROM `hospitals_united_kingdom` AS h

WHERE h.serial NOT IN (

    SELECT p.serial

    FROM `proc_united_kingdom` AS p

);


The following performs a safe clean up by deleting rows from one table only when they no longer have a matching record in another table:

-- Delete serials from the data table

DELETE h

FROM `0uk_procedures_all` AS h

LEFT JOIN `hospitals_united_kingdom_proc` AS p

    ON p.serial = h.serial

WHERE p.serial IS NULL;


-- Delete serials from the hospital table

DELETE h

FROM `hospitals_united_kingdom_proc` AS h

LEFT JOIN `0uk_procedures_all` AS p

    ON p.serial = h.serial

WHERE p.serial IS NULL;



Topic last reviewed: 16/12/25