Optimize duplicated but negated subquery
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))