mysql - 如果mysql中不存在，则设置另一个值

SELECT subjects.name as name , grades.grade as grade,  concat(instructors.lastname, ", ", instructors.firstname , " ", left(instructors.middlename, 1),".") as instructor
    FROM subjects
    JOIN grades
    JOIN instructors
    WHERE grades.student_id = 1 AND subjects.id = grades.subject_id AND instructors.subject_id = subjects.id

结果是

English    Instructor    Grade
Math       Instructor    Grade

但是我想要的是给还没有辅导员的科目提供“ N / A”。例

English    Instructor    Grade
Math       Instructor    Grade
Science    N/A           Grade
History    N/A           Grade

我将如何在mySQL中设置条件，以便结果在上面的示例中准确显示？

提前致谢

最佳答案

尝试这个 ：：

使用左联接和ifnull

SELECT subjects.name as name , grades.grade as grade,  ifnull(concat(instructors.lastname, ", ", instructors.firstname , " ", left(instructors.middlename, 1),"."),'N/A)' as instructor
    FROM subjects
    LEFT JOIN grades on subjects.id = grades.subject_id
    LEFT JOIN instructors on instructors.subject_id = subjects.id
    WHERE grades.student_id = 1