question

sakuraime avatar image
0 Votes"
sakuraime asked SeeyaXi-msft answered

Delete using JOIN vs using subquery performance

there are two query for delete

delete from tableA a join tableb b on a.col1=b.col1

vs

delete from tableA a where col1 in (select col1 from tableb)


which one is performed faster. ?

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

Short answer: it depends.

Somewhat longer answer: for your actual case, you will need to benchmark.

If I am to make a guess, I would guess that you get the same performance in most cases.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

which one is performed faster. ?

Without knowing database/table design and existing indexes and data distribution no one can say.
BTW, there is a a third-way with EXISTS, often fast:

 delete 
 from tableA a 
 where exists (select 1 from tableb as b where b.col1 = a.col1)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered

Hi @sakuraime,

It can't say for certain. SQL isn’t just all about the time it takes to finish something, but it is about the sharing of resources as well.
There is a third-way with EXISTS and the specific execution statement is as written by @OlafHelper-2800.
The test results of the three methods are as follows:
1. sub-select
101219-t1.png
2. exists
101269-t2.png
3. join
101220-t3.png
When rerunning the tests, you will get varying times as different things are running on your system.
The key is to try different methods, and make sure you are picking the one that works best for your environment and situation.

Best regards,
Seeya


If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



t1.png (23.4 KiB)
t2.png (19.1 KiB)
t3.png (18.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.