Здесь минимальная настройка с двумя таблицами a
и b
, каждая с тремя строками:
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
в поиске 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.
Был обсуждение этой проблемы, но он напрямую не отвечает на указанный выше вопрос.
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
.
Я просто решил такой случай, заменив условие ON на “TRUE” и переместив исходное условие “ON” в предложение WHERE. Я не знаю, как это повлияло на производительность.