MySQL 选择连接空值

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             |
--------------------------------------------------

如果“john”中的“cs”和“ss”为 NULL，如何打印 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'