# Subquery Optimization - Case Study 2 - `Apply` to `Left Join`

+ When the subquery appears in the SELECT clause, the optimizer may push down the Apply operator and transforms it into left joins. 

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

![`smart`](Subquery06_aa_002_20250415.png)

```
SELECT T1.col5, T2.col2 FROM T1 LEFT JOIN T2 on T1.col1 = T2.col1
```
------------------------------------------------------------------------------
When a subquery appears in the `SELECT` clause, as demonstrated in the example below:
```
SELECT T1.col5, (SELECT T2.col2 FROM T2 WHERE T2.col1 = T1.col1) FROM T1
```

The optimizer optimizes performance by converting the `Apply` operator into left joins. Initially, the `Apply` operator processes the outer query `SELECT T1.col5, T1.col1 FROM T1` and matches each row with the inner subquery `SELECT T2.col2 FROM T2 WHERE T2.col1 = T1.col1`, but this approach is inefficient.

The optimizer enhances performance by restructuring the query to join the `T1` and `T2` tables first, and then projecting the necessary columns, like so: `SELECT T1.col5, T2.col2 FROM T1 LEFT JOIN T2 on T1.col1 = T2.col1`. This transformation turns the subquery into a left join.

It's important to note that, given the subquery is scalar-valued rather than an existential testing subquery, it is converted into a `left join` rather than a `semi join`.