Delete Rows That Do Not Exist in Another Table

-- ============================================================================
-- How to Delete Rows That do not Exist in Another Table
--
-- Often at work, we will pull data from an upstream system into a delta table,
-- delete the matching rows in the main table and insert the updated rows. This
-- works great and is fast but has a major assumption : That the rows from the
-- upstream system will NEVER disappear.
--
-- As part of a process I am working on, I needed to be able to the delta table
-- run but then remove any rows that disappeared from the upstream system. Below
-- are a few ways you can do this. I suggest you use the first as it is the most
-- efficient of the three assuming you have a index on your key columns.
-- ============================================================================

--
-- Using LEFT JOIN/IS NULL:
DELETE FROM BLOB b
    LEFT JOIN FILES f ON f.id = b.fileid
    WHERE f.id IS NULL;

--
-- Using NOT EXISTS:
DELETE FROM BLOB
    WHERE NOT EXISTS(
        SELECT NULL FROM FILES f
        WHERE f.id = fileid
    );
--
-- Using NOT IN:
DELETE FROM BLOB
    WHERE fileid NOT IN (
        SELECT f.id FROM FILES f);