MySQL HeatWave User Guide
Antijoins, with the exception of supported
IN and EXISTS
antijoin variants listed below.
EXISTS semijoins and antijoins are
supported in the following variants only:
SELECT ... WHERE ... EXISTS (...)
SELECT ... WHERE ... EXISTS (...) IS
TRUE
SELECT ... WHERE ... EXISTS (...) IS NOT
FALSE
SELECT ... WHERE ... NOT EXISTS (...) IS
FALSE
SELECT ... WHERE ... NOT EXISTS (...) IS NOT
TRUE
Depending on transformations and optimizations performed
by MySQL, other variants of EXISTS
semijoins may or may not be offloaded.
IN semijoins and antijoins other than
the following variants:
SELECT ... WHERE ... IN (...)
SELECT ... WHERE ... IN (...) IS
TRUE
SELECT ... WHERE ... NOT IN (...) IS
FALSE
Depending on transformations and optimizations performed
by MySQL, other variants of IN
semijoins may or may not be offloaded.
A query with a supported semijoin or antijoin condition may be rejected for offload due to how MySQL optimizes and transforms the query.
Semijoin and antijoin queries use the best plan found after evaluating the first 10000 possible plans, or after investigating 10000 possible plans since the last valid plan.
The plan evaluation count is reset to zero after each
derived table, after an outer query, and after each
subquery. The plan evaluation limit is required because
the DUPSWEEDOUT join strategy, which is
not supported by MySQL HeatWave, may be used as a fallback
strategy by MySQL during join order optimization (for
related information, see
FIRSTMATCH). The
plan evaluation limit prevents too much time being spent
evaluating plans in cases where MySQL generates numerous
plans that use the DUPSWEEDOUT semijoin
strategy.
Outer join queries without an equality condition defined for the two tables.
Some outer join queries with IN ...
EXISTS sub-queries (semi-joins) in the
ON clause.