从两个表中检索数据，其中某些值为NULL

I need to retrieve student data who have posted a discussion in the forum as well as who never posted in a specific course I write this request:

我需要检索在论坛中发布讨论的学生数据，以及从未在特定课程中发布的我写这个请求的学生数据：

SELECT p.idp, p.age, p.speciality, f.categforum
FROM forum f
LEFT JOIN participant p
     ON f.idp=p.idp AND f.idc=4

but that return only list of student who have a post in a forum. my result should be like that:

但只返回在论坛中发帖的学生名单。我的结果应该是这样的：

+-----+-----+------------+------------+
| idp | age | speciality | categforum |
+-----+-----+------------+------------+
|  01 |  23 | computing  | NULL       |
|  02 |  25 | management | problem    |
|  03 |  35 | management | NULL       |
|  05 |  25 | computing  | social     |
+-----+-----+------------+------------+

.... I have added categforum is NULL but that isn't workable.

....我添加了Categforum是NULL但是这是行不通的。

2 个解决方案

#1

If I understand correctly, you want all participants. If so, that table should be the first table in the LEFT JOIN:

如果我理解正确，你想要所有参与者。如果是这样，那个表应该是LEFT JOIN中的第一个表：

SELECT p.idp, p.age, p.speciality, f.categforum
FROM participant p LEFT JOIN
     forum f 
     ON f.idp = p.idp AND f.idc = 4 ;

You could also use RIGHT JOIN. However, I almost never use RIGHT JOIN. I find that LEFT JOIN is much easier to follow -- at least for people who read from left-to-right. The idea of keeping all the rows in the first table is easier to follow than keeping all rows in the (yet-to-be-read) last table.

你也可以使用RIGHT JOIN。但是，我几乎从不使用RIGHT JOIN。我发现LEFT JOIN更容易理解 - 至少对于那些从左到右阅读的人来说。将所有行保留在第一个表中的想法比将所有行保留在（尚未读取）最后一个表中更容易理解。

#2

Try Right join instead.
Query:

请尝试右连接。查询：

SELECT p.idp, p.age, p.speciality, f.categforum 
FROM forum f 
RIGHT JOIN participant p ON f.idp=p.idp 
AND f.idc=4

#1