如何在PostgreSQL中为LEFT JOIN空结果集添加默认行？

可以通过**UNION ALL结合子查询**的方式实现需求，分两部分处理数据：返回已有角色的员工数据，以及为无角色的员工生成默认角色行。

方案1：基于现有角色表的默认角色 #

如果默认角色是roles表中所有存在的角色（比如示例中的Admin和Standard），可以用以下查询：

-- 第一部分：返回员工已有的角色数据
SELECT 
    e.id AS "Employee ID",
    e.name AS "Employee Name",
    r.role_id AS "Role ID",
    r.role_name AS "Role Name"
FROM employees e
JOIN roles r ON r.employee_id = e.id

UNION ALL

-- 第二部分：为无角色的员工生成所有默认角色行
SELECT 
    e.id AS "Employee ID",
    e.name AS "Employee Name",
    NULL AS "Role ID",
    dr.role_name AS "Role Name"
FROM employees e
CROSS JOIN (SELECT role_name FROM roles) dr
WHERE NOT EXISTS (SELECT 1 FROM roles r WHERE r.employee_id = e.id)

ORDER BY "Employee ID", "Role Name";

方案2：指定固定的默认角色 #

如果默认角色是固定的几个（比如仅Admin和Standard），不需要依赖roles表的现有数据，可以替换子查询为固定值：

-- 第一部分：返回员工已有的角色数据
SELECT 
    e.id AS "Employee ID",
    e.name AS "Employee Name",
    r.role_id AS "Role ID",
    r.role_name AS "Role Name"
FROM employees e
JOIN roles r ON r.employee_id = e.id

UNION ALL

-- 第二部分：为无角色的员工生成指定默认角色行
SELECT 
    e.id AS "Employee ID",
    e.name AS "Employee Name",
    NULL AS "Role ID",
    dr.role_name AS "Role Name"
FROM employees e
CROSS JOIN (
    SELECT unnest(array['Admin', 'Standard']) AS role_name
) dr
WHERE NOT EXISTS (SELECT 1 FROM roles r WHERE r.employee_id = e.id)

ORDER BY "Employee ID", "Role Name";

说明 #

• 第一部分JOIN查询仅返回员工实际拥有的角色数据，确保已有角色的记录正常显示。
• 第二部分通过CROSS JOIN为无角色的员工生成所有默认角色的组合，NOT EXISTS用于筛选出没有任何角色的员工。
• UNION ALL合并两部分结果，避免重复数据（如果用UNION会自动去重，但这里不需要，所以用ALL更高效）。

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