Mysql full join emulation

monstrosity.sql

      SELECT
        T0.`cte1_full_join_cte2__cte1_b1` AS `cte1_b1`,
        T0.`cte1_full_join_cte2__count_of_b1` AS `count_of_b1`,
        T0.`cte1_full_join_cte2__a2` AS `a2`,
        T0.`cte1_full_join_cte2__cte2_b1` AS `cte2_b1`,
        T0.`cte1_full_join_cte2__b2` AS `b2`,
        T0.`table_c__c1` AS `c1`,
        T0.`table_c__c2` AS `c2`,
        T3.`d1` AS `d1`,
        T3.`d2` AS `d2`
      FROM
        (
          (
            SELECT
              T0.`cte1__cte1_b1` AS `cte1_full_join_cte2__cte1_b1`,
              T0.`cte1__count_of_b1` AS `cte1_full_join_cte2__count_of_b1`,
              T0.`cte1__a2` AS `cte1_full_join_cte2__a2`,
              T0.`cte2__cte2_b1` AS `cte1_full_join_cte2__cte2_b1`,
              T0.`cte2__b2` AS `cte1_full_join_cte2__b2`,
              T2.`c1` AS `table_c__c1`,
              T2.`c2` AS `table_c__c2`,
              T2.`c3` AS `table_c__c3`
            FROM
              (
                (
                  SELECT
                    T0.`b1` AS `cte1__cte1_b1`,
                    COUNT(T0.`b1`) AS `cte1__count_of_b1`,
                    T0.`a2` AS `cte1__a2`,
                    T1.`b1` AS `cte2__cte2_b1`,
                    T1.`b2` AS `cte2__b2`
                  FROM
                    (
                      SELECT
                        T0.`b1` AS `b1`,
                        T0.`a2` AS `a2`,
                        T0.`a3` AS `a3`
                      FROM
                        table_a T0
                    ) T0
                    LEFT JOIN (
                      SELECT
                        T0.`b1` AS `b1`,
                        T0.`b2` AS `b2`
                      FROM
                        table_b T0
                    ) T1 ON T0.`b1` = T1.`b1`
                  WHERE
                    T0.`a3` = 69
                )
                UNION ALL
                (
                  SELECT
                    T0.`b1` AS `cte1__cte1_b1`,
                    COUNT(T0.`b1`) AS `cte1__count_of_b1`,
                    T0.`a2` AS `cte1__a2`,
                    T1.`b1` AS `cte2__cte2_b1`,
                    T1.`b2` AS `cte2__b2`
                  FROM
                    (
                      SELECT
                        T0.`b1` AS `b1`,
                        T0.`a2` AS `a2`,
                        T0.`a3` AS `a3`
                      FROM
                        table_a T0
                    ) T0
                    RIGHT JOIN (
                      SELECT
                        T0.`b1` AS `b1`,
                        T0.`b2` AS `b2`
                      FROM
                        table_b T0
                    ) T1 ON T0.`b1` = T1.`b1`
                  WHERE
                    (T0.`a3` = 69) AND
                    (T0.`b1` IS NULL)
                )
              ) T0
              LEFT JOIN table_c T2 ON T0.`cte2__cte2_b1` = T2.`c1` AND T0.`cte2__b2` = T2.`c2`
            WHERE
              T2.`c4` > 96
          )
          UNION ALL
          (
            SELECT
              T0.`cte1__cte1_b1` AS `cte1_full_join_cte2__cte1_b1`,
              T0.`cte1__count_of_b1` AS `cte1_full_join_cte2__count_of_b1`,
              T0.`cte1__a2` AS `cte1_full_join_cte2__a2`,
              T0.`cte2__cte2_b1` AS `cte1_full_join_cte2__cte2_b1`,
              T0.`cte2__b2` AS `cte1_full_join_cte2__b2`,
              T2.`c1` AS `table_c__c1`,
              T2.`c2` AS `table_c__c2`,
              T2.`c3` AS `table_c__c3`
            FROM
              (
                (
                  SELECT
                    T0.`b1` AS `cte1__cte1_b1`,
                    COUNT(T0.`b1`) AS `cte1__count_of_b1`,
                    T0.`a2` AS `cte1__a2`,
                    T1.`b1` AS `cte2__cte2_b1`,
                    T1.`b2` AS `cte2__b2`
                  FROM
                    (
                      SELECT
                        T0.`b1` AS `b1`,
                        T0.`a2` AS `a2`,
                        T0.`a3` AS `a3`
                      FROM
                        table_a T0
                    ) T0
                    LEFT JOIN (
                      SELECT
                        T0.`b1` AS `b1`,
                        T0.`b2` AS `b2`
                      FROM
                        table_b T0
                    ) T1 ON T0.`b1` = T1.`b1`
                  WHERE
                    T0.`a3` = 69
                )
                UNION ALL
                (
                  SELECT
                    T0.`b1` AS `cte1__cte1_b1`,
                    COUNT(T0.`b1`) AS `cte1__count_of_b1`,
                    T0.`a2` AS `cte1__a2`,
                    T1.`b1` AS `cte2__cte2_b1`,
                    T1.`b2` AS `cte2__b2`
                  FROM
                    (
                      SELECT
                        T0.`b1` AS `b1`,
                        T0.`a2` AS `a2`,
                        T0.`a3` AS `a3`
                      FROM
                        table_a T0
                    ) T0
                    RIGHT JOIN (
                      SELECT
                        T0.`b1` AS `b1`,
                        T0.`b2` AS `b2`
                      FROM
                        table_b T0
                    ) T1 ON T0.`b1` = T1.`b1`
                  WHERE
                    (T0.`a3` = 69) AND
                    (T0.`b1` IS NULL)
                )
              ) T0
              RIGHT JOIN table_c T2 ON T0.`cte2__cte2_b1` = T2.`c1` AND T0.`cte2__b2` = T2.`c2`
            WHERE
              (T2.`c4` > 96) AND
              (T0.`cte2__cte2_b1` IS NULL) AND
              (T0.`cte2__b2` IS NULL)
          )
        ) T0
        LEFT JOIN table_d T3 ON T0.`table_c__c3` = T3.`d1`
      WHERE
        T3.`d3` < 96
      ORDER BY 4, T3.`f1` DESC

original.sql

SELECT
  T0.`b1` AS `cte1_b1`,
  COUNT(T0.`b1`) AS `count_of_b1`,
  T0.`a2` AS `a2`,
  T1.`b1` AS `cte2_b1`,
  T1.`b2` AS `b2`,
  T2.`c1` AS `c1`,
  T2.`c2` AS `c2`,
  T3.`d1` AS `d1`,
  T3.`d2` AS `d2`
FROM
  (
    SELECT
      T0.`b1` AS `b1`,
      T0.`a2` AS `a2`,
      T0.`a3` AS `a3`
    FROM
      table_a T0
  ) T0 
  FULL JOIN (
    SELECT
      T0.`b1` AS `b1`,
      T0.`b2` AS `b2`
    FROM
      table_b T0
  ) T1 ON T0.`b1` = T1.`b1` 
  FULL JOIN table_c T2 ON T1.`b1` = T2.`c1` AND T1.`b2` = T2.`c2`
  LEFT JOIN table_d T3 ON T2.`c3` = T3.`d1`
WHERE
  (T0.`a3` = 69)
  AND (T2.`c4` > 96)
  AND (T3.`d3` < 96)
ORDER BY
  4,
  T3.`f1` DESC