mysql - 从两个表中有两个外键的多对多关系表中选择行

Question

我为我的错误标题感到抱歉，因为我混淆了选择标题。但我希望描述能够解释清楚。尽管关系表的数据为空，但我想从具有两个表的外键的关系表中选择值，所以有我的表

我有三个表:

Student
==========
id || name
===============
1  || Rooney
2  || Carrick
3  || Smalling
4  || De Gea

然后:

Item
==========
id || Title
===============
1  || Pre-Test
2  || Post-Test
3  || Final-Test

然后是多对多关系的表

Score
==========
id || student_id || item_id || Score
=====================================
1  || 1          || 1       || 100
2  || 1          || 2       || 80
3  || 2          || 1       || 90
4  || 2          || 3       || 85
5  || 3          || 2       || 80
6  || 3          || 3       || 90
7  || 4          || 1       || 95

我想得到这样的结果:

Result
==========
score_id || student_name || item_name || Score
================================================
1        || Rooney       || Pre-Test  || 100
2        || Rooney       || Post-Test || 80
NULL     || Rooney       || Final-Test|| NULL
3        || Carrick      || Pre-Test  || 90
NULL     || Carrick      || Post-Test || NULL
4        || Carrick      || Final-Test|| 85
NULL     || Smalling     || Pre-Test  || NULL
5        || Smalling     || Post-Test || 80
6        || Smalling     || Final-Test|| 90
7        || De Gea       || Pre-Test  || 95
NULL     || De Gea       || Post-Test || NULL
NULL     || De Gea       || Final-Test|| NULL 

我已经从另一个论坛中搜索了一些关于外连接的教程，并在我的数据库中进行了尝试，但查询没有显示空值。谢谢，

*)已编辑

我已经尝试使用 union 和 join 进行查询，但结果没有显示空值

SELECT score.id AS score_id, student.name, item.title, score.score
FROM student
RIGHT JOIN score ON student.id = score.student_id
RIGHT JOIN item ON score.item_id = item.id
UNION 
SELECT score.id AS score_id, student.name, item.title, score.score
FROM item
RIGHT JOIN score ON score.item_id = item.id
RIGHT JOIN student ON score.student_id = student.id
ORDER BY score_id
LIMIT 0 , 30

和

SELECT score.id AS score_id, student.name AS student_name, item.title AS item_title, score.score
FROM student
LEFT JOIN score ON student.id = score.student_id
LEFT JOIN item ON score.item_id = item.id
LIMIT 0 , 30

改成右连接还是没有显示空值，结果如下:

score_id || student_name || item_title || score
1        || Rooney       || Pre-Test   || 100
2        || Rooney       || Post-Test  || 80
3        || Carrick      || Pre-Test   || 90
4        || Carrick      || Final-Test || 85
5        || Smalling     || Post-Test  || 80
7        || Smalling     || Final-Test || 90
8        || De Gea       || Pre-Test   || 95

Answer 1

使用交叉连接

SELECT 
    sc.id AS score_id, 
    s.name AS student_name, 
    i.title AS item_name, 
    sc.score 
FROM student s
CROSS JOIN item i
LEFT JOIN score sc ON sc.item_id = i.id AND sc.student_id = s.id;