php - MysQL : How to set default value for empty row?

Question

我有 4 张 table 。

1. Course 2. Semester 3. AssignTeacher 4. Teacher.

 Course has id, name, code.
 Semester has id, name, course_id.
 AssignTeacher has id,course_id,teacher_id.
 Teacher has id, name.

现在我想找到 courses.code、courses.name、semesters.name 和 teachers.name从上表。其中 teachers.name 应该与那些在 AssignTeacher 表中具有 id 的教师一起出现。现在在类(class)表中，当该类(class)没有教师 ID 而不是显示空值时，我可以在查询中设置一些默认值，如“无”吗？

这是我到目前为止写的查询，虽然我没有得到我期望的结果:

SELECT courses.code, courses.name, semesters.name,teachers.name 
FROM semesters 
JOIN courses on courses.semester_id = semesters.id 
JOIN assign_teachers on assign_teachers.course_id=courses.id
JOIN teachers on assign_teachers.id=teachers.id

Answer 1

当然，您应该使用 COALESCE() 将 null 替换为想要的值:

SELECT courses.code, courses.name, semesters.name,COALESCE(teachers.name,'None') as teacher_name
from semesters join courses on  courses.semester_id = semesters.id 
  join assign_teachers on assign_teachers.course_id=courses.id
   join teachers on assign_teachers.id=teachers.id

但是当没有 teacher.id 时，你得到空值似乎很奇怪，因为你使用的是普通连接，所以应该过滤这条记录。我认为你应该改用 LEFT join :

SELECT courses.code, courses.name, semesters.name,COALESCE(teachers.name,'None') as teacher_name
from semesters join courses on  courses.semester_id = semesters.id 
  left join assign_teachers on assign_teachers.course_id=courses.id
   left join teachers on assign_teachers.id=teachers.id