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

Question

I have two tables (Tasks and Timeentries), which are connected by a foreign key (TimeEntries.TaskID references Tasks.ID)

Now I'd like to delete all rows from Tasks which are not referenced by the TimeEntries table. I thought that this should work:

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

But it affects 0 rows, even though there are a lot of unreferenced rows in the Tasks table.

What might be the problem here? Of course I could write an SP which iterates all rows, but it seems like this could be done in a one liner.

I guess this is one of those sleeptime underflow errors. Please help!

Answer 1

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
       )