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        ) 

Answer 2

One way, this will take care of the 'problem' you are having with nulls (see link below for more info)

DELETE FROM Tasks  WHERE NOT EXISTS (SELECT 1 FROM TimeEntries                    WHERE TimeEntries.TaskID  = Tasks.ID ) 

To understand the problem you are having, take a look at Select all rows from one table that don't exist in another table