为什么 PostgreSQL 不能做这个简单的 FULL JOIN 呢？

这是包含 2 个表的最小设置a and b每行 3 行：

CREATE TABLE a (
    id SERIAL PRIMARY KEY,
    value TEXT
);
CREATE INDEX ON a (value);

CREATE TABLE b (
    id SERIAL PRIMARY KEY,
    value TEXT
);
CREATE INDEX ON b (value);

INSERT INTO a (value) VALUES ('x'), ('y'),        (NULL);
INSERT INTO b (value) VALUES        ('y'), ('z'), (NULL);

这是一个按预期工作正常的 LEFT JOIN：

SELECT * FROM a
LEFT JOIN b ON a.value IS NOT DISTINCT FROM b.value;

与输出：

 id | value | id | value 
----+-------+----+-------
  1 | x     |    | 
  2 | y     |  1 | y
  3 |       |  3 | 
(3 rows)

将“LEFT JOIN”更改为“FULL JOIN”会出现错误：

SELECT * FROM a
FULL JOIN b ON a.value IS NOT DISTINCT FROM b.value;

错误：仅在可合并连接或可散列连接的连接条件下才支持 FULL JOIN

有人可以回答吗：

什么是“可合并连接或可哈希连接连接条件”以及为什么要连接a.value IS NOT DISTINCT FROM b.value不满足这个条件，但是a.value = b.value完全没问题吗？

似乎唯一的区别是 NULL 值的处理方式。自从value列在两个表中都建立了索引，运行EXPLAIN on a NULL查找与查找非值一样有效NULL:

EXPLAIN SELECT * FROM a WHERE value = 'x';
                                QUERY PLAN                                
--------------------------------------------------------------------------
 Bitmap Heap Scan on a  (cost=4.20..13.67 rows=6 width=36)
   Recheck Cond: (value = 'x'::text)
   ->  Bitmap Index Scan on a_value_idx  (cost=0.00..4.20 rows=6 width=0)
         Index Cond: (value = 'x'::text)


EXPLAIN SELECT * FROM a WHERE value ISNULL;
                                QUERY PLAN                                
--------------------------------------------------------------------------
 Bitmap Heap Scan on a  (cost=4.20..13.65 rows=6 width=36)
   Recheck Cond: (value IS NULL)
   ->  Bitmap Index Scan on a_value_idx  (cost=0.00..4.20 rows=6 width=0)
         Index Cond: (value IS NULL)

这已经使用 PostgreSQL 9.6.3 和 10beta1 进行了测试。

已经有关于这个问题的讨论 https://www.postgresql.org/message-id/20060313104028.GB6714@svana.org，但它并没有直接回答上述问题。

PostgreSQL 实现FULL OUTER JOIN使用哈希或合并连接。

要获得此类加入的资格，加入条件必须采用以下形式

<expression using only left table> <operator> <expression using only right table>

现在你的加入条件does看起来像这样，但是PostgreSQL没有特殊的IS NOT DISTINCT FROM运算符，因此它将您的条件解析为：

(NOT ($1 IS DISTINCT FROM $2))

并且这样的表达式不能用于散列或合并连接，因此会出现错误消息。

我可以想出一种方法来解决它：

SELECT a_id, NULLIF(a_value, '<null>'),
       b_id, NULLIF(b_value, '<null>')
FROM (SELECT id AS a_id,
             COALESCE(value, '<null>') AS a_value
      FROM a
     ) x
   FULL JOIN
     (SELECT id AS b_id,
             COALESCE(value, '<null>') AS b_value
      FROM b
     ) y
      ON x.a_value = y.b_value;

那行得通，如果<null>没有出现在任何地方value列。