MySQL查询遇关联表report_users/report_groups为空时返回0结果的问题排查

我创建了三张表：

create table reports(id int not null AUTO_INCREMENT,name varchar(255)not null,public_access tinyint not null,primary key (id));
create table report_users(id int not null AUTO_INCREMENT,report_id int not null,user_id int not null,primary key (id),foreign key (report_id) references reports(id));
create table report_groups(id int not null AUTO_INCREMENT,report_id int not null,group_id int not null,primary key (id),foreign key (report_id) references reports(id));

需求是查询reports表中满足以下任一条件的记录：

• public_access字段为true
• 报表在关联表report_users中匹配指定user_id
• 报表在关联表report_groups中匹配指定group_id

插入测试数据后，正常查询能返回符合条件的结果，但执行truncate table report_groups;清空该表后，无论传入的user_id和group_id是什么，查询都返回空集。请问这是查询语句本身存在问题吗？

这大概率是你用了**内连接（INNER JOIN）**关联report_groups表导致的问题。内连接要求关联的两张表都有匹配记录才会返回结果，当report_groups被清空后，这个关联条件会过滤掉所有记录，哪怕其他条件满足也不行。

正确的做法是用**EXISTS子查询或者左连接（LEFT JOIN）**配合条件判断，这样不会因为某一张关联表为空而导致所有结果被过滤。

推荐写法1：使用EXISTS子查询 #

SELECT DISTINCT r.*
FROM reports r
WHERE r.public_access = 1
OR EXISTS (SELECT 1 FROM report_users ru WHERE ru.report_id = r.id AND ru.user_id = [指定的user_id])
OR EXISTS (SELECT 1 FROM report_groups rg WHERE rg.report_id = r.id AND rg.group_id = [指定的group_id]);

推荐写法2：使用左连接 #

SELECT DISTINCT r.*
FROM reports r
LEFT JOIN report_users ru ON r.id = ru.report_id AND ru.user_id = [指定的user_id]
LEFT JOIN report_groups rg ON r.id = rg.report_id AND rg.group_id = [指定的group_id]
WHERE r.public_access = 1
OR ru.user_id IS NOT NULL
OR rg.group_id IS NOT NULL;

这两种写法都能确保满足任一条件的记录被返回，不会因某张关联表为空而过滤掉所有结果。

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