谢谢大家帮助我。

SELECT DISTINCT project.id, project.user_id, project.project_name
FROM project
LEFT JOIN project_roles ON project.id = project_roles.project_id
WHERE project.user_id = 1 OR project_roles.user_id = 1;

这是对我有用的答案。

尝试复制示例数据，它与您提供的有点不同，但上下文是相同的。

Schema（MySQL v8.0）

CREATE TABLE project 
(
    id  INT,
    project_name    VARCHAR(512),
    user_id INT
);

INSERT INTO project (id, project_name, user_id) VALUES ('1', 'A', '23');
INSERT INTO project (id, project_name, user_id) VALUES ('2', 'B', '42');
INSERT INTO project (id, project_name, user_id) VALUES ('4', 'D', '23');
INSERT INTO project (id, project_name, user_id) VALUES ('1', 'A', '19');
INSERT INTO project (id, project_name, user_id) VALUES ('3', 'C', '15');
INSERT INTO project (id, project_name, user_id) VALUES ('3', 'C', '29');
INSERT INTO project (id, project_name, user_id) VALUES ('2', 'B', '19');
INSERT INTO project (id, project_name, user_id) VALUES ('3', 'C', '19');


CREATE TABLE project_roles  
(
    id  INT,
    project_id  VARCHAR(512),
    user_id INT
);

INSERT INTO project_roles (id, project_id, user_id) VALUES ('1', '1', '23');
INSERT INTO project_roles (id, project_id, user_id) VALUES ('2', '2', '42');
INSERT INTO project_roles (id, project_id, user_id) VALUES ('3', '4', '23');
INSERT INTO project_roles (id, project_id, user_id) VALUES ('4', '1', '19');
INSERT INTO project_roles (id, project_id, user_id) VALUES ('5', '3', '15');
INSERT INTO project_roles (id, project_id, user_id) VALUES ('6', '3', '29');
INSERT INTO project_roles (id, project_id, user_id) VALUES ('7', '2', '19');
INSERT INTO project_roles (id, project_id, user_id) VALUES ('8', '3', '19');

CREATE TABLE users   
(
    id  INT,
    nickname VARCHAR(512)
);

INSERT INTO users (id, nickname) VALUES ('23', 'John');
INSERT INTO users (id, nickname) VALUES ('42', 'Doe');
INSERT INTO users (id, nickname) VALUES ('19', 'James');
INSERT INTO users (id, nickname) VALUES ('15', 'Mark');
INSERT INTO users (id, nickname) VALUES ('29', 'Peter');

查询#1

SELECT distinct U.id, U.nickname, PR.project_id, P.project_Name FROM users U
INNER JOIN project_roles PR ON PR.user_id = U.id
INNER JOIN project P ON P.id = PR.project_id
where  U.id = 19;

create table USERS (id, nickname) as select 1, 'test';

create table PROJECTS (id, user_id, project_name) as
select 7777, 2, 'NAME1'
union
select 8888, 1, 'NAME2'
union
select 9999, 1, 'NAME3';

create table PROJECT_ROLES (id, user_id, project_id) as
select 5, 1, 7777;

你应该使用set运算符union来实现：

select id, project_name
  from projects
 where user_id = 1
union
select p.id, p.project_name
  from project_roles r, projects p
 where r.user_id = 1
   and p.id = r.project_id;

  id  | project_name 
------+--------------
 7777 | NAME1
 9999 | NAME3
 8888 | NAME2

如果用户位于PROJECTS表和PROJECT_ROLES表中，则集合运算符UNION将删除重复项。

如果用户拥有项目A和B，且此用户在project_roles中拥有项目C行，则我希望获取项目A、B和C
第一个查询（在UNION之上）返回项目表中的项目。这很简单。
第二个查询返回project_roles中的项目，这些项目不在该用户的project中。还返回项目名称。这假定projects表不为空，并且project_roles中存在的每个项目至少有一条记录。

SELECT id, 
       user_id, 
       project_name, 
       'In projects table' AS msg
FROM projects
WHERE user_id = 1
UNION 
SELECT DISTINCT 
       pr.project_id, 
       pr.user_id, 
       COALESCE(pn.project_name,'Project name not available'), 
       'Not in projects table'
FROM project_roles pr
  LEFT JOIN (SELECT DISTINCT project_id, project_name FROM projects) pn 
    ON pr.project_id = pn.project_id
WHERE pr.user_id = 1
AND NOT EXISTS (SELECT 1 
                FROM projects p
                WHERE pr.user_id = p.user_id
                AND pr.project_id = p.id)