Subquery hints affect whether to use semijoin transformations and which semijoin strategies to permit, and, when semijoins are not used, whether to use subquery materialization or IN-to-EXISTS transformations.

Syntax of hints that affect semijoin strategies:

hint_name([@query_block_name] [strategy [, strategy] ...])

The syntax refers to these terms:

• hint_name: These hint names are permitted:
• SEMIJOIN, NO_SEMIJOIN: Enable or disable the named semijoin strategies.

• strategy: A semijoin strategy to be enabled or disabled. These strategy names are permitted: DUPSWEEDOUT, FIRSTMATCH, LOOSESCAN, MATERIALIZATION.

For SEMIJOIN hints, if no strategies are named, semijoin is used if possible based on the strategies enabled according to the optimizer_switch system variable. If strategies are named but inapplicable for the statement, DUPSWEEDOUT is used.

For NO_SEMIJOIN hints, if no strategies are named, semijoin is not used. If strategies are named that rule out all applicable strategies for the statement, DUPSWEEDOUT is used.

See MySQL documentation for details.

Worklog #8244 may be also useful.