Query in mysql gives 0 results if one of the tables is empty

Question

I have these 3 tables:

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));

I want to get rows from the report table that meet at least one of the following conditions:

1 - The field public_access is true
2 - The report is in the related table report_users with in parameter user_id 
3 - The report is in the related table report_groups with in parameter group_id

First, I create a new report that is publicly accessible:

insert into reports values(null, 'report 1 open to all', 1);

Then another report can only be accessed by user_id = 1:

insert into reports values(null, 'report 2 only for user_id 1', 0);
insert into report_users values(null, 2, 1);

Then another report can only be accessed by group_id = 1

insert into reports values(null, 'report 3 only for group_id 1', 0);
insert into report_groups values(null, 3, 1);

Now, I have 3 rows: each one is accessible, one row is only accessible by user_id = 1 and another row is only accessible by group_id = 1.

Give me all rows where user_id = 1:

select reports.* 
from reports, report_users,report_groups
where 
reports.public_access = 1
or
(report_users.report_id = reports.id and report_users.user_id = 1)
or
(report_groups.report_id = reports.id and report_groups.group_id = 5)
;

I get 2 rows. It works.

Give me all rows with group_id = 1:

select reports.* 
from reports, report_users,report_groups
where 
reports.public_access = 1
or
(report_users.report_id = reports.id and report_users.user_id = 4)
or
(report_groups.report_id = reports.id and report_groups.group_id = 1)
;

I get 2 rows. It works.

but. If report_users or report_groups are empty, I don't get any results. I first run this query:

truncate table report_groups;

When I run the same query as before, I get an empty set. Why? Actually, it doesn't look like there's any difference in the user_id and group_id I'm sending. I always get 0 rows.

To me it seems like just because one of the two tables is empty I'm not getting any results. Is there something wrong with the query itself?

Answer 1

What do you do with this line of code:

from reports, report_users,report_groups

is an (old style) CROSS JOIN of 3 tables, which means that if one of the tables is empty, the result will also be empty.

Instead use EXISTS:

select r.* 
from reports r
where r.public_access = 1
   or exists (select * from report_users u where u.report_id = r.id and u.user_id = ?)
   or exists (select * from report_groups g where g.report_id = r.id and g.group_id = ?);