Using T-SQL EXCEPT with DELETE / Optimizing a quer

Question

The following code removes records of tasks related to inactive projects from the table.

delete from [Deliverables] where 
[Deliverables].[ProjectID] not in 
(
select 
[ProjectID] from [ActiveProjects]
)

I've read somewhere that using NOT IN with subquery that returns a lot of values is not really the most efficient thing to do and it's better to use EXCEPT clause.

However, when I try to use the following code, I get an error (Incorrect syntax near the keyword 'except'.)

delete from [Deliverables]
except
select * from [Deliverables], [ActiveProjects]
where [Deliverables].[ProjectID] = [ActiveProjects].[ProjectID]

How can I use EXCEPT with DELETE? If I can't, is there any way to optimize my query to execute faster?

Answer 1

Try it like this (modify to your needs by adding where-clauses and your column names etc)

delete from table1
  from table1 a
 inner join
      ( select your_column
          from table1
        except
        select your_column
          from table2
      ) b
    on a.your_column = b.your_column;

Answer 2

You can try as well not exists and script would look like:

delete from [Deliverables] 
where not exists 
    (select 1 
        from [ActiveProjects]
        where [ActiveProjects].[ProjectID] = [Deliverables].[ProjectID])

If there is a lot of data in [ActiveProjects] then it should be better solution, however it is all data dependent so please test efficiency before use.

Answer 3

delete d
from [Deliverables] as d
    inner join (
        select d2.[ProjectId] from [Deliverables] as d2
        EXCEPT
        select ap.[ProjectId] from [ActiveProjects] as ap
    } as todel on
        ((todel.[ProjectId] is NULL) and (d.[ProjectId] is NULL))
        or todel.[ProjectId] = d.[ProjectId]

A subtlety: The nice thing about EXCEPT is that it will equate NULL values, rather than deciding that they never match (as "=" does). In your trivial example (just looking at ids) this is unlikely to be of value, but if you are comparing a larger part of a table that allows nulls you'll have to account for this awkward "null matching" or miss a lot of rows.