Самообъединение таблиц

Аналогично объединению таблицы с другой таблицей, можно объединить таб­лицу саму с собой. Предположим, что вы хотите выяснить имена служащих отдела, в котором работает Нора Эдварде. Для этого необходимо найти в таблице employee номер отдела (departmentID), в котором работает Нора, а затем посмотреть в таблице employee, кто еще работает в том же отделе.

Это можно сделать так:

select e2.name

from employee el, employee e2

where el.name = 'Нора Эдварде'

and el.departmentID = e2.departmentID;

В этом запросе для таблицы employee мы определили два разных псев­донима. По сути мы сообщили системе MySQL, что хотели бы иметь две от­дельных таблицы, el и е2, которые должны содержать одни и те же данные. После этого мы объединяем их так же, как любые другие таблицы. Теперь сна­чала находим строку Норы в таблице el (el. name=' Нора Эдварде '), а затем в таблице е2 ищем строки с тем же значением номера отдела, что и у Норы (el.departmentID = e2. departmentID).

Ниже приведены результаты предыдущего запроса

Здесь представлены все служащие, работающие в одном отделе с Норой, включая и саму Нору Эдварде. Очень просто добавить условие, которое исключит ее из результирующего списка:

select e2.name

from employee el, employee e2

where el.name = 'Нора Эдварде'

and el.departmentID = e2.departmentID

and e2.name!= 'Nora Edwards';

Типы объединений

Существуют различные типы объединений, варианты которых могут использоваться в MySQL.

Основное объединение

В предыдущей теме мы упомянули понятие декартова произведения. В дан­ном контексте его иногда называют полным или перекрестным объединением, но, независимо от терминологии, такое объединение возвращает полный набор ком­бинаций. При добавлении к объединению некоторого условного выражения (типа employee. departmentID = department. departmentID) мы превраща­ем его в нечто, называемое объединением по эквивалентности, которое ограни­чивает число возвращаемых запросом строк.

До сих пор мы использовали набор таблиц, перечисленных в выражении FROM и разделенных запятыми. Это определяло, как было показано выше, перекрестное объединение, которое превращалось в объединение по эквивалентности с помо­щью выражения WHERE. MySQL предлагает различные формы синтаксиса для объединений такого типа.

Рассмотрим наш оригинальный запрос:

select employee.name, department.name

from employee, department

where employee.departmentID = department.departmentID;

Вместо запятой в выражении FROM можно использовать ключевое слово JOIN:

select employee.name, department.name

from employee join department

where employee.departmentID = department.departmentID;

Вместо JOIN точно так же можно использовать CROSS JOIN (перекрестное объединение) или INNER JOIN (внутреннее объединение).

При использовании этого типа объединения MySQL находит связываемые таб­лицы и пытается объединить их оптимальным образом, не обязательно в том порядке, в котором их перечислите вы. Если вы хотите отменить оптимизацию и заставить MySQL связать таблицы в указанном вами порядке, замените слово JOIN выражением STRAIGHT JOIN (непосредственное объединение).

Левые и правые объединения

Рассмотрим, например, ситуацию, когда требуется выяснить имена служащих, еще не получавших внешних заданий, т.е. служащих, коды которых (emp1оуееID) отсутствуют в таблице (assignment). Решить такую задачу можно с помощью LEFT JOIN, как показано ниже.

select employee.name

from employee left join assignment

on employee.employeelD = assignment.employeelD

where clientID is null;

Легко подтвердить правильность полученного результата с помощью непо­средственной проверки таблиц, но почему и как этот запрос работает Левое объединение заставляет взять левую таблицу в объединении (в данном случае это таблица employee) и попытаться найти соответствия среди строк правой таблицы (здесь это таблица assignment). Соответствующие строки раз­мещаются рядом с левой таблицей. Для строк левой таблицы, не имеющих со­ответствующих строк в правой таблице, LEFT JOIN создает строки, состоящие из значений NULL. Строки левой таблицы, не имеющие соответствующих строк в правой таблице, можно найти с помощью поиска по ключу NULL.

Давайте снова вернемся к нашему примеру. В указанном объединении для каждого служащего, имеющего задание, мы получим строку с информацией о служа­щем и задании. Если служащий не имеет соответствующей строки в таблице заданий, левое объединение добавит "фиктивную строку", состоящую из одних значе­ний NULL. Такие фиктивные строки можно найти с помощью поиска служащих, для которых полученные задания имеют значение NULL в поле clientID. (Поле clientID является ключевым полем, поэтому в самой таблице assignment такое просто невозможно.)

В данном примере мы использовали LEFT JOIN, но точно так же можно использовать RIGHT JOIN, которое работает аналогично, но использует в качестве основной правую таблицу, а вместо отсутствующих строк левой таблицы создает строки значений NULL.