Elegant way to delete rows which are not referenced by other table

There’s one notorious gotcha for not in. Basically, id not in (1,2,3) is shorthand for:

id <> 1 and id <> 2 and id <> 3

Now if your TimeEntries table contains any row with a TaskID of null, the not in translates to:

ID <> null and ID <> 1 and ID <> 2 AND ...

The result of a comparison with null is always unknown. Since unknown is not true in SQL, the where clause filters out all rows, and you end up deleting nothing.

An easy fix is an additional where clause in the subquery:

DELETE FROM Tasks 
WHERE  ID not IN 
       (
       SELECT  TaskID 
       FROM    TimeEntries 
       WHERE   TaskID is not null
       )