MySQL：关联表无匹配行时为字段设置默认值

系统环境 #

$ uname -srvm
Linux 5.15.0-56-generic #62-Ubuntu SMP Tue Nov 22 19:54:14 UTC 2022 x86_64

$ mysql --version
mysql  Ver 8.0.31-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu))

表结构与数据 #

character_stats表 #

mysql> SELECT name, level FROM character_stats;
+-----------+-------+
| name      | level |
+-----------+-------+
| foo       |     0 |
| bar       |     0 |
| baz       |     3 |
| tester    |     4 |
| testertoo |     2 |
+-----------+-------+

halloffame表 #

mysql> SELECT * from halloffame;
+----+-----------+----------+--------+
| id | charname  | fametype | points |
+----+-----------+----------+--------+
|  1 | bar       | T        |      0 |
|  2 | foo       | T        |      0 |
|  3 | baz       | T        |      0 |
|  4 | tester    | T        |      0 |
|  5 | testertoo | T        |      0 |
|  6 | tester    | D        |     40 |
|  7 | tester    | M        |     92 |
|  8 | bar       | M        |     63 |
+----+-----------+----------+--------+

需求与问题 #

需要展示character_stats的所有行，同时关联halloffame中fametype='M'的points列；若角色无对应fametype='M'的行，需将points设为0，而非省略整行。

当前使用内连接的查询结果仅返回有匹配的行：

mysql> SELECT name, level, points FROM character_stats JOIN
    -> (SELECT charname, points FROM halloffame WHERE fametype='M')
    -> AS hof ON (hof.charname=name);
+--------+-------+--------+
| name   | level | points |
+--------+-------+--------+
| tester |     4 |     92 |
| bar    |     0 |     63 |
+--------+-------+--------+

期望输出：

+-----------+-------+--------+
| name      | level | points |
+-----------+-------+--------+
| foo       |     0 |      0 |
| bar       |     0 |     63 |
| baz       |     3 |      0 |
| tester    |     4 |     92 |
| testertoo |     2 |      0 |
+-----------+-------+--------+

尝试过的无效方法 #

• 单个角色的IFNULL查询有效，但无法关联全表：

SELECT IFNULL((SELECT points FROM halloffame WHERE fametype='M'
AND charname='foo' LIMIT 1), 0) as points;

• 关联时使用COALESCE无法获取character_stats.name的值：

SELECT name, level, 'M' AS fametype, points FROM character_stats
JOIN (SELECT COALESCE((SELECT points FROM halloffame WHERE
fametype='M' AND charname=name LIMIT 1), 0) AS points) AS hof;

• 尝试CROSS JOIN报错Unknown column 'cc.name' in 'where clause'：

SELECT name, level, points FROM character_stats
CROSS JOIN (SELECT DISTINCT name FROM character_stats) AS cc
JOIN (SELECT COALESCE((SELECT points FROM halloffame WHERE
fametype='M' AND charname=cc.name LIMIT 1), 0) AS points) AS hof;

解决方案 #

方法一：左连接 + IFNULL #

SELECT 
    cs.name, 
    cs.level, 
    IFNULL(hof.points, 0) AS points
FROM character_stats cs
LEFT JOIN halloffame hof 
    ON cs.name = hof.charname 
    AND hof.fametype = 'M';

说明：左连接会保留character_stats的所有行，未匹配到halloffame中fametype='M'的行时，points为NULL，通过IFNULL将NULL替换为0。注意hof.fametype='M'要写在ON条件中，若写在WHERE中会过滤掉无匹配的行，变成内连接效果。

方法二：子查询 + COALESCE #

SELECT 
    name, 
    level, 
    COALESCE(
        (SELECT points FROM halloffame WHERE charname = cs.name AND fametype='M' LIMIT 1),
        0
    ) AS points
FROM character_stats cs;

说明：对character_stats的每一行，通过关联别名cs的子查询获取对应角色的M类型积分，无匹配时返回NULL，再用COALESCE将NULL替换为0。

内容的提问来源于stack exchange，提问作者AntumDeluge