How to emulate a dependent CTE-CodePudding

Following up from this question Possible to emulate a basic CTE in MySQL by in-lining it, would it be possible to in-line the following query in mysql5.7 without the usage of CTEs?

WITH tbl1 AS (
    SELECT 1 AS a
), tbl2 AS (
    SELECT 1 AS a UNION ALL SELECT 2
), tbl3 AS (
    SELECT * FROM tbl1 JOIN tbl2 USING (a)
) SELECT * FROM tbl3, tbl2

If so, how could that be done?

CodePudding user response：

There's no particular problem with doing that, except that you have to repeat tbl2:

SELECT *
FROM (
    SELECT *
    FROM (
        SELECT 1 AS a
    ) tbl1
    JOIN (
        SELECT 1 AS a UNION ALL SELECT 2
    ) tbl2
    USING (a)
) tbl3
CROSS JOIN
(
    SELECT 1 AS a UNION ALL SELECT 2
) tbl2

fiddle