# Subqueries with IN and NOT IN

The `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.

While `IN` is logically related to the `ANY` and `SOME` operators, it has distinct semantics and receives specialized treatment from the query optimizer.

## Syntax

```sql
scalar_expression [NOT] IN (subquery)
```

* `scalar_expression`: An expression that evaluates to a single value.
* `subquery`: A subquery that returns a single column.

## Relationship to ANY and SOME

In MariaDB, `IN` is a synonym for `= ANY`.

* `IN` evaluates to `TRUE` if the comparison returns `TRUE` for at least one row produced by the subquery.
* `IN` evaluates to `FALSE` if the subquery returns no rows.

Despite 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`.

## Handling NULL Values

The behavior of `IN` and `NOT IN` follows SQL three-valued logic.

* Source Expression is NULL: If the `scalar_expression` is `NULL`, the result is `NULL`.
* Subquery Contains NULL (IN): If no match is found and the subquery contains at least one `NULL`, the result is `NULL`.
* 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.

{% hint style="warning" %}
Avoid using `NOT IN` if the subquery columns can contain `NULL` values. In such cases, `NOT EXISTS` is generally safer and more predictable.
{% endhint %}

### NOT IN vs. <> ANY

`NOT IN` and `<> ANY` are not synonyms and represent different logical tests:

| OperatorText | Logical MeaningText                                         | ResultText                                                                               |
| ------------ | ----------------------------------------------------------- | ---------------------------------------------------------------------------------------- |
| `NOT IN`     | "Is this value absent from the entire set?"                 | Returns `TRUE` only if the value matches no rows in the subquery.                        |
| `<> 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. |

## Optimization and Performance

MariaDB treats `IN` subqueries as high-priority candidates for specific optimizations, including:

* Semi-join transformations: Converting the subquery into a join to improve execution speed.
* Subquery materialization: Storing the subquery result in a temporary internal table to avoid repeated execution.
* IN-to-EXISTS rewrites: Converting the predicate based on cost estimates.

## Examples

The following examples use table `sq1` (values: `50`, `100`) and table `sq2` (values: `40`, `50`, `120`).

### Using IN

```sql
SELECT * FROM sq1 WHERE num1 IN (SELECT num2 FROM sq2);
```

Result: `50` (Because `50` exists in both tables).

### Using NOT IN

```sql
SELECT * FROM sq1 WHERE num1 NOT IN (SELECT num2 FROM sq2);
```

Result: `100` (Because `100` is not present in `sq2`).

### NOT IN with NULL Pitfall

```sql
-- sq1 contains 50, 100
SELECT * FROM sq1 WHERE num1 NOT IN (SELECT NULL);
```

Result: Empty set (The condition evaluates to `NULL` for all rows, so no rows are returned).


---

# 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/reference/sql-statements/data-manipulation/selecting-data/subqueries/subqueries-with-in-and-not-in.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.