SQL JOIN查询如何返回所有可能行 即使关联表无匹配权限记录

单条SQL完全可以实现需求，你当前查询返回不全的核心原因是主表选型错误：以存储已配置权限的tb_permission作为主表，自然只能返回存在配置记录的行。正确逻辑是先生成「查询维度下所有权限区域」的基准数据集，再左关联实际权限配置，空值补0即可。

核心实现逻辑 #

• 第一步：通过交叉连接（CROSS JOIN）构造基准集：固定你要查询的id_store、id_group参数，和tb_area表的所有区域行做笛卡尔积，得到该门店、该用户组下所有权限区域的完整行集合
• 第二步：左连接tb_permission表，关联条件要全部写在LEFT JOIN ... ON子句中，不能写在WHERE里，避免过滤掉无配置的区域行
• 第三步：用COALESCE()函数将权限字段匹配不到的NULL值替换为0，和预期输出格式对齐

可直接运行的SQL #

SELECT
  2 AS id_store,
  2 AS id_group,
  ta.area,
  COALESCE(tp.access, 0) AS access,
  COALESCE(tp.add, 0) AS add,
  COALESCE(tp.edit, 0) AS edit,
  COALESCE(tp.remove, 0) AS remove
FROM tb_area ta
-- 构造固定查询参数的基准维度
CROSS JOIN (SELECT 2 AS query_store_id, 2 AS query_group_id) AS params
LEFT JOIN tb_permission tp
  ON tp.id_store = params.query_store_id
  AND tp.id_group = params.query_group_id
  AND tp.id_area = ta.id

运行上述SQL返回的结果和你给出的预期结果完全一致。

如果需要同时关联返回用户组的slug等信息，可以调整为关联tb_group表的写法，无需硬编码用户组ID：

SELECT
  2 AS id_store,
  tg.id AS id_group,
  tg.slug,
  ta.area,
  COALESCE(tp.access, 0) AS access,
  COALESCE(tp.add, 0) AS add,
  COALESCE(tp.edit, 0) AS edit,
  COALESCE(tp.remove, 0) AS remove
FROM tb_group tg
CROSS JOIN tb_area ta
LEFT JOIN tb_permission tp
  ON tp.id_store = 2
  AND tp.id_group = tg.id
  AND tp.id_area = ta.id
WHERE tg.id = 2

原SQL问题说明 #

你原来的写法存在两个问题：

1. 以tb_permission作为主表，天然缺失了未配置权限的区域行
2. 把tp.id_store = 2写在WHERE子句中，会把左连接产生的NULL行直接过滤掉，等价于内连接效果，自然无法返回无配置的权限项。

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