如何使用Subquery返回无NULL值结果？改写Join查询遇难题

问题背景 #

我原本用JOIN写的SQL查询能正常返回无NULL的结果，但改成子查询后结果里出现了NULL值，想知道怎么消除这些NULL，还有优化查询的办法吗？

我的子查询语句（带NULL结果） #

select ri.id_room,ri.id_item,
(select r.room_name from tbl_rooms r where r.id = ri.id_room and r.id_property = 1)  as 'Room Name',
(select r.sortvalue from tbl_rooms r where r.id = ri.id_room and r.id_property = 1) as 'Sort Value',
(select 'Room Status' = case when is_active = '1' then 'Active' else 'INACTIVE' end from tbl_rooms r where r.id = ri.id_room and r.id_property = 1),
(select i.itemname from tbl_items i where i.id = ri.id_item) as 'Item Code'
 from tbl_roomitems ri

原本的JOIN语句（无NULL结果） #

select ri.id_room, r.room_name, r.sortvalue,'Status' = case when is_active = '1' then 'Active' 
else 'INACTIVE' end from tbl_roomitems ri
left outer join tbl_rooms r on ri.id_room = r.id
where ri.id_item = 1 and r.id_property = 1
order by r.sortvalue, r.room_name

一、消除子查询的NULL值 #

子查询出现NULL的核心原因是没对齐JOIN版的过滤逻辑，且未处理无匹配记录的情况。要得到和JOIN版一致的无NULL结果，可采用以下两种方案：

方案1：用COALESCE替换NULL，同时添加过滤条件 #

给子查询返回的结果套上COALESCE函数，把NULL替换成你需要的默认值（比如空字符串、0、默认状态），同时在主查询里加上ri.id_item = 1的过滤条件：

select 
    ri.id_room,
    ri.id_item,
    -- 把NULL替换为空字符串
    COALESCE((select r.room_name from tbl_rooms r where r.id = ri.id_room and r.id_property = 1), '') as 'Room Name',
    -- 把NULL替换为0（根据sortvalue类型调整）
    COALESCE((select r.sortvalue from tbl_rooms r where r.id = ri.id_room and r.id_property = 1), 0) as 'Sort Value',
    -- 把NULL替换为默认状态
    COALESCE((select case when is_active = '1' then 'Active' else 'INACTIVE' end from tbl_rooms r where r.id = ri.id_room and r.id_property = 1), 'INACTIVE') as 'Room Status',
    COALESCE((select i.itemname from tbl_items i where i.id = ri.id_item), '') as 'Item Code'
from tbl_roomitems ri
where ri.id_item = 1  -- 和JOIN版对齐过滤条件
order by 
    (select r.sortvalue from tbl_rooms r where r.id = ri.id_room and r.id_property = 1),
    (select r.room_name from tbl_rooms r where r.id = ri.id_room and r.id_property = 1)

方案2：直接过滤掉含NULL的记录 #

如果不需要默认值，只想保留tbl_rooms里有匹配的记录，就加EXISTS子句过滤：

select 
    ri.id_room,
    ri.id_item,
    (select r.room_name from tbl_rooms r where r.id = ri.id_room and r.id_property = 1) as 'Room Name',
    (select r.sortvalue from tbl_rooms r where r.id = ri.id_room and r.id_property = 1) as 'Sort Value',
    (select case when is_active = '1' then 'Active' else 'INACTIVE' end from tbl_rooms r where r.id = ri.id_room and r.id_property = 1) as 'Room Status',
    (select i.itemname from tbl_items i where i.id = ri.id_item) as 'Item Code'
from tbl_roomitems ri
where 
    ri.id_item = 1
    -- 只保留tbl_rooms中有匹配的记录
    AND EXISTS (select 1 from tbl_rooms r where r.id = ri.id_room and r.id_property = 1)
order by 
    (select r.sortvalue from tbl_rooms r where r.id = ri.id_room and r.id_property = 1),
    (select r.room_name from tbl_rooms r where r.id = ri.id_room and r.id_property = 1)

二、查询优化建议 #

1. 优先用JOIN而非多列子查询
   你原来的JOIN写法效率更高，因为多列子查询会对每一列单独执行一次查询（相当于多次访问tbl_rooms表），而JOIN只需要关联一次表，减少数据库的IO次数。

2. 添加针对性索引
   给关联字段加索引能大幅提升查询速度：

-- 给tbl_rooms的id和id_property加联合索引，加速子查询/关联
CREATE INDEX idx_rooms_id_property ON tbl_rooms(id, id_property);
-- 给tbl_roomitems的id_item和id_room加索引，加速过滤和关联
CREATE INDEX idx_roomitems_id_item_id_room ON tbl_roomitems(id_item, id_room);
-- 确保tbl_items的id是主键（如果不是，添加主键或索引）
ALTER TABLE tbl_items ADD PRIMARY KEY (id);

3. 避免重复子查询（如果一定要用子查询）
   不要重复写相同条件的子查询，用APPLY提前获取tbl_rooms的所需数据，只关联一次表：

select 
    ri.id_room,
    ri.id_item,
    COALESCE(r.room_name, '') as 'Room Name',
    COALESCE(r.sortvalue, 0) as 'Sort Value',
    COALESCE(r.room_status, 'INACTIVE') as 'Room Status',
    COALESCE(i.itemname, '') as 'Item Code'
from tbl_roomitems ri
-- 一次性获取tbl_rooms的所需字段
outer apply (
    select 
        room_name, 
        sortvalue, 
        case when is_active = '1' then 'Active' else 'INACTIVE' end as room_status
    from tbl_rooms r 
    where r.id = ri.id_room and r.id_property = 1
) r
left join tbl_items i on i.id = ri.id_item
where ri.id_item = 1
order by r.sortvalue, r.room_name

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