使用LATERAL JOIN按外键过滤出现异常结果的解决咨询

我尝试通过外键过滤一个NxM（多对多）表，支持0、1或多个不同标签。但LEFT LATERAL JOIN返回了异常结果。请忽略奇怪的类型转换，这是因为我在使用Spring Boot。

数据库结构（PostgreSQL v13） #

CREATE TABLE posts (id int primary key);
CREATE TABLE tags (id int primary key);
CREATE TABLE post_tags (post_id int references posts(id),
                        tags_id int references tags(id),
                       primary key (post_id, tags_id));

INSERT INTO posts VALUES (1), (2), (3), (4);
INSERT INTO tags VALUES (8), (9);
INSERT INTO post_tags VALUES (1,8), (1,9), (2,8);

现有查询及问题 #

查询1 #

select * from posts p
left join lateral (select * from post_tags pt where pt.post_id = p.id) pt on 1=1
where (1 is null or pt.tags_id = any(cast(STRING_TO_ARRAY(CAST('9' AS TEXT), ',') AS INT[])));

结果：

查询2 #

select * from posts p
left join lateral (select * from post_tags pt where pt.post_id = p.id limit 1) pt on 1=1
where (1 is null or pt.tags_id = any(cast(STRING_TO_ARRAY(CAST('9' AS TEXT), ',') AS INT[])));

结果：无结果显示（不符合预期，理应返回post1）

查询3 #

select * from posts p
left join lateral (select * from post_tags pt where pt.post_id = p.id) pt on 1=1
where (1 is null or pt.tags_id = any(cast(STRING_TO_ARRAY(CAST('9,8' AS TEXT), ',') AS INT[])));

结果：出现重复的post1

查询4 #

select * from posts p
left join lateral (select * from post_tags pt where pt.post_id = p.id limit 1) pt on 1=1
where (1 is null or pt.tags_id = any(cast(STRING_TO_ARRAY(CAST('9,8' AS TEXT), ',') AS INT[])));

结果：符合预期（无重复post）

需求 #

查询返回每个匹配条件的post至多一条结果：

• 传入单个标签（如9）时，应返回：

• 传入多个标签（如9,8）时，返回如查询4的结果（匹配post1和post2，无重复post1）

解决方案 #

问题根源 #

查询2失败的核心原因是：LATERAL JOIN里先随机取了post1的第一条标签（8），之后外部WHERE条件判断该标签是否等于9，不满足就被过滤了。正确的做法是先在LATERAL子查询内过滤符合条件的标签，再取一条。

方法1：使用EXISTS子查询（仅返回post信息） #

如果只需要返回post本身的信息，不需要关联的标签，用EXISTS最简洁，天然避免重复：

SELECT p.*
FROM posts p
WHERE EXISTS (
    SELECT 1
    FROM post_tags pt
    WHERE pt.post_id = p.id
    AND pt.tags_id = ANY(STRING_TO_ARRAY('9', ',')::INT[])
);

支持0个标签的场景（返回所有post）：

SELECT p.*
FROM posts p
WHERE STRING_TO_ARRAY('9', ',')::INT[] IS NULL 
OR EXISTS (
    SELECT 1
    FROM post_tags pt
    WHERE pt.post_id = p.id
    AND pt.tags_id = ANY(STRING_TO_ARRAY('9', ',')::INT[])
);

方法2：LATERAL JOIN内先过滤再取一条（返回post+匹配标签） #

如果需要返回匹配的标签信息，把过滤条件放到LATERAL子查询内部，确保只关联符合条件的标签，再取一条：

SELECT p.id, pt.post_id, pt.tags_id
FROM posts p
LEFT JOIN LATERAL (
    SELECT pt.post_id, pt.tags_id
    FROM post_tags pt
    WHERE pt.post_id = p.id
    AND pt.tags_id = ANY(STRING_TO_ARRAY('9', ',')::INT[])
    LIMIT 1
) pt ON true
WHERE pt.post_id IS NOT NULL;

支持0个标签的场景：

SELECT p.id, pt.post_id, pt.tags_id
FROM posts p
LEFT JOIN LATERAL (
    SELECT pt.post_id, pt.tags_id
    FROM post_tags pt
    WHERE pt.post_id = p.id
    AND (STRING_TO_ARRAY('9', ',')::INT[] IS NULL OR pt.tags_id = ANY(STRING_TO_ARRAY('9', ',')::INT[]))
    LIMIT 1
) pt ON true
WHERE STRING_TO_ARRAY('9', ',')::INT[] IS NULL OR pt.post_id IS NOT NULL;

方法3：使用聚合函数（返回post+所有匹配标签的集合） #

如果需要展示该post所有匹配的标签，用数组聚合避免重复行：

SELECT p.id, 
       array_agg(pt.tags_id) AS matched_tags
FROM posts p
JOIN post_tags pt ON p.id = pt.post_id
WHERE pt.tags_id = ANY(STRING_TO_ARRAY('9,8', ',')::INT[])
GROUP BY p.id;

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