Блог

У меня есть таблица статусов проверки качества для рецептов, и я хочу выбрать все записи «отбросить», включая информацию из «идеальных» статусов, где они перекрываются. Однако я получаю только пересечение, и я хочу, чтобы все записи «отбрасывались».

Я хочу выполнить левое соединение, которое даст мне все «отбрасываемые» строки из таблицы recipe_qa, объединенные с любыми «идеальными» строками.

 select *
from recipe_qa as bad
left join recipe_qa as good on good.id = bad.id
where bad.type = 'discard'
and good.type = 'perfect'
 

Приведенный выше запрос возвращает строки, в которых есть «идеальная» и «отброшенная» запись (24 строки) для идентификатора. Что я хочу, так это запрос, который выдаст мне все строки ‘discard’ (76 строк) либо с ‘perfect’ id, либо с null, где нет соответствующей строки.

Вот скрипка: http://sqlfiddle.com /#!2/faa49/4

Что я делаю не так?

Проще говоря, ваше предложение where устраняет их.

При работе с левыми (или внешними) соединениями вы должны учитывать, когда накладывается ограничение на данные, а когда создается декартово.

Допустим, вам нужны все записи из одной таблицы (A) и только те, которые совпадают в другой (B). При выполнении соединения в B будут присутствовать нулевые значения (не совпадающие записи с A). Добавление ограничивающего критерия (предложения where) к полям B фактически приведет к удалению записей, которые вы хотите получить из A; поскольку предложение where выполняется ПОСЛЕ объединения. Это имеет тот же эффект, как если бы вы начали с ВНУТРЕННЕГО СОЕДИНЕНИЯ! (в данном случае хорошо.type = ‘perfect’ удалит все те записи, которые являются плохими.type = ‘discard’, потому что когда good.id не существует для bad.id , хорошо.тип будет нулевым, а не «идеальным»; таким образом, where устраняет такие записи)

Этой ситуации можно избежать, просто переместив ограничивающие критерии в таблице B в объединение при использовании внешних соединений. Таким образом, декартово значение генерируется ПОСЛЕ наложения ограничения, гарантирующего, что «Все записи» из таблицы A остаются всеми записями. Если не в соединении, как вы видели, ограничение вводится после удаления декартовых и, следовательно, нулевых значений, и вы больше не получаете «Все записи», поэтому ЛЕВОЕ соединение отрицается. Это как если бы вы выполняли ВНУТРЕННЕЕ соединение в первую очередь. Вот почему выполнение оператора OR для возврата нулей и значения также работает, но только если это столбец NOT NULL (или тип null не имеет семантического значения), как указывает LC в комментариях ниже.

В этом случае ваше предложение where хорошо.type устраняет результаты левого соединения, поэтому либо добавьте критерии к объединению, которые устанавливают ограничения до создания декартовой системы (позволяя существовать нулям)

 select *
from recipe_qa as bad
left join recipe_qa as good 
  on good.id = bad.id
 and good.type = 'perfect'
where bad.type = 'discard'
 

http://sqlfiddle.com /#!2/faa49/8/0

ИЛИ используйте условие is null, чтобы не исключать записи из левого соединения. Это сопряжено с некоторыми рисками, указанными в приведенных ниже комментариях.

 select *
from recipe_qa as bad
left join recipe_qa as good 
  on good.id = bad.id
where bad.type = 'discard'
 and (good.type = 'perfect' or good.type is null)