值缺失时的表连接问题：如何获取类别与子类别数据

原查询语句用于根据ID获取物品的类别和子类别名称：

SELECT a.value, b.value FROM X a
INNER JOIN X b ON a.ID = a.ID
               AND a.NAME = 'Category'
               AND b.NAME = 'Subcategory'

问题在于部分类别没有对应的子类别（即该ID不存在Subcategory行），而由于X.NAME有五十余种不同取值，无法移除b.NAME = 'Subcategory'条件。要求实现：返回类别名称，当子类别缺失时显示NULL值，且无需手动插入X.NAME = 'Subcategory'且X.value = NULL的行。

示例数据 #

insert into X values ('10101', 'Category', 'Murder');
insert into X values ('10101', 'Subcategory', 'Strangulation');
insert into X values ('15151', 'Category', 'Theft');
insert into X values ('15151', 'Subcategory', 'Vehical');
insert into X values ('15151', 'Severity', 'High');
insert into X values ('33456', 'Category', 'Theft');
insert into X values ('45565', 'Category', 'Murder');
insert into X values ('45565', 'Severity', 'Low');
insert into X values ('76543', 'Category', 'Violence');
insert into X values ('76543', 'Subcategory', 'Domestic Violence');
insert into X values ('76543', 'Severity', 'Medium');

解决方案 #

使用LEFT JOIN替代INNER JOIN，并调整条件位置即可实现需求：

SELECT a.value AS Category, b.value AS Subcategory
FROM X a
LEFT JOIN X b 
    ON a.ID = b.ID 
    AND b.NAME = 'Subcategory'
WHERE a.NAME = 'Category';

逻辑说明 #

1. LEFT JOIN特性：左表会保留所有满足a.NAME = 'Category'的记录，即使右表没有匹配的同ID+Subcategory行，对应的b.value会自动填充为NULL，正好满足子类别缺失时显示NULL的要求。
2. 条件拆分：把a.NAME = 'Category'放在WHERE里过滤出所有类别行，b.NAME = 'Subcategory'留在JOIN条件中，确保只关联子类别数据，不会牵扯其他五十余种NAME类型的行。

预期结果示例 #

执行后会得到如下格式的结果：

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