“In almost any case there will be no difference between an IN-subquery and an EXISTS-correlated subquery, the optimizer will rewrite both to a join.
Compared to a JOIN there’s also hardly any difference, if the subquery is the unique part of the 1:m relation.
If the subquery is the m-part, there’s an automatic DISTINCT added to the subquery (in Explain that step will be executed before the join), but you have to add it manually to the JOIN (after the join within Explain).
If that subquery column is very non-unique that automatic DISTINCT might be overridden by a GROUP BY to enhance performance.
If it’s NOT IN vs. NOT EXISTS there might be a huge difference, because NOT IN has to deal with NULLs via three-way-logic, whereas NOT EXISTS simply ignores NULL. If there’s any NULLable column (inner or outer) used by the subquery, it is recommended to use NOT EXISTS instead of NOT IN. And NOT EXISTS is always more efficient than an Outer Join solution filtering for NULLs.”