with posts(id_post, txt) as (select 1, 'искомый пост: готовка, cooking, фильм' 
from dual union all select 2, 'тоже годен: готовка, cooking, фильм, лишний тег'
from dual union all select 3, 'негодный пост: cooking, фильм, без готовки'
from dual)
, tags(id_tag, tag_name) as (select 1, 'готовка'
from dual union all select 2, 'cooking'
from dual union all select 3, 'фильм'
from dual union all select 4, 'лишний'
from dual
)
, tags_posts(id_tag, id_post) as (select 1, 1
from dual union all select 2, 1
from dual union all select 3, 1
from dual union all select 1, 2
from dual union all select 2, 2
from dual union all select 3, 2
from dual union all select 4, 2
from dual union all select 2, 3
from dual union all select 3, 3
from dual
)

select * 
  from posts p
  where not exists( 
          select null 
            from lateral(select p.id_post, id_tag from tags where tag_name in ( 'готовка', 'cooking', 'фильм')
                          minus
                         select p.id_post, id_tag from tags_posts where id_post = p.id_post)
         )
;     

select * 
  from posts p
 where (select cast(collect(id_tag) as sys.ORA_MINING_NUMBER_NT) 
          from tags 
         where tag_name in ( 'готовка', 'cooking', 'фильм')
       )submultiset of
       (select cast(collect(id_tag) as sys.ORA_MINING_NUMBER_NT) 
          from tags_posts 
         where id_post = p.id_post)
;

select * from posts p
where (select count(distinct t.id_tag) 
           from tags t
          where t.tag_name in ( 'готовка', 'cooking', 'фильм')
         )=(select count(distinct t.id_tag) 
           from tags t
              , tags_posts tp 
          where t.id_tag=tp.id_tag 
            and tp.id_post=p.id_post 
            and t.tag_name in ( 'готовка', 'cooking', 'фильм')
         )
;