https://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/

https://dev.mysql.com/doc/refman/8.0/en/semijoins.html

An antijoin is an operation that returns only rows for which there is no match. Since MySQL 8.0.17, the following subqueries are transformed into antijoins:

• NOT IN (SELECT ... FROM ...)
• NOT EXISTS (SELECT ... FROM ...)
• IN (SELECT ... FROM ...) IS NOT TRUE
• EXISTS (SELECT ... FROM ...) IS NOT TRUE
• IN (SELECT ... FROM ...) IS FALSE
• EXISTS (SELECT ... FROM ...) IS FALSE

In short, any negation of a subquery of the form IN (SELECT ... FROM ...) or EXISTS (SELECT ... FROM ...) is transformed into an antijoin.

Last updated 18 Dec 2019