外部自聯接過濾器與子查詢

我有一個儲存有關門票資訊的表格。售票時有一條記錄，使用時有一條記錄。有一個名為 TransType 的列設置為“已售出”或“已使用”以標記它是哪一個。表中還有其他列，其中一些列在銷售時包含值，但在使用時不包含值，反之亦然。該表實際上是數據倉庫風格的事實表。

在其他事情中，我正在計算銷售和使用之間的時間差，因此我將表加入到自身中，以便為每張票獲取一個記錄，以便能夠在同一記錄中計算兩個事件的時間戳。

我需要包括所有售出的門票，所以外部連接應該可以解決這個問題。

首先我執行了這個查詢

select x.* 
from factI as x
left join factI as y on x.tickedId = y.tickedId
where x.TransType = 'sold'
and y.TransType = 'used'

當我執行它時，過濾器 x.TransType = ‘sold’ 不起作用，查詢返回所有記錄的結果，不管 TransType 是什麼。如果我使用內部連接，這可行，但顯然不會退回未使用的票。

所以我將查詢更改為這個給我正確結果的查詢。

select * from (
  select * from factI where TransType = 'sold'
) as x
left join (
  select * from factI where TransType = 'used'
) as y on x.ticketId = y.ticketId

當我使用外部（左）聯接時，為什麼第一個查詢中的 where 子句沒有正確過濾掉？

您的第一個查詢是inner join因為y.TransType = 'used'使用正確表的條件在where子句中。

只需將該條件移動到on子句，就可以在沒有派生表的情況下重寫您的第二個查詢：

select x.*, y.*
from factI as x
left join factI as y on  x.tickedId = y.tickedId
                    and y.TransType = 'used'
where x.TransType = 'sold' ;

在您的第一個查詢中，在where子句中，您將結果限制為 where y.TransType = 'used'。這會將左連接變成內連接，因為您在y.TransType為空的地方（也就是不存在其他票證的地方）丟棄了結果。

一個簡單的解決方法是將該條件移動到這樣的on子句中：

select x.* 
from factI as x
left join factI as y on x.tickedId = y.tickedId
   and y.TransType = 'used' -- moved the condition to the on clause
where x.TransType = 'sold'

作為一般規則，我在on子句而不是where子句中保留外連接列的條件。否則你必須檢查null：

select x.* 
from factI as x
left join factI as y on x.tickedId = y.tickedId
where x.TransType = 'sold'
and (y.TransType = 'used' or y.TransType is null)

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