基于关联表返回用户关联公司列表，是否可使用Lateral Join？

数据库表结构 #

Users
- id
- name

Users_In_Company
- company_id
- user_id

Companies
- id
- name

需求 #

返回用户列表，新增一列展示该用户关联的所有公司名称（逗号分隔）。

现有基础查询 #

SELECT u.*
FROM users u

期望输出 #

ID   NAME   Companies
1    John   Acme1, Amazon, Walmart

问题 #

是否可以使用Lateral Join实现该需求？需考虑性能，单请求最多返回1000行。

当然可以用Lateral Join实现这个需求，且针对单请求1000行的场景，性能完全可控。

具体查询语句如下：

SELECT 
    u.id,
    u.name,
    c.companies
FROM users u
LEFT JOIN LATERAL (
    SELECT string_agg(co.name, ', ') AS companies
    FROM users_in_company uc
    JOIN companies co ON uc.company_id = co.id
    WHERE uc.user_id = u.id
) c ON true
LIMIT 1000;

补充说明 #

• 用LEFT JOIN LATERAL能保证无关联公司的用户也会被返回，此时Companies列值为NULL；若只需要有关联公司的用户，替换为JOIN LATERAL即可。
• string_agg是PostgreSQL的字符串聚合函数，若使用MySQL等其他数据库，可替换为GROUP_CONCAT并调整对应语法。
• 性能优化建议：给users_in_company(user_id, company_id)和companies(id)建立索引，能大幅提升聚合查询的速度，尤其在数据量较大时。
• 由于单请求限制返回1000行，Lateral Join的额外开销非常有限，不会出现性能瓶颈。

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