Bezdepbonusu7Программирование: статьи и ответы
Меню

Вопрос

Почему это левое внешнее соединение дает неправильные результаты?

Автор вопроса: JimОтветов: 1

Я запутался в следующем SQL (возможно, тривиальном) на OUTER JOIN.
У меня есть 2 небольшие таблицы emp и deptno, т.е. в которых есть записи для сотрудников и их отделов. Их отношения, очевидно, 1-N (но это не имеет значения).
Я пытаюсь использовать внешнее соединение, чтобы найти отдел, в котором нет работающих сотрудников. Поэтому я подумал, что правильным решением будет OUTER JOIN.
Если я сделаю следующее:

select d.deptno as d_deptno, e.deptno as e_deptno
from dept d left outer join  emp e
on d.deptno = e.deptno;

Я получил:

d_deptno    e_deptno
10,         10
10,         10
10,         10
20,         20
20,         20
20,         20
20,         20
20,         20
30,         30
30,         30
30,         30
30,         30
30,         30
30,         30
40,         null

Итак, я подумал, что мне нужна только последняя строка, поэтому мне нужно только изменить свой запрос следующим образом:

select d.deptno as d_deptno, e.deptno as e_deptno
from dept d left outer join  emp e
on d.deptno = e.deptno and e.deptno is null;

т.е. Я добавил and e.deptno is null. По какой-то причине, если бы я сделал e_deptno is null, запрос не смог бы быть проанализирован (почему?)
Но результаты, которые я получил, были следующими!

d_deptno  e_deptno
10,       null
20,       null
30,       null
40,       null

Почему я получаю эти результаты? Что я неправильно понимаю с OUTER JOINs?

  • вы должны поставить второе условие, где пункт where e.deptno is null chetan 07.07.2013
  • Кроме того, вы не можете использовать псевдоним в условии where. chetan 07.07.2013
  • @chetan: Значит, я не могу сделать join on and or etc? Jim 07.07.2013
  • да, вы можете присоединиться к .. и... или .. и т. д., если у вас есть несколько условий соединения, но вы должны поставить буквальное сравнение в предложении where chetan 07.07.2013
  • @chetan: я не уверен, что буквальное сравнение отличается от того, что не должно быть частью условия join Jim 07.07.2013
  • Прочитайте этот (предупреждение: длинный) ответ: Использование IS NULL или IS NOT NULL в условиях соединения - теоретический вопрос, особенно часть, выделенная жирным шрифтом: Я хочу сказать, что эти значения NULL создаются (в результирующем наборе) во время LEFT OUTER JOIN. Null, который вы видите в результатах 1-го запроса, в строке: 40, null создается внешним соединением. Его нет в таблицах. ypercubeᵀᴹ 07.07.2013

sqljoinleft-joinouter-join

Ответы

Ответ 1 · рейтинг 2 · Community

Условие e.deptno is null должно быть в предложении where:

select d.deptno as d_deptno, e.deptno as e_deptno
from dept d left outer join  emp e
on d.deptno = e.deptno
where e.deptno is null

Это связано с тем, что предложение on использует условия, указанные в качестве критериев для соединения строк из одной таблицы в другую, поэтому оно будет ссылаться только на emp записей, которые имеют значение null deptno и одновременно deptno, соответствующие записи dept.

Поскольку эти критерии являются взаимоисключающими, запрос никогда не будет успешно связан с записью emp, поэтому внешнее соединение гарантирует, что для значений emp будут возвращены пустые значения.

Предложение where применяется после условий соединения, поэтому перемещение условия is null в предложение where гарантирует, что будут выбраны только dept записи без соответствующих emp записей.

e_deptno is null недействителен в этом запросе ни в предложении on, ни в предложении where, потому что e_deptno определяется только в предложении select (после применения on, where и любой группировки) - вероятно, это было бы однако допустимо в предложении с наличием (в MySQL).

Перевод вопроса и ответов сообщества Stack Overflow, лицензия CC BY-SA. Оригинал на Stack Overflow.

Похожие материалы