Delete using JOIN vs using subquery performance

Question

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. ?

Answer 1

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.

Answer 2

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)

Answer 3

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 @Olaf Helper .
The test results of the three methods are as follows:

1. sub-select
   
2. exists
   
3. join
   
   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.