LEFT 和 RIGHT JOIN 多個表，顯示 mariadb 10.3 上的所有空數據

我想加入 3 個空值的表，

這裡我有 3 個表如下

tbl_reporting                               tbl_criteria                   tbl_student
|===============================|           |========================|     |===============|
| id | id_criteria | id_student |           | id |   name    |weight |     | id |   name   |
|===============================|           |========================|     |===============|
| 1  |     2       |      3     |           |  1 | worrying  |  3    |     | 1  | Nina     |
| 2  |     1       |      2     |           |  2 | naughty   |  2    |     | 2  | Adam     |
| 3  |     1       |      1     |           |  3 | usually   |  2    |     | 3  | Dodi     |
| 4  |     2       |      2     |           |  4 | good      |  1    |     | 4  | Zarah    |
| 5  |     1       |      1     |           |  5 | obey      |  1    |     | 5  | Udep     |

和我想要的結果，像這樣

result
| id | student_name   | criteria_name | weight |
|==============================================|
| 1  | Dodi           |    naughty    |   2    |
| 2  | Adam           |    worrying   |   3    |
| 3  | Nina           |    worrying   |   3    |
| 4  | Adam           |    naughty    |   2    |
| 5  | Nina           |    worrying   |   3    |
|NULL| Zarah          |      NULL     |   0    |    
|NULL| Udep           |      NULL     |   0    |    
|NULL| NULL           |     usualy    |   0    |    
|NULL| NULL           |      good     |   0    |    
|NULL| NULL           |      obey     |   0    |

我嘗試自己..但不是我所期望的。我在這裡編碼自我：連結

謝謝

SELECT r.id, s.name student_name, c.name criteria_name, c.weight
FROM tbl_reporting r
JOIN tbl_student s ON r.id_student = s.id
JOIN tbl_criteria c ON r.id_criteria = c.id
UNION ALL
SELECT NULL, s.name, NULL, 0
FROM tbl_student s
WHERE NOT EXISTS ( SELECT NULL
                  FROM tbl_reporting r
                  WHERE r.id_student = s.id )

UNION ALL
SELECT NULL, NULL, c.name, 0
FROM tbl_criteria c
WHERE NOT EXISTS ( SELECT NULL
                  FROM tbl_reporting r
                  WHERE r.id_criteria = c.id );

https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=989338bb30f2f5b4d7be4667763e9522

引用自：https://dba.stackexchange.com/questions/287318