Полное декартово произведение таблиц
На практике чаще всего приходится делать запросы, основанные на нескольких таблицах сразу.
Построим команду SELECT, перечислив в инструкции FROM таблицы через запятую:
SELECT список_полей FROM таблица1, таблица2;
В результате получится третья таблица, где каждая строка таблицы1 соединена с каждой строкой таблицы2. Полученная таблица является полным декартовым произведением двух таблиц.
Полное декартово произведение обычно никакой полезной информации не несет, потому что соединяет строки двух таблиц совершенно бессмысленным образом. Изредка бывают случаи, когда его целесообразно использовать. Гораздо чаще для формирования информативных запросов требуется построение подмножества декартова произведения двух таблиц.
Соединение таблиц
Фактически перед выполнением запроса нужно построить в оперативной памяти новую таблицу, в которой соединены строки исходных таблиц на основе связей по первичным и внешним ключам. То есть нам нужны не все множество пар строк из двух таблиц, а только часть этого множества – подмножество связанных по ключу строк.
Для построения подмножества декартова произведения двух таблиц используются три оператора:
- JOIN (соединение);
- LEFT JOIN (левое соединение);
- RIGHT JOIN (правое соединение).
Общий вид запроса с соединением выглядит следующим образом:
Рассмотрим различия между тремя видами соединений на следующем примере.
В базе данных имеется две таблицы. В таблице abiturient хранятся личные данные абитуриентов. В таблице application хранятся поданные абитуриентами заявления. Каждый абитуриент может подать несколько заявлений, поэтому таблица application должна содержать внешний ключ со ссылкой на таблицу abiturient. Логическая модель показана на рисунке.
Необходимо построить запрос, результатом которого будет таблица, содержащая и данные абитуриента и данные всех его заявлений.
Если присмотреться к данным таблиц, то можно увидеть, что результат может быть различным в зависимости от того, хочет ли пользователь видеть тех абитуриентов, которые зарегистрировались, но еще не успели подать заявления. В рассматриваемом примере заявлений нет у абитуриентки Галкиной.
Если пользователь составит запрос с помощью оператора JOIN, то он будет выглядеть так:
SELECT application.idapplication, application.spec, application.forma, abiturient.idabiturient,
abiturient.fio, abiturient.birth_date, abiturient.age FROM application JOIN abiturient ON
application.idabiturient=abiturient.idabiturient;
А в результате получится следующая таблица:
Если пользователь составит запрос с помощью оператора LEFT JOIN, то запрос будет таким:
SELECT application.idapplication, application.spec, application.forma, abiturient.idabiturient,
abiturient.fio, abiturient.birth_date, abiturient.age FROM application LEFT JOIN abiturient ON
application.idabiturient=abiturient.idabiturient;
А в результате получится таблица:
Нетрудно заметить, что результаты отличаются одной строкой. В первом случае пользователь видит только тех абитуриентов, которые имеют хотя-бы одно заявление. Поэтому Галкина в таблицу не попала. Во втором случае пользователь видит всех абитуриентов и Галкину в том числе.
В этом и состоит разница между операторами JOIN и LEFT JOIN.
Выражение таблица1 JOIN таблица2 означает, что нужно брать только те записи из обеих таблиц, которые связаны по первичному и внешнему ключу. Если в какой-то таблице есть записи, не имеющие связанных в другой таблице, то эти записи в соединение не попадут.
Выражение таблица1 LEFT JOIN таблица2 означает, что нужно брать все записи из таблицы 1, а из таблицы 2 – только те, которые имеют в таблице 1 связанные записи. Если в таблице 2 есть записи, которые не имеют связанных в таблице1, то они не попадают в соединение.
Оператор RIGHT JOIN является полным аналогом LEFT JOIN, только он позволяет выбрать все данные из той таблицы, которая стоит справа от оператора. LEFT JOIN и RIGHT JOIN взаимозаменяемы на основе следующей формулы:
Из практики известно, что RIGHT JOIN выполняется несколько медленнее, чем LEFT JOIN, потому что в этом случае задействован немного другой алгоритм. Поэтому программисты стараются избегать RIGHT JOIN и заменять его на LEFT JOIN.
Объединение
В некоторых реализациях языка SQL присутствует оператор FULL JOIN, который позволяет объединить результаты, полученные посредством LEFT JOIN и RIGHT JOIN. Таким образом, результат содержит все записи обеих таблиц. Но в большинстве реализаций SQL такого оператора нет. Чтобы получить аналогичный результат нужно воспользоваться оператором UNION:
