如何用UNION ALL实现含全权限用户的公司权限关联查询

我有一张名为USER1的主表，包含User和Company字段，用于映射用户可访问的公司；另有一张名为COMPANY1的主表，存储所有可用公司列表。若USER1表中某用户的Company字段为空，则表示该用户可访问COMPANY1表中的所有公司。

USER1表数据 #

USER1 table

User          |          Company
username1     |          MyCompany2
username2     |          MyCompany1
username3     |          
username4     |          

COMPANY1表数据 #

COMPANY1 table

Index       |      Company
1           |      MyCompany1
2           |      MyCompany2
3           |      MyCompany3
4           |      MyCompany4

期望结果 #

NEW table

User          |          Company
username1     |          MyCompany2
username2     |          MyCompany1
username3     |          MyCompany1
username3     |          MyCompany2
username3     |          MyCompany3
username3     |          MyCompany4
username4     |          MyCompany1
username4     |          MyCompany2
username4     |          MyCompany3
username4     |          MyCompany4

尝试的SQL及报错 #

我尝试了如下SQL语句：

SELECT [User], [Company Name] FROM [USER1]
WHERE [Company Name] <> ''

UNION ALL

SELECT [User],
(SELECT [Company] FROM [COMPANY1]) [Company Name]
FROM [USER1]
WHERE [Company Name] = ''

执行时报错：提示子查询只能返回一个结果。

解决方案 #

之前的错误原因是：SELECT子句中的子查询(SELECT [Company] FROM [COMPANY1])返回了多行数据，但该位置的子查询必须仅返回单个值，因此触发报错。

正确的做法是对Company字段为空的用户，使用**交叉连接（CROSS JOIN）**关联COMPANY1表，生成用户与所有公司的组合记录。

正确SQL语句 #

-- 提取有明确关联公司的用户记录
SELECT [User], [Company] AS [Company Name]
FROM [USER1]
WHERE [Company] <> ''

UNION ALL

-- 为无关联公司的用户生成所有公司的访问记录
SELECT u.[User], c.[Company] AS [Company Name]
FROM [USER1] u
CROSS JOIN [COMPANY1] c
WHERE u.[Company] = ''

逻辑说明 #

1. 第一部分查询直接筛选USER1中Company不为空的记录，保留原有用户-公司的映射关系。
2. 第二部分通过CROSS JOIN将USER1中Company为空的用户，与COMPANY1的所有公司记录进行关联，每个空Company的用户会生成与所有公司的组合记录，满足“访问所有公司”的需求。
3. 使用UNION ALL合并两部分结果，由于两部分数据无重复，无需自动去重（若使用UNION会额外执行去重操作，效率更低）。

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