The part inside the brackets in `AS ( … )`, i.e. the nested query itself:

```
               SELECT 2222222222
         UNION SELECT 2222222223
         UNION SELECT 2222222225
```

should remain the same when you turn it into a derived table:

```
SELECT *
     , lrn_2.lrn AS lrn2
     , lrn_0.lrn AS lrn0
     , COALESCE(lrn_2.lrn, lrn_0.lrn) AS lrn
  FROM (
               SELECT 2222222222
         UNION SELECT 2222222223
         UNION SELECT 2222222225) AS cte1(number)
  LEFT JOIN lrn_2
    ON lrn_2.number = cte1.number
  LEFT JOIN lrn_0
    ON substr(cte1.number, 1, 6) = lrn_0.number
;
```

Note that if the reason you are converting the CTE into a derived table is to be able to run the query in a pre-8.0 version, then the syntax above  will still fail. Before version 8.0 was released, MySQL did not support the `( … ) AS alias (column aliases)` syntax for derived tables. Only `( … ) AS alias` was supported, and column aliases had to be provided by the derived table itself, i.e. like this:

```
SELECT *
     , lrn_2.lrn AS lrn2
     , lrn_0.lrn AS lrn0
     , COALESCE(lrn_2.lrn, lrn_0.lrn) AS lrn
  FROM (
               SELECT 2222222222 AS number  /* <-- column alias */
         UNION SELECT 2222222223
         UNION SELECT 2222222225) AS cte1
  LEFT JOIN lrn_2
    ON lrn_2.number = cte1.number
  LEFT JOIN lrn_0
    ON substr(cte1.number, 1, 6) = lrn_0.number
;
```