I found posts about reusing results from subquery, but none of them mention about IN operator
My query looks like this:
select count(*)
from X
where ...
and X.id NOT IN (select id from Y)
and X.id IN (select id from Z
where ...
and Z.id IN (select id from Y)
)
As you see, the subquery select id from Y is repeated
How can I reuse the result of the subquery in the IN operator?