Correlated Subqueries, NOT EXISTS & Anti Joins - Clarification
I am a bit confused now regarding correlated subqueries and the NOT EXISTS operator (I had originally thought I understood but am all mixed up now!)
I was reading around and have gathered that if one were to use EXISTS that this isnt the preferred method, and that the query should actually be re-written as a join (im guessing INNER JOIN) to improve performance.
NOT EXISTS is also not a recommended method from what I read as well.
Correlated subqueries in general are not recommended for performance issues, since the subquery needs to be executed once for every row returned by the outer query.
I was reading up on anti joins, and found that a lot of people referred to anti joins with the use of NOT EXISTS and a correlated subquery, which if my above understanding is correct is super bad in performance as it combines two things that people dont recommend.
I was wondering for anti joins, is there any other way to write them besides a NOT EXISTS with a correlated subquery?
Essentially what would be the most efficient way of writing an anti join? Or when Im trying to find all the rows that are NOT a common match between two tables.