MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.27


CREATE TABLE `notification` (
  `notif_id` BIGINT NOT NULL,
  `name` VARCHAR(45) NULL,
  `status` TINYINT NULL,
  PRIMARY KEY (`notif_id`));
CREATE TABLE `user` (
  `user_id` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`user_id`));
CREATE TABLE `notif_user` (
  `notif_id` BIGINT NOT NULL,
  `user_id` VARCHAR(45) NULL
 );
CREATE TABLE `user_group` (
  `group_id` BIGINT NOT NULL,
  `name` VARCHAR(45) NULL
 );
CREATE TABLE `notif_group` (
  `notif_id` BIGINT NOT NULL,
  `group_id` VARCHAR(45) NULL
 );
insert into notification values (1,'Notif1',0);
insert into notification values (2,'Notif2',0);
insert into notification values (3,'Notif3',1);
insert into notification values (4,'Notif4',0);
insert into notification values (5,'Notif5',0);
insert into user values (1,'First');
insert into user values (2,'Second');
insert into user_group values (1,'One');
insert into user_group values (2,'Two');
insert into notif_user values (1,1);
insert into notif_user values (1,2);
insert into notif_user values (2,1);
insert into notif_user values (3,2);
insert into notif_user values (4,1);
insert into notif_user values (4,2);
insert into notif_group values (1,1);
insert into notif_group values (1,2);
insert into notif_group values (2,1);
insert into notif_group values (3,2);
insert into notif_group values (5,1);
select * from notification where status = 0 and (
 notif_id in (select notif_id from notif_user where user_id = 1) OR 
 notif_id in (select notif_id from notif_group where group_id = 1))


notif_id
name
status


1
Notif1
0

2
Notif2
0

4
Notif4
0

5
Notif5
0