Description\n-----------\n\nThe IN operator tests whether a value is a member of a set of values returned by a subquery. It is the most commonly used construct for equality-based subquery comparisons in MariaDB.\n\nWhile IN is logically related to the ANY and SOME operators, it has distinct semantics and receives specialized treatment from the query optimizer.\n\nSyntax\n\n``sql\nscalar_expression [NOT] IN (subquery)\n`\n\n scalar_expression: An expression that evaluates to a single value.\n subquery: A subquery that returns a single column.\n\nRelationship to ANY and SOME\n\nIn MariaDB, IN is a synonym for = ANY.\n\n IN evaluates to TRUE if the comparison returns TRUE for at least one row produced by the subquery.\n IN evaluates to FALSE if the subquery returns no rows.\n\nDespite this logical equivalence, the optimizer applies additional transformations to IN subqueries (such as semi-joins) that do not always apply to other quantitative ANY comparisons like > ANY or <= SOME.\n\nHandling NULL Values\n\nThe behavior of IN and NOT IN follows SQL three-valued logic.\n\n Source Expression is NULL: If the scalar_expression is NULL, the result is NULL.\n Subquery Contains NULL (IN): If no match is found and the subquery contains at least one NULL, the result is NULL.\n Subquery Contains NULL (NOT IN): If the subquery returns any NULL value, NOT IN will always evaluate to NULL or FALSE, never TRUE, even if no matching non-null value exists.\n\nAvoid using NOT IN if the subquery columns can contain NULL values. In such cases, NOT EXISTS is generally safer and more predictable.\n\nNOT IN vs. <> ANY\n\nNOT IN and <> ANY are not synonyms and represent different logical tests:\n\n| OperatorText | Logical MeaningText                                         | ResultText                                                                               |\n| ------------ | ----------------------------------------------------------- | ---------------------------------------------------------------------------------------- |\n| NOT IN     | "Is this value absent from the entire set?"                 | Returns TRUE only if the value matches no rows in the subquery.                        |\n| <> ANY     | "Is there at least one value in the set that is different?" | Returns TRUE if there is at least one row in the subquery that differs from the value. |\n\nOptimization and Performance\n\nMariaDB treats IN` subqueries as high-priority candidates for specific optimizations, including:\n\n Semi-join transformations: Converting the subquery into a join to improve execution speed.\n Subquery materialization: Storing the subquery result in a temporary internal table to avoid repeated execution.\n IN-to-EXISTS rewrites: Converting the predicate based on cost estimates.\n\nExamples\n--------\n\nSELECT * FROM sq1 WHERE num1 IN (SELECT num2 FROM sq2);\n\nURL: https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/subqueries/subqueries-with-in-and-not-in