Присоединение таблиц в запросах — это базовый инструмент в работе с базами данных. Давайте рассмотрим какие присоединения (JOIN) бывают, и что от этого меняется в результатах запроса.
Для начала создадим две таблицы, над которыми будем проводить опыты. Это таблица с именами сотрудников и словарь с перечнем должностей.
Persons (Сотрудники)
Столбец position_ref (от «position reference») это ссылка на следующую таблицу, где перечислены должности сотрудников.
Positions (должности)
Т.е. чтобы узнать должность сотрудника в таблице Persons, нужно присоединить соответствующие данные из таблицы Positions, связывая их по значениям position_ref и id_pos.
Далее мы рассмотрим все варианты присоединений. Данные специально подобраны так, чтобы продемонстрировать отличия в результатах разных запросов.
INNER JOIN
Внутреннее присоединение. Равносильно просто JOIN или CROSS JOIN (верно для MYSQL, в стандарте SQL INNER JOIN не эквивалентен синтаксически CROSS JOIN, т.к. используется с выражением ON).
|
1 2 3 4 5 |
SELECT id_person, name, id_pos, title FROM `persons` INNER JOIN `positions` ON id_pos = position_ref |
Такое присоединение покажет нам данные из таблиц, только если условие связывания соблюдается — т.е. для сотрудника указан существующий в словаре идентификатор должности.
Если поменять порядок соединения таблиц — получим тот же результат.
Условно представим себе эти таблицы, как пересекающиеся множества, где пересечение — это наличие связи между таблицами. Получим картинку:
Далее проследим как получить разные части (подмножества) данного множества.
OUTER JOIN
Внешнее присоединение. Различают LEFT OUTER JOIN и RIGHT OUTER JOIN, и обычно опускают слово «OUTER».
Внешнее присоединение включает в себя результаты запроса INNER и добавляются «неиспользованные» строки из одной из таблиц. Какую таблицу использовать в качестве «добавки» — указывает токен LEFT или RIGHT.
LEFT JOIN
Внешнее присоединение «слева».
|
1 2 3 4 5 |
SELECT id_person, name, id_pos, title FROM `persons` LEFT OUTER JOIN `positions` ON id_pos = position_ref |
«Левая» таблица persons, содержит строку id_person#3 — «Александр», где указан идентификатор должности, отсутствующей в словаре. Мы увидим все записи из «левой» таблицы, тогда как правая будет присоединена по возможности.
На картинке это можно показать вот так:
RIGHT JOIN
Присоединение «справа».
|
1 2 3 4 5 |
SELECT id_person, name, id_pos, title FROM persons RIGHT OUTER JOIN positions ON id_pos = position_ref |
Тут данные из «левой» таблицы присоединяются к «правой».
Словарь должностей (правая таблица) содержит неиспользуемую запись с id_pos#3 — «программист». Теперь она попала в результат запроса.
Полное множество
MySQL не знает соединения FULL OUTER JOIN. Что если нужно получить полное множество?
Первый способ — объединение запросов LEFT и RIGHT.
|
1 2 3 4 5 6 7 8 9 10 11 |
(SELECT id_person, name, id_pos, title FROM persons LEFT OUTER JOIN positions ON id_pos = position_ref) UNION (SELECT id_person, name, id_pos, title FROM persons RIGHT OUTER JOIN positions ON id_pos = position_ref) |
При таком вызове UNION, после слияния результатов, SQL отсечет дубли (как DISTINCT). Для отсечения дублей SQL прибегает к сортировке. Это может сказываться на быстродействии.
Второй способ — объединение LEFT и RIGHT, но в одном из запросов мы исключаем часть, соответствующую INNER. А объединение задаём как UNION ALL, что позволяет движку SQL обойтись без сортировки.
|
1 2 3 4 5 6 7 8 9 10 11 |
(SELECT id_person, name, id_pos, title FROM persons LEFT OUTER JOIN positions ON id_pos = position_ref) UNION ALL (SELECT id_person, name, id_pos, title FROM persons RIGHT OUTER JOIN positions ON id_pos = position_ref WHERE id_person is NULL) |
Этот пример показывает нам как исключить пересечение и получить только левую или правую часть множества.
Левое подмножество
LEFT JOIN ограничиваем проверкой, что данных из второй таблицы нет.
|
1 2 3 4 5 6 |
SELECT id_person, name, id_pos, title FROM persons LEFT OUTER JOIN positions ON id_pos = position_ref WHERE id_pos is NULL |
В нашем примере — это специалисты, у которых не задана должность или нет должности с указанным ключом.
Правое подмножество
Аналогично выделяем правую часть.
|
1 2 3 4 5 6 |
SELECT id_person, name, id_pos, title FROM persons RIGHT OUTER JOIN positions ON id_pos = position_ref WHERE id_person is NULL |
В нашем случае получим должности, которые никому не назначены.
Всё кроме пересечения
Остался один вариант, тот когда исключено пересечение множеств. Его можно сложить из двух предыдущих запросов через UNION ALL (т.к. подмножества не пересекаются).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
(SELECT id_person, name, id_pos, title FROM persons LEFT OUTER JOIN positions ON id_pos = position_ref WHERE id_pos is NULL) UNION ALL (SELECT id_person, name, id_pos, title FROM persons RIGHT OUTER JOIN positions ON id_pos = position_ref WHERE id_person is NULL) |
Это запрос соберет все случаи, когда по какой то причине данные из таблиц не связаны.
А графически такое объединение выглядит следующим образом:
Откуда взялись сокращения p. ps. и pos.? Учитывая, что таблицы две Persons и Positions, не понятно что где и, главное, зачем сокращено.
Сокращать можно и нужно когда таблиц много и у них длинные имена. Тогда получается много текста и взглядом трудно понять что где. Сокращения помогут избавится от лишней писанины.
Это прийдет с опытом. После написания пару десятков сложных запросов с кучей join. Сами начнете сокращать.
Хорошая была бы заметка. Вот только вместе с борьбой за JOIN еще приходится распутывать загадки уважаемого автора с этими сокращениями. Две таблицы, для начинающих можно обойтись без умствований и загадочных сокращений.
Поддерживаю ;), пока пытаешься разобраться с основами забываешь зачем начал их смотреть перекидывая силы на расшифровку, которая к сожалению в предусловия не выписана.
Специально для «лиги лени» переписал статью.
Если вы действительно хотите разобраться с языком запросов, то надо посвятить этому какое то время. Лучше всего попробовать реализовать какие то практические задачи.
Ув. автор, вы зря переписали статью, надо было просто добавить строчку под * откуда взялось сокращение и для чего оно и все было бы сразу же понятно, вре равно сокр. придется использовать, просто надо было осветить этот момент и все.
Спасибо, все четко и понятно. Освежил знания!
«INNER JOIN
Внутреннее присоединение. Равносильно просто JOIN или CROSS JOIN.»
Мне кажется тут нужно поправить. CROSS JOIN, оно же декартово произведение, это совсем не INNER JOIN.
Спасибо за замечание! Здесь действительно необходимо уточнить, что эти три варианта синтаксически эквивалентны в MYSQL. Тогда как в SQL стандарте — INNER JOIN используется с выражением ON, а CROSS в прочих случаях.
Вообще половину не понял. Откуда взялась например левая таблица, правая таблица , что за муть ?? Было две таблицы зачем их рядом ставить на картинке и делать запрос из левой таблицы (вообще в голове вот конкретно это не укладывается. ). Что значит Внешнее присоединение «слева», присоединение чего и куда ??
Это самая подробная статья с объяснением и картинками, которую я видел), если тебе не понятно, что это и зачем, может это не твое, может оно тебе и не надо?)
Михаил, видимо, находится на одной из начальных фаз изучения вопроса. И тут можно сравнить это со стадиями принятия неизбежного. Т.е. он в «отрицании» или «гневе». Ребята из лиги лени дошли то «торга». Впереди еще возможна «депрессия», но я думаю он справится.
Автор, спасибо, конечно, за статью, но что за косяки такие с «<code>»? Оно же прямо в тексте примеров. Вы за собой не проверяете результат редактирования?
Вот что я вижу на странице в примерах:
…FROM
personsLEFT OUTER JOIN
positionsON ...Можете устранить пожалуйста? Выглядит очень неряшливо, а для непосвящённых так вообще дико.
Спасибо, Владимир, что заметили и маякнули мне! Поправил.
Спасибо, познавательно! :)
P.S.: дизайн поплыл что-то, а фон классный
Зачем вы разным цветом делаете строки, текст? сразу появляются вопросы и читать неудобно.
в последних запросах есть скобки, в ранних нет. поему нет комментария, зачем/почему?!
дислайк.
Да что там читать то, Андрей? Данных 7 строк на 2 таблицы.
Здорово живём. Верно ли понимаю, что «левой» принимается таблица, введённая/после оператором/-ра FROM (в примере это везде ‘persons’)? И уже если данные добавляются из неё — это LEFT JOIN, а ежели добавляется из другой («правой», упомянутой после JOIN) — RIGHT JOIN?
LEFT и RIGHT — указывают в присоединении какая таблица является основной. LEFT — таблица слева, RIGHT — та что справа. Я бы не называл сами таблицы «левой» или «правой», т.к. это атрибут присоединения (оператора JOIN), а не самой таблицы.
В целом, да верно — левая — та, что определена первой, как бы «слева» для языков, где принято писать слева-направо. И наоборот для правой.
Спасибо! все четко и понятно без лишней «воды»