# Subquery Optimization - Transform `Apply` to `Join`

+ `Apply` operator processes each row from outside query and feeds into the subquery, which is inefficient.
+ TiDB optimizer converts the `Apply` operator into a `Semi Join` operator
+ `Semi Join` only determines whether there is at least one matching row between two tables, without seeking to identify all matching records.

```
SELECT * FROM T1 WHERE ~!@EXISTS (SELECT T2.col1 FROM T2 WHERE T2.col1 = T1.col1)@!~;
```

![`smart`](Subquery02_aa_001_20240220.png)

------------------------------------------------------------------------------
As previously mentioned, for the existential testing subquery, the `Apply` operator processes each row from `SELECT name, street, city FROM users WHERE state = 'New Mexico'` by feeding it into the subquery `SELECT userID FROM allowlist WHERE allowlist.userID = users.userID` to determine if a match exists. This method is inefficient.

Typically, the TiDB optimizer converts the `Apply` operator into a `Semi Join` operator, as illustrated in the slides.

The primary distinction between a `Join` and a `Semi Join` is that a `Semi Join` only determines whether there is at least one matching row between two tables, without seeking to identify all matching records.

In the example above, we are only concerned with whether a match exists: `EXISTS (SELECT T2.col1 FROM T2 WHERE T2.col1 = T1.col1)`. However, for a query like `SELECT * FROM T1 INNER JOIN T2 WHERE T1.col1 = T2.col2`, the goal is to return all records that match between the two tables.