MySQL 如何使用 SQL Left Join 返回空值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13794782/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to return null values using SQL Left Join
提问by Ruben_PH
Please look at this SQL Fiddle for the tables and what query I have tried:
SQL Fiddle
请查看此 SQL Fiddle 中的表以及我尝试过的查询：
SQL Fiddle
So basically, I have two tables namely tbl_curriculumand tbl_enrolled_subjects.
所以基本上，我有两个表，即tbl_curriculum和tbl_enrolled_subjects。
tbl_curriculumcontains all the subjects (subject_id) a student should take based on his course (course_id).
tbl_enrolled_subjectscontains all the subjects the student has taken/enrolled based on tbl_curriculum.
tbl_curriculum包含学生应根据其课程 (course_id) 参加的所有科目 (subject_id)。
tbl_enrolled_subjects包含学生根据tbl_curriculum参加/注册的所有科目。
I want to check which subjects the student has taken and which is not, the query should return something like this:
我想检查学生选择了哪些科目，哪些没有，查询应该返回如下内容：
Subject_id|Grade|Status
23 | 2 |Passed
24 | 2 |Passed
31 | 2 |Passed
50 | 2 |Passed
83 | 1 |Passed
27 |NULL |NULL
28 |NULL |NULL
29 |NULL |NULL
. . . And So On.
. . . 等等。
Subject_ID with Grade and Status mean the student has already taken the subject. On the otherhand, NULL values indicates the the student has not taken those subjects yet.
I used this query:
带有成绩和状态的 Subject_ID 表示学生已经选修了该科目。另一方面，NULL 值表示学生尚未选修这些科目。
我使用了这个查询：
SELECT a.subject_id, b.grade, b.status
FROM tbl_curriculum a
LEFT JOIN tbl_enrolled_subjects b
ON a.course_id = b.course_id AND a.subject_id = b.subject_id
WHERE b.student_id_no='05-0531';
But I keep getting only the subjects the student has taken.
但我一直只得到学生选修过的科目。
Subject_id|Grade|Status
23 | 2 |Passed
24 | 2 |Passed
31 | 2 |Passed
50 | 2 |Passed
83 | 1 |Passed
Am I missing something? Thanks in advance.
我错过了什么吗？提前致谢。
采纳答案by purgatory101
The reason you are not finding any nulls is because your where-clause is searching for rows with a student_id_no filled out. But, with the data you have the student_id_no will also be null in the case where the student has not taken the class...thus you are filtering out those.
您没有找到任何空值的原因是因为您的 where 子句正在搜索填写了 student_id_no 的行。但是，对于您拥有的数据，如果学生没有上课，student_id_no 也将为空……因此您将过滤掉这些数据。
Try this:
尝试这个：
SELECT a.subject_id, b.grade, b.status, b.student_id_no
FROM tbl_curriculum a
LEFT JOIN tbl_enrolled_subjects b
ON a.course_id = b.course_id AND a.subject_id = b.subject_id
where student_id_no is null or student_id_no = '05-0531'
order by subject_id
回答by Gabriel
Is very simple, you use IS EMPTY
很简单，你用 IS EMPTY
In your entity you have defined a inverse key, then you call your entity principal where inverse Key IS EMPTY.
在您的实体中，您定义了一个反向密钥，然后在反向密钥为空的地方调用您的实体主体。
SELECT a FROM tbl_curriculum a WHERE a.enrollers IS EMPTY;
Then I have a field curriculum defined like tbl_enrolled_subjectsin the tbl_curriculum
然后，我有这样定义的字段课程tbl_enrolled_subjects中tbl_curriculum
/**
*
* @ORM\OneToMany(targetEntity="tbl_enrolled_subjects", mappedBy="id")
*/
private $enrollers;
回答by davidp_1978
I believe putting the student filter into the JOIN predicate also gives the desired results. It works in the SQL Fiddle but I don't know how it affects the query plan.
我相信将 student 过滤器放入 JOIN 谓词也能得到想要的结果。它适用于 SQL Fiddle，但我不知道它如何影响查询计划。
SELECT a.subject_id, b.grade, b.status
FROM tbl_curriculum a
LEFT JOIN tbl_enrolled_subjects b
ON a.course_id = b.course_id AND a.subject_id = b.subject_id
**AND** b.student_id_no='05-0531';
回答by Ajith Sasidharan
try this query ::
试试这个查询::
SELECT a.subject_id, b.grade, b.status
FROM tbl_curriculum a
JOIN tbl_enrolled_subjects b
ON a.course_id = b.course_id AND a.subject_id = b.subject_id
WHERE b.student_id_no='05-0531'
union
select subject_id,null,null
from tbl_curriculum
where concat(subject_id,course_id) not in (
select concat(subject_id,course_id)
from tbl_enrolled_subjects
WHERE student_id_no='05-0531')
