Deleting records with T-SQL efficiently without using IN clause

Question

Is there an efficient way to delete records from SQL Server without using the keyword IN in the below scenario:

-- If Documents were deleted, remove them from your ocean
DELETE FROM IndexHistory 
WHERE DocId IN (SELECT IH.Docid 
                FROM IndexHistory IH 
                LEFT JOIN IndexedLineItems I ON IH.Docid = I.DocId
                WHERE I.Docid IS NULL)

If you study this scenario, you have all the records you want to delete from the query inside the parenthesis.

It is documented fact that using 'IN' is inefficient. So one would think that there should be a way to delete these without the 'IN', because all your records are identify-able without using the "IN".

So for example, I am thinking that something like this might be possible, but I don't have the right syntax:

DELETE FROM IndexHistory IH 
LEFT JOIN IndexedLineItems I ON IH.Docid = I.DocId
WHERE I.Docid IS NULL

I appreciate the answer may still be "no, it's not possible."

If it is not possible, maybe it can be a possible suggestion to improve the language for Microsoft. Before I do that, I thought I would post here to see if I'm missing something.

Don't you mean this: https://stackoverflow.com/questions/16481379/how-to-delete-using-inner-join-with-sql-server — Konrad Z., Oct 09 '18 at 15:47
Marc, not exactly, but it is related and a good find, and helpful for me to post an additional possible answer (mine is not an inner join, but a left join). Some of the answers on bottom also seem to be good. — Heap of Pinto Beans, Oct 09 '18 at 16:14

Gordon Linoff · Answer 1 · 2018-10-10T23:34:19.353

6

Use not exists:

Delete ih from IndexHistory ih
    where not exists (select 1 from IndexedLineItems ili where ih.Docid = ili.DocId)

edited Oct 10 '18 at 23:34

answered Oct 09 '18 at 15:44

Gordon Linoff

1,242,037
58
646
786

you missing a join or something? – Heap of Pinto Beans Oct 09 '18 at 15:54
2

@Kam No, it's a correlated subquery. The join is `ih.Docid = ili.DocId`. – Bacon Bits Oct 09 '18 at 16:03
Fyi, the reason the "not exists" with a correlated subquery works better is it can leverage indexes as its directly joining the outer table to the inner. With an "in" clause, the optimizer cant make assumption about the results it will operate on, so it has to scan it. That turns out to be a big benefit. – Xedni Oct 09 '18 at 16:45
I got error on this one. Msg 102, Level 15, State 1, Line 2 Incorrect syntax near 'ih'. – Heap of Pinto Beans Oct 10 '18 at 16:19
@Kam . . . That was a typo . . . I left out the alias. – Gordon Linoff Oct 10 '18 at 23:34

score 5 · Answer 2 · answered Oct 09 '18 at 15:44

5

I prefer CTEs for readability reason, it can also be changed to use a SELECT to see what i'm going to delete. This NOT EXISTS might also be more efficient:

WITH DeleteData AS
(
  SELECT IH.Docid 
  FROM IndexHistory IH 
  WHERE NOT EXISTS( SELECT 1 FROM IndexedLineItems I
                    WHERE I.DocId = IH.DocId)
)
DELETE FROM DeleteData

You don't need an additional JOIN from this CTE to the table because only one table is selected.

answered Oct 09 '18 at 15:44

Tim Schmelter

450,073
74
686
939

this seemed to have worked. i've never seen this before (With...). I will keep the floor open for now, incase there are additional answers. – Heap of Pinto Beans Oct 09 '18 at 16:05
1

@Kam: it's a [common-table-expression](https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-2017). No matter how complex the CTE itself is, you can always write `SELECT * FROM DeleteData` to see what you will delete. Then it might also be useful to add more columns in the CTE(or simply `SELECT IH.* ...`). Its not necessary to select only the Docid. – Tim Schmelter Oct 09 '18 at 16:19

score 1 · Answer 3 · answered Oct 09 '18 at 16:00

1

In a different thinking, use inner join instead, so you only delete the matches:

DELETE FROM IndexHistory IH 
JOIN IndexedLineItems I ON IH.Docid = I.DocId
WHERE I.Docid IS NOT NULL

answered Oct 09 '18 at 16:00

MEdwin

2,940
1
14
27

score 0 · Accepted Answer · edited Oct 09 '18 at 17:36

0

This works fine...

DELETE IH
    FROM IndexHistory IH
    Left JOIN IndexedLineItems LI
      ON IH.Docid = LI.DocId
    WHERE LI.DocId is Null

edited Oct 09 '18 at 17:36

MEdwin

2,940
1
14
27

answered Oct 09 '18 at 16:23

Heap of Pinto Beans

677
2
12
23

Many great answers, and tons of good information here. But this is the most direct answer. – Heap of Pinto Beans Oct 10 '18 at 16:35

Deleting records with T-SQL efficiently without using IN clause

4 Answers4