Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions The same duplicate-free result can be obtained by using a subquery: Here, the optimizer can recognize that the  IN  clause requires the subquery to return only one instance of each class number from the  roster  table. In this case, the query can use a  semijoin ; that is, an operation that returns only one instance of each row in  class  that is matched by rows in  roster . The following statement, which contains an  EXISTS  subquery predicate, is equivalent to the previous statement containing an  IN  subquery predicate: SELECT class_num, class_name FROM class WHERE class_num IN (SELECT class_num FROM roster); SELECT class_num, class_name FROM class WHERE EXISTS (SELECT * FROM roster WHERE class.class_num = roster.class_num); In MySQL 8.0.16 and later, any statement with an  EXISTS  subquery predicate is subject to the same semijoin transforms as a statement with an equivalent  IN  subquery predicate. Beginning with 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. An antijoin is an operation that returns only rows for which there is no match. Consider the query shown here: SELECT class_num, class_name FROM class WHERE class_num NOT IN (SELECT class_num FROM roster); This query is rewritten internally as the antijoin  SELECT class_num, class_name FROM class ANTIJOIN roster ON class_num , which returns one instance of each row in  class  that is  not matched by any rows in  roster . This means that, for each row in  class , as soon as a match is found in roster , the row in  class  can be discarded. Antijoin transformations cannot in most cases be applied if the expressions being compared are nullable. An exception to this rule is that  (... NOT IN (SELECT ...)) IS NOT FALSE  and its equivalent (... IN (SELECT ...)) IS NOT TRUE  can be transformed into antijoins. Outer join and inner join syntax is permitted in the outer query specification, and table references may be base tables, derived tables, view references, or common table expressions. In MySQL, a subquery must satisfy these criteria to be handled as a semijoin (or, in MySQL 8.0.17 and later, an antijoin if  NOT  modifies the subquery): 1695