# Subquery Optimization - Case Study 3 - `Apply` to `Join`

+ Similar to case study 2, the optimizer pushs down the Apply operator, and convert the Apply operator to a left join operator.

```
SELECT T1.col5 FROM T1 WHERE 100 < (~!@SELECT AVG(T2.col2) FROM T2 WHERE T2.col1 = T1.col1@!~);
```

![`smart`](Subquery07_aa_002_20250415.png)

```
SELECT TMP.col5, AVG(TMP.col2) FROM (~!@SELECT T1.col5 AS col5, T2.col2 as col2 FROM T1 LEFT JOIN T2 on T1.col1 = T2.col1@!~) TMP WHERE TMP.col2 > 100;
```

------------------------------------------------------------------------------
When a subquery involves an aggregate operation, such as in the following example:

```
SELECT T1.col5 FROM T1 WHERE 100 < (SELECT AVG(T2.col2) FROM T2 WHERE T2.col1 = T1.col1)
```

The `Apply` operator initially processes each row from the outer query `SELECT T1.col1, T1.col5 FROM T1`, passing it to the inner query `SELECT AVG(T2.col2) FROM T2 WHERE T2.col1 = T1.col1` to check if the result exceeds 100. If the condition is met, the result is returned.

Due to the inefficiency of this approach, the optimizer automatically makes the following improvements:
- Elevates the `Agg` operator and pushes down the `Apply` operator.
- Converts the `Apply` operator into a `left outer join` operator.

For example, it transforms the query into: 

```
SELECT TMP.col5, AVG(TMP.col2) 
FROM (SELECT T1.col5 AS col5, T2.col2 AS col2 FROM T1 LEFT JOIN T2 ON T1.col1 = T2.col1) TMP 
WHERE TMP.col2 > 100
```

This change effectively eliminates the `Apply` operator from the query.