SQL左连接学生与论文表后，如何将NULL替换为MISSING和0？

表结构与插入数据 #

现有两张关联表：

• Students表：字段Id（自增主键）、First_name
• Papers表：字段Title、Grade、Student_id（关联Students.Id的外键）

插入数据的SQL语句：

INSERT INTO students (first_name) VALUES
('Caleb'), ('Samantha'), ('Raj'), ('Carlos'), ('Lisa');
INSERT INTO papers (student_id, title, grade ) VALUES
(1, 'My First Book Report', 60),
(1, 'My Second Book Report', 75),
(2, 'Russian Lit Through The Ages', 94),
(2, 'De Montaigne and The Art of The Essay', 98),
(4, 'Borges and Magical Realism', 89); 

原查询问题 #

原左连接查询语句会返回包含NULL的结果：

SELECT a.Id, a.first_name, b.title, b.grade
FROM students a LEFT JOIN papers b
ON a.id = b.student_id;

修改后的查询语句 #

使用COALESCE()函数可将NULL值替换为指定内容，该函数返回参数列表中第一个非NULL的值，适配多数SQL数据库：

SELECT 
    a.first_name AS `First name`,
    COALESCE(b.title, 'MISSING') AS Title,
    COALESCE(b.grade, 0) AS Grade
FROM students a LEFT JOIN papers b
ON a.id = b.student_id;

若使用MySQL，也可替换为IFNULL()函数实现相同效果：

SELECT 
    a.first_name AS `First name`,
    IFNULL(b.title, 'MISSING') AS Title,
    IFNULL(b.grade, 0) AS Grade
FROM students a LEFT JOIN papers b
ON a.id = b.student_id;

最终查询结果 #

执行修改后的语句后，会得到符合需求的结果：

First name   Title                          Grade
Caleb        My First Book Report           60
Caleb        My Second Book Report          75
Samantha     Russian Lit Through The Ages   94
Samantha     De Montaigne and The Art of The Essay 98
Raj          MISSING                        0
Carlos       Borges and Magical Realism     89
Lisa         MISSING                        0

内容的提问来源于stack exchange，提问作者Don K.