mySQL选择加入null值

Question

嗨，我正在尝试查询每张4个表中的4个值。

select i.name, c.name, s.name, d.name
from instructor i
join course c
    on i.pid = c.instructor
join course_taken ct
    on c.id = ct.cid
join student s
    on s.id = ct.sid
join department d
    on s.major = d.id or s.major is null
where i.name = 'lee';

除空部分外，一切都很好。

表结构

--------------------------------------------------
| student | course  | instructor | department    |
--------------------------------------------------
| name   | name     | id         | id            |
--------------------------------------------------
| id     | id       | name       | name          |
--------------------------------------------------
| major  |instructor| department |               |
--------------------------------------------------

结果

--------------------------------------------------
| i.name    | c.name  | s.name  | d.name         |
--------------------------------------------------
| kim       | math    | jack    | cs             |
--------------------------------------------------
| kim       | math    | john    | cs             |  --> THIS VALUE IS
--------------------------------------------------
| kim       | math    | john    | ss             |  --> NULL AND SHOULD BE PRINTED IN NULL
--------------------------------------------------
| kim       |math     | json    | ss             |
--------------------------------------------------

如果" cs"one_answers" ss"在"约翰"中，我该如何打印null？

Answer 1

使用 COALESCE()功能

select coalesce(i.name,'NULL') as iName, ...
from instructor i

根据您的评论，您似乎根本不想从department表中输出name列。在这种情况下，只需选择 NULL，例如

select i.name, c.name, s.name, null

(或(

select i.name, c.name, s.name, 'null'