Optimize duplicated but negated subquery I have what I call a 'Hamlet' query in the form:
select * from stuff a
left join otherStuff b on (a.x=b.x) -- lots of joins ommited
where
 (b.ToBe = 1 and a.Being in (select Being from stuffThatIs where stuffThatIs.Name = b.Name)
 or
 (b.ToBe &lt;&gt; 1 and a.Being not in (select Being from stuffThatIs where stuffThatIs.Name = b.Name)

The most-nested queries being identical, so I thought the database would reuse the result instead of doing 2 queries.
But in the execution plan the query shows twice with the same cost of 19%.
Can this be optimized manually? Or a hint added?
I'd prefer a portable solution instead of a maximum perf one.
There are indexes on all named columns, sometimes even unique. The example is simplified, the whole where clause is in a join condition itself etc. So please optimize just the pattern, not the whole query.
Maybe this query would be better?
We would check stuffThatIs for every row, and then validate if

ToBe=1 and there is a.Being in stuffThatIs  OR
ToBe&lt;&gt;1 and there isn't a.Being in stuffThatIs

SELECT *
FROM someStuff a
LEFT JOIN otherStuff b ON (a.x = b.x)
OUTER APPLY (
  SELECT 1 [is_inside]
  WHERE EXISTS(SELECT *
               FROM stuffThatIs x
               WHERE x.Name = b.Name  
                 AND x.Being = b.Being) -- instead of &quot;...a.Being in (select Being...&quot;
) x
WHERE ((a.ToBe = 1 AND x.is_inside IS NOT NULL)
    OR (a.ToBe &lt;&gt; 1 AND x.is_inside IS NULL))