I am looking at an application and I found this SQL:
DELETE FROM Phrase
WHERE PhraseId NOT IN(SELECT Id FROM PhraseSource)
Id - GUID primary key
In this case the sub-query could be evaluated for each row if the database system is not smart enough (but in case of MS SQL Server, I suppose it should be able to recognize the fact that you don't need to evaluate the subquery more than once).
Still there is a better solution:
DELETE p FROM Phrase p LEFT JOIN PhraseSource ps ON ps.Id = p.PhraseId WHERE ps.Id IS NULL
This uses the
LEFT JOIN which matches the rows of both tables, but in case there is no match it leaves the
NULL. Now you just check for
NULLs on the left side to see which
Phrases do not have a match and will delete those.
All types of
JOIN statements are very nicely described in this answer.
Here you can see three different approaches for a similar issue compared on MySQL. As @Drammy mentions, to actually see the performance of a given approach, you could see the execution plan on your target database and do performance testing on different approaches of the same problem.