从SELECT插入临时表返回空表，如何让Id返回NULL替代？

场景说明 #

需要将外部传入的ID字符串拆分后，根据主表mod_SHOP_Prodotti的ProdottoAggregato字段值，分别将匹配的ID存入两个临时表；当前问题是当临时表无匹配数据时返回空表，希望改为返回一条Id为NULL的记录。

原实现代码：

CREATE TABLE #TempProdotti (Id int NULL);
CREATE TABLE #TempProdottiAggregati (Id int NULL);
CREATE TABLE #TempCorsiSingoli (Id int NULL);

-- 拆分ID字符串并转换为INT类型存入临时表
INSERT INTO #TempProdotti (Id)
(SELECT CAST(value AS int) AS Id
 FROM string_split('3116,3122,3090', ','));

-- 将匹配的非聚合产品ID存入临时表
INSERT INTO #TempCorsiSingoli (Id)
(SELECT Id
 FROM mod_SHOP_Prodotti
 WHERE Id IN (SELECT Id FROM #TempProdotti)
   AND ProdottoAggregato = 0);

-- 将匹配的聚合产品ID存入临时表
INSERT INTO #TempProdottiAggregati (Id)
(SELECT Id
 FROM mod_SHOP_Prodotti
 WHERE Id IN (SELECT Id FROM #TempProdotti)
   AND ProdottoAggregato = 1);

-- 查询临时表
SELECT *
FROM #TempProdotti;
SELECT *
FROM #TempProdottiAggregati;
SELECT *
FROM #TempCorsiSingoli;

DROP TABLE #TempProdotti;
DROP TABLE #TempProdottiAggregati;
DROP TABLE #TempCorsiSingoli;

简洁解决方案 #

方案1：修改查询语句，无数据时返回NULL #

在查询临时表时，通过UNION ALL结合NOT EXISTS判断表是否为空，为空则追加一条Id为NULL的记录：

CREATE TABLE #TempProdotti (Id int NULL);
CREATE TABLE #TempProdottiAggregati (Id int NULL);
CREATE TABLE #TempCorsiSingoli (Id int NULL);

INSERT INTO #TempProdotti (Id)
(SELECT CAST(value AS int) AS Id
 FROM string_split('3116,3122,3090', ','));

INSERT INTO #TempCorsiSingoli (Id)
(SELECT Id
 FROM mod_SHOP_Prodotti
 WHERE Id IN (SELECT Id FROM #TempProdotti)
   AND ProdottoAggregato = 0);

INSERT INTO #TempProdottiAggregati (Id)
(SELECT Id
 FROM mod_SHOP_Prodotti
 WHERE Id IN (SELECT Id FROM #TempProdotti)
   AND ProdottoAggregato = 1);

-- 原始ID表查询
SELECT * FROM #TempProdotti;

-- 聚合产品表查询：无数据时返回NULL
SELECT Id FROM #TempProdottiAggregati
UNION ALL
SELECT NULL WHERE NOT EXISTS (SELECT 1 FROM #TempProdottiAggregati);

-- 单个课程表查询：无数据时返回NULL
SELECT Id FROM #TempCorsiSingoli
UNION ALL
SELECT NULL WHERE NOT EXISTS (SELECT 1 FROM #TempCorsiSingoli);

DROP TABLE #TempProdotti;
DROP TABLE #TempProdottiAggregati;
DROP TABLE #TempCorsiSingoli;

方案2：简化流程，无需临时表 #

使用CTE（通用表表达式）直接拆分ID并关联主表，同时处理无匹配的情况，省去临时表的创建和销毁步骤：

WITH SplitIds AS (
    -- 拆分并转换ID字符串
    SELECT CAST(value AS int) AS Id FROM string_split('3116,3122,3090', ',')
)
-- 查询单个课程：无匹配时返回NULL
SELECT s.Id 
FROM SplitIds s
JOIN mod_SHOP_Prodotti p ON s.Id = p.Id
WHERE p.ProdottoAggregato = 0
UNION ALL
SELECT NULL WHERE NOT EXISTS (
    SELECT 1 FROM SplitIds s
    JOIN mod_SHOP_Prodotti p ON s.Id = p.Id
    WHERE p.ProdottoAggregato = 0
);

-- 查询聚合产品：无匹配时返回NULL
SELECT s.Id 
FROM SplitIds s
JOIN mod_SHOP_Prodotti p ON s.Id = p.Id
WHERE p.ProdottoAggregato = 1
UNION ALL
SELECT NULL WHERE NOT EXISTS (
    SELECT 1 FROM SplitIds s
    JOIN mod_SHOP_Prodotti p ON s.Id = p.Id
    WHERE p.ProdottoAggregato = 1
);

-- 可选：查询原始拆分的ID
SELECT * FROM SplitIds;

内容的提问来源于stack exchange，提问作者Matteo Pietro Peru