內連接僅適用於非空值

我可以使用 where join 和下一個 sql 獲得正確的結果：

WITH fktable1 AS (
SELECT 2 AS id
)
,fktable2 AS (
SELECT 3 AS id
)
SELECT main.*
FROM
-- main data
   (
   VALUES
   (1, NULL, NULL),
   (2, 2, NULL),
   (3, NULL, 3),
   (4, 2, 3),
   (5, 4, NULL), -- wrong, not exist fkcol1 in the fktable1
   (6, NULL, 5) -- wrong, not exist fkcol2 in the fktable2
   ) AS main (col1, fkcol1, fkcol2)
   , fktable1, fktable2
WHERE 
   (
       fktable1.id = main.fkcol1
       OR main.fkcol1 is NULL
   )
   AND (
       fktable2.id = main.fkcol2
       OR main.fkcol2 is NULL
   );

它給了我輸出：

col1 | fkcol1 | fkcol2 
------+--------+--------
   1 |        |       
   2 |      2 |       
   3 |        |      3
   4 |      2 |      3
(4 rows)

有什麼方法可以通過表連接語句獲得相同的結果嗎？我試圖找到解決方案並閱讀許多存在的問題，但沒有成功。

您有一個帶有條件的內部連接：

WITH fktable1 (id) AS (
 values (2)
)
,fktable2 (id) AS (
 values (3)
)
SELECT main.*
FROM (
 VALUES
   (1, NULL, NULL),
   (2, 2, NULL),
   (3, NULL, 3),
   (4, 2, 3),
   (5, 4, NULL), 
   (6, NULL, 5) 
) AS main (col1, fkcol1, fkcol2)
 join fktable1 f1 on f1.id = main.fkcol1 or main.fkcol1 is null
 join fktable2 f2 on f2.id = main.fkcol2 or main.fkcol2 is null

與a_horse_with_no_name的答案略有不同的是：

WITH fktable1 (id) AS (
 values (2)
)
,fktable2 (id) AS (
 values (3)
)
SELECT main.*
FROM (
 VALUES
   (1, NULL, NULL),
   (2, 2, NULL),
   (3, NULL, 3),
   (4, 2, 3),
   (5, 4, NULL), 
   (6, NULL, 5) 
) AS main (col1, fkcol1, fkcol2)
 join fktable1 f1 
     on coalesce(main.fkcol1, f1.id) = f1.id
 join fktable2 f2 
     on coalesce(main.fkcol2, f2.id) = f2.id

該ON子句有點短，但可能不那麼明顯。這個想法是，如果 main.fkcol1 為空，則 f1.id 與自身進行比較。請注意，如果 id 可以為空，則這是無效的，因此a_horse_with_no_name的解決方案也更安全一些。

為了使它成為空安全的，像這樣的結構：

coalesce(main.fkcol1, f1.id, -1) = coalesce(f1.id, -1)

或者：

coalesce(main.fkcol1, f1.id) IS NOT DISTINCT FROM f1.id

可以使用

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