Get rows that don’t exist in another SQL table

There are three ways to select all records from one table that do not exist in another table:

SELECT field1 FROM table1 WHERE field1 NOT IN (SELECT field1 FROM table2)

SELECT field1 FROM table1 WHERE NOT EXISTS (SELECT * FROM table2 WHERE table1.field1 = table2.field1)

SELECT field1 FROM table1 LEFT JOIN table2 ON table1.field1 = table2.field1 WHERE table1.field1 IS NULL

IMPORTANT: NOT INĀ never matches if there is but a single NULL in the list.

Performances should be quite different according to the DBMS we are using:

  • MySQL – NOT EXISTS is (a little bit) less efficient
  • MS SQL Server – JOIN is less efficient
  • PostgreSQL – NOT IN is less efficient
  • Oracle – all methods have the same performance

Leave a comment

Your email address will not be published. Required fields are marked *