in a project, there have frequence table(a,b,c) as condition.
and the target query result is p, relative info table is m

p:m is 1:many

there hava many table look like p(p1,p2,p3,p4) and m(m1,m2,m3,m4)

CREATE TABLE `p` (
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
-- other fileds
);

CREATE TABLE `m` (
  `id` int NOT NULL AUTO_INCREMENT,
  `a_id` int DEFAULT NULL,
  `b_id` int DEFAULT NULL,
  `c_id` int DEFAULT NULL,
  `p_id` int DEFAULT NULL,
-- other fileds
  PRIMARY KEY (`id`),
  KEY `m_FK` (`a_id`),
  KEY `m_FK_1` (`b_id`),
  KEY `m_FK_2` (`c_id`),
  CONSTRAINT `m_FK` FOREIGN KEY (`a_id`) REFERENCES `a` (`id`),
  CONSTRAINT `m_FK_1` FOREIGN KEY (`b_id`) REFERENCES `b` (`id`),
  CONSTRAINT `m_FK_2` FOREIGN KEY (`c_id`) REFERENCES `c` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE `a` (
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
-- other fileds
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE `b` (
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
-- other fileds
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE `c` (
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
-- other fileds
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

a, b, c involve queries, for Temporary Tables solution, its looks like

CREATE TABLE `a_after` ( `id` int NOT NULL , PRIMARY KEY (`id`)) ENGINE=InnoDB select a.id from a ....; -- there are other join with omitted conditions
CREATE TABLE `b_after` ( `id` int NOT NULL , PRIMARY KEY (`id`)) ENGINE=InnoDB select a.id from a ....; 
CREATE TABLE `c_after` ( `id` int NOT NULL , PRIMARY KEY (`id`)) ENGINE=InnoDB select a.id from a ....;

select * from p 
where exists(
select * from m
where m.p_id = p.id
and a_id in (select id from a_after) 
and b_id in (select id from b_after)
and c_id in (select id from c_after)
)

In actual scene,

a_after’s data range is hundreds to twenty thousand

b_after’s data range is hundreds to Five thousand

c_after’s data range is hundreds to Five thousand

i wonder know in such case, should it suitable for use cte, for example,

with 
a_after as (select a.id from a ....),
b_afteras (select a.id from b ....),
c_after as (select a.id from c ....)
select * from p 
where exists(
select * from m
where m.p_id = p.id
and a_id in (table a_after) 
and b_id in (table b_after)
and c_id in (table c_after)
)

so i could reuse them as a sql fragment to include the cte and subquery in mutilple, when a’s condition is pass, so i use include a_after as cte, and append a_id in (table a_after) , all i need is just keep the alias is identical