Even though, I used DELETE statement with EXISTS and NOT EXISTS very well, I always forget the syntax and specific details. That is why I thought it is a good idea to put these tips into a blog post. More recently, I wrote a DELETE command using EXISTS that deleted all the records and I had to restore the data back.

a) Basic DELETE statement syntax is:

DELETE FROM tablename> WHERE conditions>;

Example:

DELETE FROM contract_rate WHERE contract_effective_date IS NULL;

This statement deletes all the contracts that have blank effective date.

b) Now let us discuss why to use EXISTS or NOT EXISTS in the DELETE statements. You can list only one table in the FROM clause in the DELETE statement. So, if you need to perform a DELETE operation based on the values in another table, easiest way is to use the EXISTS or NOT EXISTS clauses.

The syntax for DELETE statement with EXISTS or NOT EXISTS is:

DELETE FROM table_to_delete_records_from> a WHERE EXISTS (SELECT * FROM table_to_check> b WHERE a TO b JOIN condition>);

Or:

DELETE FROM table_to_delete_records_from> a WHERE NOT EXISTS (SELECT * FROM table_to_check> b WHERE a TO b JOIN condition>);

Example:

To delete all the records from CONTRACT_RATE that have a matching CONTRACT records:

DELETE FROM CONTRACT_RATE a WHERE EXISTS (SELECT * FROM CONTRACT b WHERE a.contract_no = b. contract_no);

To delete all the records from CONTRACT_RATE that DO NOT have a matching CONTRACT records:

DELETE FROM CONTRACT_RATE a WHERE NOT EXISTS (SELECT * FROM CONTRACT b WHERE a.contract_no = b. contract_no);

Where I often land into confusion was instead of using WHERE NOT EXISTS, when I try to use WHERE EXISTS plus a negative relationship between the two tables within the WHERE clause of the SELECT. That approach often gives undesirable results.

One best practice is to run the SQL with SELECT count(*) in the place of DELETE, first, to check if the number records you are about to delete make sense. Another thing that can be helpful is to have a backup before you DELETE records when ever it is possible and appropriate.

Summary

Even though, DELETE with EXISTS or NOT EXISTS is quite simple, improper use of it can give undesirable results. Some of the best practices are to run the SQL statement with SELECT count(*) in the place of "DELETE" first, to make sure you understand the number of records you are about to DELETE, and have a backup before deleting records.