我可以寫一個沒有 OR IS NULL 的 FULL OUTER JOIN 嗎？

這裡有一些數據可以玩：

CREATE TABLE a (
   a_id   int  NOT NULL,
   a_prop text NOT NULL
);

CREATE TABLE b (
   b_id   int  NOT NULL,
   b_prop text NOT NULL
);

INSERT INTO a VALUES (1, 'blah'), (2, 'blah'), (4, 'not this one');
INSERT INTO b VALUES (1, 'blah'), (3, 'blah'), (5, 'not this one');

現在我想寫一個返回的查詢：

一種可能性是：

SELECT *
FROM a
FULL OUTER JOIN b ON a_id = b_id
WHERE (a_prop = 'blah' OR a_prop IS NULL)
AND (b_prop = 'blah' OR b_prop IS NULL);

這要求我為OR ... IS NULL我有條件的每個領域都寫。如果某些條件是日期範圍等，這將變得更加冗長。

如果這是左連接：

SELECT *
FROM a
LEFT JOIN b ON a_id = b_id
WHERE a_prop = 'blah'
AND (b_prop = 'blah' OR b_prop IS NULL);

我可以將條件移至ON子句以避免這種情況：

SELECT *
FROM a
LEFT JOIN b ON a_id = b_id AND b_prop = 'blah'
WHERE a_prop = 'blah';

有沒有辦法用完整的外部連接來做到這一點？

小提琴

沒有OR IS NULL？

col = 'x' OR col IS NULL

原來的：

SELECT *
FROM   a
FULL   JOIN b ON a_id = b_id
WHERE (a_prop = 'blah' OR a_prop IS NULL)
AND   (b_prop = 'blah' OR b_prop IS NULL);

使用col <> x IS NOT TRUE：

SELECT *
FROM   a
FULL   JOIN b ON a_id = b_id
WHERE  a_prop <> 'blah' IS NOT TRUE
AND    b_prop <> 'blah' IS NOT TRUE;

或加入前過濾：

SELECT *
FROM      (SELECT * FROM a WHERE a_prop = 'blah') a
FULL JOIN (SELECT * FROM b WHERE b_prop = 'blah') b ON a_id = b_id;

col <> 'x' OR col IS NULL

問題的第一個版本要求這個謂詞。

原來的：

SELECT *
FROM a
FULL OUTER JOIN b ON a_id = b_id
WHERE (a_prop <> 'not this one' OR a_prop IS NULL)
AND   (b_prop <> 'not this one' OR b_prop IS NULL);

使用col IS DISTINCT FROM 'x'：

SELECT *
FROM   a
FULL   JOIN b ON a_id = b_id
WHERE  a_prop IS DISTINCT FROM 'not this one'
AND    b_prop IS DISTINCT FROM 'not this one';

或加入前過濾：

SELECT *
FROM      (SELECT * FROM a WHERE a_prop <> 'not this one') a
FULL JOIN (SELECT * FROM b WHERE b_prop <> 'not this one') b ON a_id = b_id;

db<>fiddle here - 顯示所有

旁白：而不是!=我使用<>，這是 SQL 中的標準運算符。（!=是 Postgres 中公認的別名。）

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