# Subquery Optimization - The `Apply` Operator

+ `Apply` operator uses each row from `SELECT name, street, city FROM users WHERE state = 'New Mexico'`
+ Then, it inputs it into the subquery `SELECT userID FROM allowlist WHERE allowlist.userID = users.userID` to determine whether the condition is satisfied.


```
SELECT name, street, city FROM users WHERE state = 'New Mexico' AND userID ~!@IN (SELECT userID FROM allowlist WHERE allowlist.userID = users.userID)@!~;
```

```
SELECT ~!@userID@!~, name, street, city FROM users WHERE state = 'New Mexico';
```

```
~!@SELECT userID FROM allowlist WHERE allowlist.userID = users.userID@!~;
```

![`smart`](Subquery01_aa_001_20240220.png)

------------------------------------------------------------------------------
In the case of a correlated subquery, the simplest execution method involves using each row from the outer query as a parameter for the inner subquery and then returning the result of this inner query. For instance, consider the following example:

```sql
SELECT name, street, city 
FROM users 
WHERE state = 'New Mexico' 
AND userID IN (
    SELECT userID 
    FROM allowlist 
    WHERE allowlist.userID = users.userID
);
```

In this example, for each row retrieved by the outer query, `SELECT name, street, city FROM users WHERE state = 'New Mexico'`, the `userID` is passed into the inner subquery, `SELECT userID FROM allowlist WHERE allowlist.userID = users.userID`, to check if a value exists. If a match is found, the row from the outer query is included in the result; otherwise, it is not returned.

To manage this process, the TiDB database introduces an `Apply` operator. This operator processes each row `r` from the input set `R`, typically the outer table, by applying it to the expression `E(r)`, where `E` represents the inner subquery operation, and then evaluates the result of `E(r)`.

In the aforementioned example, the `Apply` operator uses each row from `SELECT name, street, city FROM users WHERE state = 'New Mexico'` and inputs it into the subquery `SELECT userID FROM allowlist WHERE allowlist.userID = users.userID` to determine whether the condition is satisfied.

However, since the inner query must be executed for every row in the outer query, the performance of the `Apply` operator is often not optimal. Thus, the optimizer typically employs a technique called de-correlation to enhance efficiency, which involves converting the `Apply` operator into a `Join` operator. In the following section, we will explore more common de-correlation techniques.