我很抱歉我的标题不好，因为我不知道该怎么选。但我希望描述能解释清楚。虽然关系表的数据为空，但我想从两个表都有外键的关系表中选择值，所以有我的表
我有三张桌子：
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
更改为right join仍然没有显示空值，结果如下：
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
最佳答案
使用CROSS JOIN
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;