Search code examples
sqlinner-joinsubtraction

MySQL how to remove records in a table that are in another table

I have table A with close to 15000 entries. I have a second table B with 7900 entries with a common field with table A.

I need to extract into a third temporary tableC all entries from table A except the ones that also appear in table B. Simple as it may sound, i havent found a way to do it. The closest i got was this:

INSERT INTO tableC
SELECT * 
FROM tableA
    INNER JOIN tableB
        ON tableA.field IS NOT tableB.field

This SQL just selects everything in tableA, even entries that are in tableB.

Any ideas where i'm going wrong?

Solution

  • What if you try this?

    INSERT INTO tableC 
SELECT * 
FROM tableA 
WHERE tableA.field NOT IN (SELECT tableB.field FROM tableB)

    Or you can try the alternate EXISTS syntax

    INSERT INTO tableC 
SELECT * 
FROM tableA 
WHERE NOT EXISTS (SELECT * FROM tableB WHERE tableB.field = tableA.field)