MySQL选择连接空值
问题描述:
嗨我试图查询是要显示每个4表中的4个值。MySQL选择连接空值
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' 和 'SS' 在 “约翰” 是NULL?
答
使用COALESCE()
像
select coalesce(i.name,'NULL') as iName, ...
from instructor i
根据您的评论功能看起来你不想有从department
表输出的name
列在所有。在这种情况下,只需选择NULL
它喜欢
select i.name, c.name, s.name, null
(OR)
select i.name, c.name, s.name, 'null'
谢谢你让我知道该功能。我试图隐藏主要(CS或SS如果NULL)。我尝试从教师i'选择i.name,c.name,s.name,coalesce(d.name,'NULL') ,没有工作。仍然在主要列中获得'cs'和'ss':( – Gomtting
@Gomtting,请参阅编辑答案,如果有帮助 – Rahul
哦,对不起,我想我没有意识到我从上面选择的名称..我编辑表我需要主要的名字,即使它是NULL,就像'cs/cs/ss/ss'到'cs/NULL/NULL/ss'。 – Gomtting