優化重複但否定的子查詢

我有一個我稱之為“哈姆雷特”的查詢形式：

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 <> 1 and a.Being not in (select Being from stuffThatIs where stuffThatIs.Name = b.Name)

最嵌套的查詢是相同的，所以我認為數據庫會重用結果而不是執行 2 個查詢。

但在執行計劃中，查詢顯示兩次，成本相同，均為 19%。

這可以手動優化嗎？還是添加了提示？我更喜歡攜帶式解決方案，而不是最大性能的解決方案。

所有命名列都有索引，有時甚至是唯一的。該範例已簡化，整個where子句本身處於連接條件等。因此請僅優化模式，而不是整個查詢。

也許這個查詢會更好？我們會檢查每一行的 stuffThatIs，然後驗證是否

1. ToBe=1和there is a.Being in stuffThatIs 或
2. ToBe<>1和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 "...a.Being in (select Being..."
) x
WHERE ((a.ToBe = 1 AND x.is_inside IS NOT NULL)
   OR (a.ToBe <> 1 AND x.is_inside IS NULL))

我認為這可能有效……除非您期望來自 stuffthatis 的多個重複項（在這種情況下，請嘗試加入 SELECT DISTINCT 的子查詢）。

select A.*, B.*
from stuff a
left join otherStuff b on (a.x=b.x) -- lots of joins ommited
LEFT JOIN stuffthatis c on b.name = c.name

where
(b.ToBe = 1 and a.Being = C.being)
or
(b.ToBe <> 1 and C.name is null)

引用自：https://dba.stackexchange.com/questions/278068