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

Question

这是一个最小的设置，a 2 个表，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);

这是一个左联接，可以按预期正常工作：

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)

将"左联接"更改为"完全联接"会给出错误：

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

错误：完全联接仅支持合并联接或哈希联接连接条件

有人可以回答：

什么是"可合并连接或哈希可连接连接条件"，为什么在a.value IS NOT DISTINCT FROM b.value上加入不满足此条件，但a.value = b.value完全没问题？

似乎唯一的区别是如何处理 NULL 值。由于value列在两个表中都编制了索引，因此对NULL查找运行EXPLAIN与查找非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中进行了测试。

关于这个问题已经有讨论，但它并没有直接回答上述问题。

Answer 1

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

要获得此类联接的资格，联接条件必须具有以下形式

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

现在你的连接条件看起来像这样，但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列中的任何位置，则此方法有效。

Answer 2

我刚刚通过将 ON 条件替换为"TRUE"并将原始的"ON"条件移动到 WHERE 子句中来解决这种情况。不过，我不知道这会影响性能。