我很抱歉我的头衔不好，因为我很难选择冠军头衔 . 但我希望描述可以解释它 . 我想从关系表中选择具有两个表的外键的值虽然关系表的数据为空，所以有我的表
我有三张 table ：
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
1 回答
使用
CROSS JOIN