# EXISTS-to-IN Optimization

MySQL (including MySQL 5.6) has only one execution strategy for EXISTS subqueries. The strategy is essentially the straightforward, "naive" execution, without any rewrites.

[MariaDB 5.3](/docs/release-notes/community-server/old-releases/5.3/changes-improvements-in-mariadb-5-3.md) introduced a rich set of optimizations for IN subqueries. Since then, it makes sense to convert an EXISTS subquery into an IN so that the new optimizations can be used.

`EXISTS` will be converted into `IN` in two cases:

1. Trivially correlated EXISTS subqueries
2. Semi-join EXISTS

We will now describe these two cases in detail

## Trivially-correlated EXISTS subqueries

Often, EXISTS subquery is correlated, but the correlation is trivial. The subquery has form

```sql
EXISTS (SELECT ...  FROM ... WHERE outer_col= inner_col AND inner_where)
```

and "outer\_col" is the only place where the subquery refers to outside fields.\
In this case, the subquery can be re-written into uncorrelated IN:

```sql
outer_col IN (SELECT inner_col FROM ... WHERE inner_where)
```

(`NULL` values require some special handling, see below). For uncorrelated IN subqueries, MariaDB is able a cost-based choice between two execution strategies:

* [IN-to-EXISTS](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/subquery-optimizations/non-semi-join-subquery-optimizations.md#the-in-to-exists-transformation) (basically, convert back into EXISTS)
* [Materialization](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/subquery-optimizations/non-semi-join-subquery-optimizations.md#materialization-for-non-correlated-in-subqueries)

That is, converting trivially-correlated `EXISTS` into uncorrelated `IN` gives query optimizer an option to use Materialization strategy for the subquery.

Currently, EXISTS->IN conversion works only for subqueries that are at top level of the WHERE clause, or are under NOT operation which is directly at top level of the WHERE clause.

## Semi-join EXISTS subqueries

If `EXISTS` subquery is an AND-part of the `WHERE` clause:

```sql
SELECT ... FROM outer_tables WHERE EXISTS (SELECT ...) AND ...
```

then it satisfies the main property of [semi-join subqueries](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/subquery-optimizations/semi-join-subquery-optimizations.md):

*with semi-join subquery, we're only interested in records of outer\_tables that have matches in the subquery*

Semi-join optimizer offers a rich set of execution strategies for both correlated and uncorrelated subqueries. The set includes FirstMatch strategy which is an equivalent of how EXISTS suqueries are executed, so we do not lose any opportunities when converting an EXISTS subquery into a semi-join.

In theory, it makes sense to convert all kinds of EXISTS subqueries: convert both correlated and uncorrelated ones, convert irrespectively of whether the subquery has inner=outer equality.

In practice, the subquery will be converted only if it has inner=outer equality. Both correlated and uncorrelated subqueries are converted.

## Handling of NULL values

TODO: rephrase this:

* IN has complicated NULL-semantics. NOT EXISTS doesn't.
* EXISTS-to-IN adds IS NOT NULL before the subquery predicate, when required

## Control

The optimization is controlled by the `exists_to_in` flag in [optimizer\_switch](/docs/server/server-management/variables-and-modes/server-system-variables.md#optimizer_switch). Before [MariaDB 10.0.12](/docs/release-notes/community-server/old-releases/10.0/10.0.12.md), the optimization was OFF by default. Since [MariaDB 10.0.12](/docs/release-notes/community-server/old-releases/10.0/10.0.12.md), it has been ON by default.

## Limitations

EXISTS-to-IN doesn't handle

* subqueries that have GROUP BY, aggregate functions, or HAVING clause
* subqueries are UNIONs
* a number of degenerate edge cases

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/subquery-optimizations/exists-to-in-optimization.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.