如何编写Inner Join查询获取指定用户可访问的所有Items

让我来帮你梳理正确的实现思路和查询语句。首先要明确：用户能访问的Item分为两类——用户自己拥有的Item，以及通过所属访问组（t_access_gropes）被授权访问的Item。你的原查询只覆盖了部分场景，而且关联逻辑存在问题，导致结果不完整。

先回顾你的表结构与测试数据 #

表结构：

CREATE TABLE t_users ( user_id varchar PRIMARY KEY, user_email varchar );
CREATE TABLE t_items ( item_id varchar PRIMARY KEY, owner_id varchar not null references t_users(user_id), title varchar );
CREATE TABLE t_access_gropes ( access_group_id varchar PRIMARY KEY, user_id varchar not null references t_users(user_id) );
CREATE TABLE t_access_sets ( access_set_id varchar PRIMARY KEY, item_id varchar not null references t_items(item_id), access_group_id varchar not null references t_access_gropes(access_group_id) );

测试数据：

INSERT INTO t_users VALUES ('us123', 'us123@email.com');
INSERT INTO t_users VALUES ('us456', 'us456@email.com');
INSERT INTO t_users VALUES ('us789', 'us789@email.com');

INSERT INTO t_items VALUES ('it123', 'us123', 'title1');
INSERT INTO t_items VALUES ('it456', 'us456', 'title2');
INSERT INTO t_items VALUES ('it678', 'us789', 'title3');
INSERT INTO t_items VALUES ('it323', 'us123', 'title4');
INSERT INTO t_items VALUES ('it764', 'us456', 'title5');
INSERT INTO t_items VALUES ('it826', 'us789', 'title6');
INSERT INTO t_items VALUES ('it568', 'us123', 'title7');
INSERT INTO t_items VALUES ('it038', 'us456', 'title8');
INSERT INTO t_items VALUES ('it728', 'us789', 'title9');

INSERT INTO t_access_gropes VALUES ('ag123', 'us123');
INSERT INTO t_access_gropes VALUES ('ag456', 'us456');
INSERT INTO t_access_gropes VALUES ('ag789', 'us789');

INSERT INTO t_access_sets VALUES ('as123', 'it123', 'ag123');
INSERT INTO t_access_sets VALUES ('as456', 'it456', 'ag123');

原查询的问题分析 #

你的原查询使用了多层INNER JOIN，这会强制要求所有关联条件都满足：

• Item必须属于该用户
• 用户必须有对应的访问组
• Item必须被加入到该访问组的权限集合中

这就漏掉了两类关键数据：

1. 用户自己拥有但未配置到任何权限集合的Item（比如it323、it568）
2. 不属于该用户但通过访问组授权的Item（比如it456，属于us456但授权给了us123的访问组）

正确的查询实现 #

这里提供两种简洁且正确的写法：

方法1：使用UNION合并两类数据

UNION会自动去重，避免同一个Item既属于用户又被授权时重复出现：

-- 获取用户自己拥有的Item + 通过访问组授权的Item
SELECT i.*
FROM t_items i
WHERE i.owner_id = 'us123'
UNION
SELECT i.*
FROM t_items i
JOIN t_access_sets s ON i.item_id = s.item_id
JOIN t_access_gropes g ON s.access_group_id = g.access_group_id
WHERE g.user_id = 'us123';

针对你的测试数据，这条查询会返回us123可访问的4条Item：it123、it323、it568、it456。

方法2：使用OR结合子查询

这种写法逻辑更直观，直接筛选“自己拥有”或“在授权列表中”的Item：

SELECT i.*
FROM t_items i
WHERE 
    -- 条件1：用户自己拥有的Item
    i.owner_id = 'us123'
    -- 条件2：通过访问组授权的Item
    OR i.item_id IN (
        SELECT s.item_id
        FROM t_access_sets s
        JOIN t_access_gropes g ON s.access_group_id = g.access_group_id
        WHERE g.user_id = 'us123'
    );

两种方法的结果一致，你可以根据自己的习惯选择使用。

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