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

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.
15 people are following this question.