ЕСТЕСТВЕННОЕ ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ или ИСПОЛЬЗОВАНИЕ, если общий атрибут равен NULL

Итак, если таблица A:

no | username 
1  | admin
2  | chicken

И таблица B:

id | no
a  | 1
b  | 3
c  | 4

Затем я делаю ЕСТЕСТВЕННОЕ ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ следующим образом:

SELECT no
FROM A NATURAL FULL OUTER JOIN
     B;

Тогда каков результат? Одинаков ли результат для всех реализаций PostgreSQL?

Поскольку «нет» исходит от таблицы А или таблицы Б, это двусмысленно. Но ЕСТЕСТВЕННЫЕ соединения сочетают в себе «нет». Но что, если одно из «нет» неоднозначно, т. е. A.no НЕ НУЛЕВОЕ, а B.no IS NULL, какое из «нет» он выбирает? А что, если A.no и B.no оба равны NULL?

TL;DR: Итак, вопрос в том, КАКОЕ значение нет в SELECT no: это A.no или B.no, или это их СОВЕЩАНИЕ?

SELECT no
FROM A NATURAL FULL OUTER JOIN
     B;

postgresql null join natural-join
person James Chen    schedule 04.12.2016    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Во-первых, не используйте natural для соединений. Это ошибка, ожидающая своего появления. Как вы заметили в своем вопросе, natural выбирает ключи объединения на основе имен столбцов. Он не принимает во внимание типы. Он даже не принимает во внимание явно объявленные отношения внешнего ключа.

Однако особенно коварная проблема заключается в том, что кто-то, кто читает запрос, не видит ключей соединения. Это значительно усложняет отладку запросов или их изменение/улучшение.

Итак, мой совет — использовать вместо этого using.

SELECT no
FROM A FULL OUTER JOIN
     B
     USING (no);

Что возвращает full join? Он возвращает все строки из обеих таблиц, независимо от того, совпадает ли соединение или нет. Поскольку сравнение NULL всегда терпит неудачу, NULL не будет соответствовать условиям соединения.

Например, следующий запрос возвращает 4 строки, а не 2, содержащие значение NULL:

with x as (
      select NULL::int as id union all select NULL as id
     )
select id
from x full join
     x y
     using (id);

Вы получите тот же результат с соединением natural, но я просто не использую эту конструкцию.

Я не уверен на 100%, но почти уверен, что все версии Postgres, поддерживающие full join, будут работать одинаково. Это поведение получено специально из определений ANSI joins и условий соединения.

person Gordon Linoff    schedule 04.12.2016
comment
Спасибо за подробный и проницательный ответ, но я разместил этот вопрос по академическим причинам. Я знаю, что ЕСТЕСТВЕННОЕ СОЕДИНЕНИЕ лениво и плохо, так как плохо адаптируется к изменениям схемы. Но при естественном соединении FULL OUTER, если совпадение соединения не удается, тогда это сделает один из «нет» NULL, может быть, это A.no, но, возможно, это B.no. В таком случае, какова ценность «нет»? Это их ОБЪЕДИНЕНИЕ или что-то другое? Мой вопрос действительно в том, какова ценность «нет»? т.е. нет в SELECT no FROM ... - person James Chen; 04.12.2016
comment
Я также заранее извиняюсь за мою плохую формулировку, надеюсь, это может иметь смысл. ^^ - person James Chen; 04.12.2016
comment
@ДжеймсЧен. . . Если значение NULL в одной из таблиц, то будет возвращено NULL. - person Gordon Linoff; 04.12.2016
comment
Я только что попробовал, и «нет» вернул ответ, отличный от NULL. Я пробовал SELECT no FROM A NATURAL FULL OUTER JOIN B WHERE B.no IS NULL; и весь вывод «нет» не равен NULL. Мне любопытно узнать, как PostgreSQL выбирает, какое «нет» выводить в этом неоднозначном случае. - person James Chen; 04.12.2016
comment
Похоже, что Postgres добавляет подразумеваемый COALESCE(a.no,b.no) только для NATURAL FULL OUTER JOIN. Для обычного FROM a FULL OUTER JOIN B ON a.no = b.no вы получаете ошибку ambiguous column reference. (но опять же: не используйте NATURAL) - person wildplasser; 04.12.2016
comment
@wildplasser: неявное объединение в столбцах соединения также выполняется, когда вы присоединяетесь через предложение using - person a_horse_with_no_name; 04.12.2016
comment
@wildplasser Спасибо, похоже, но я не уверен. Есть ли официальные доказательства того, что PostgreSQL делает это? Я не могу найти его в документации PostgreSQL? - person James Chen; 04.12.2016
comment
@a_horse_with_no_name : я не думал об ИСПОЛЬЗОВАНИИ. Не знаю, продиктовано ли это стандартом SQL, но кажется имеет смысл. @ Джеймс Чен: я не знаю (и на самом деле мне все равно, так как я никогда не использую эти конструкции ;-) - person wildplasser; 04.12.2016
comment
@ДжеймсЧен. . . Мне не хватает твоей точки зрения. В ваших данных ни одно из значений no в таблицах не равно null. Предложение using и natural join оба принимают значение no из таблицы со значением. Следовательно, вы можете получить значение NULL для ключа соединения только тогда, когда значение равно NULL в одной или обеих таблицах. В моем примере значения равны NULL, поэтому на выходе появляется NULL. - person Gordon Linoff; 04.12.2016
comment
@GordonLinoff Поскольку это ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ, вы можете получить NULL. Если вы посмотрите, когда b.no = 4, когда этот полный внешний соединяется с a, тогда a.no = NULL, но b.no = 4. Я предполагаю, что «no» неявно объединяется, так что «no» равно 4 ( мой вопрос в том, действительно ли имеет место неявное слияние или нет). - person James Chen; 04.12.2016