Last active
October 29, 2021 20:08
-
-
Save danhuynhdev/1dc89d7daa21092c3a6cea53fa5ed6f2 to your computer and use it in GitHub Desktop.
Mysql full join emulation
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment