SQL EXISTS判断查询如何返回匹配targetId所在的表名？

适配SQL Server的修改方案 #

方案1：SQL Server 2017及以上版本（支持STRING_AGG函数）

直接在子查询中新增表名字段，过滤目标ID后聚合拼接表名即可：

SELECT 
    CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END AS flag,
    ISNULL(STRING_AGG(tableName, ', '), '') AS tableNames
FROM (
    SELECT targetId, 'Table1' AS tableName FROM [myDatabase].[dbo].[Table1] UNION ALL
    SELECT targetId, 'Table2' AS tableName FROM [myDatabase].[dbo].[Table2] UNION ALL
    SELECT targetId, 'Table3' AS tableName FROM [myDatabase].[dbo].[Table3] UNION ALL
    SELECT targetId, 'Table4' AS tableName FROM [myDatabase].[dbo].[Table4] 
) AS allRecords
WHERE targetId = 'idToSearch';

说明：这里把原来的UNION换成UNION ALL，因为不同表的记录不需要去重，查询性能更高。ISNULL用来处理ID不存在时返回空字符串而非NULL的场景，不需要可以删掉。

方案2：SQL Server 2016及更早版本（无STRING_AGG函数）

用变量拼接的方式实现，推荐下面的写法，每个子查询先过滤目标ID，数据量大的时候性能优势更明显：

DECLARE @tableNames NVARCHAR(MAX) = '';
DECLARE @flag BIT = 0;

SELECT @tableNames = @tableNames + tableName + ', ', @flag = 1
FROM (
    SELECT DISTINCT 'Table1' AS tableName FROM [myDatabase].[dbo].[Table1] WHERE targetId = 'idToSearch' UNION ALL
    SELECT DISTINCT 'Table2' AS tableName FROM [myDatabase].[dbo].[Table2] WHERE targetId = 'idToSearch' UNION ALL
    SELECT DISTINCT 'Table3' AS tableName FROM [myDatabase].[dbo].[Table3] WHERE targetId = 'idToSearch' UNION ALL
    SELECT DISTINCT 'Table4' AS tableName FROM [myDatabase].[dbo].[Table4] WHERE targetId = 'idToSearch'
) AS matchedTables;

-- 去除末尾多余的逗号和空格
IF LEN(@tableNames) > 1
    SET @tableNames = LEFT(@tableNames, LEN(@tableNames) - 1);

SELECT @flag AS flag, @tableNames AS tableNames;

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