PostgreSQL查询：获取用户ID=1的自有及关联角色的所有项目

表结构 #

• project：id（项目ID）、user_id（创建者ID）、project_name（项目名称）
• project_roles：id、user_id（关联用户ID）、project_id（关联项目ID）
• users：id（用户ID）、nickname（昵称）

需求 #

查询用户ID为1的所有项目，包含两类：

1. 用户自己创建的项目（project.user_id = 1）
2. 用户通过project_roles表关联的项目

示例数据 #

• users表：
  • id: 1, nickname: 'test'
• project表：
  • id: 7777, user_id: 2, project_name: 'NAME1'
  • id: 8888, user_id: 1, project_name: 'NAME2'
  • id: 9999, user_id: 1, project_name: 'NAME3'
• project_roles表：
  • id: 5, user_id: 1, project_id: '7777'

期望返回全部3个项目（7777、8888、9999）

原SQL问题 #

你写的SQL存在语法错误，OR后面的子句未正确关联两张表，直接嵌套WHERE的写法不符合SQL语法规范，无法得到期望结果。

正确SQL写法 #

方法1：使用EXISTS子查询 #

该方式查询效率较高，适合数据量较大的场景：

SELECT *
FROM project
WHERE user_id = 1
   OR EXISTS (
       SELECT 1
       FROM project_roles
       WHERE project_roles.project_id = project.id
         AND project_roles.user_id = 1
   );

方法2：使用JOIN + DISTINCT #

如果用户既是项目创建者又在project_roles中有记录，会出现重复数据，因此用DISTINCT去重：

SELECT DISTINCT p.*
FROM project p
LEFT JOIN project_roles pr ON p.id = pr.project_id
WHERE p.user_id = 1
   OR pr.user_id = 1;

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