Anonymous 1440
<>https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=17192c2ea375081ce103d558353b6d4e&hide=255
Top Answer
Mikael Eriksson
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
;
```