SQL join в примерах с описанием

Присоединение таблиц в запросах — это базовый инструмент в работе с базами данных. Давайте рассмотрим какие присоединения (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).

Такое присоединение покажет нам данные из таблиц, только если условие связывания соблюдается — т.е. для сотрудника указан существующий в словаре идентификатор должности.

Если поменять порядок соединения таблиц — получим тот же результат.

Условно представим себе эти таблицы, как пересекающиеся множества, где пересечение — это наличие связи между таблицами. Получим картинку:

Далее проследим как получить разные части (подмножества) данного множества.

OUTER JOIN

Внешнее присоединение. Различают LEFT OUTER JOIN и RIGHT OUTER JOIN, и обычно опускают слово «OUTER».

Внешнее присоединение включает в себя результаты запроса INNER и добавляются «неиспользованные» строки из одной из таблиц. Какую таблицу использовать в качестве «добавки» — указывает токен LEFT или RIGHT.

LEFT JOIN

Внешнее присоединение «слева».

«Левая» таблица persons, содержит строку id_person#3 — «Александр», где указан идентификатор должности, отсутствующей в словаре. Мы увидим все записи из «левой» таблицы, тогда как правая будет присоединена по возможности.

На картинке это можно показать вот так:

RIGHT JOIN

Присоединение «справа».

Тут данные из «левой» таблицы присоединяются к «правой».

Словарь должностей (правая таблица) содержит неиспользуемую запись с id_pos#3 — «программист». Теперь она попала в результат запроса.

Полное множество

MySQL не знает соединения FULL OUTER JOIN. Что если нужно получить полное множество?

Первый способ — объединение запросов LEFT и RIGHT.

При таком вызове UNION, после слияния результатов, SQL отсечет дубли (как DISTINCT). Для отсечения дублей SQL прибегает к сортировке. Это может сказываться на быстродействии.

Второй способ — объединение LEFT и RIGHT, но в одном из запросов мы исключаем часть, соответствующую INNER. А объединение задаём как UNION ALL, что позволяет движку SQL обойтись без сортировки.

Этот пример показывает нам как исключить пересечение и получить только левую или правую часть множества.

Левое подмножество

LEFT JOIN ограничиваем проверкой, что данных из второй таблицы нет.

В нашем примере — это специалисты, у которых не задана должность или нет должности с указанным ключом.

Правое подмножество

Аналогично выделяем правую часть.

В нашем случае получим должности, которые никому не назначены.

Всё кроме пересечения

Остался один вариант, тот когда исключено пересечение множеств. Его можно сложить из двух предыдущих запросов через UNION ALL (т.к. подмножества не пересекаются).

Это запрос соберет все случаи, когда по какой то причине данные из таблиц не связаны.

А графически такое объединение выглядит следующим образом:

Написать комментарий

Мало букафф? Читайте есчо !

Аналог strpos в mySQL

Февраль 4, 2019 г.

Недавно обнаружил, что в mySQL целых три аналога PHP функции strpos. INSTR(str,substr) - возвращает индекс первого найденного совпадения substr ...

Читать

Создание бекапов базы mySQL из консоли unix

Февраль 13, 2018 г.

Ещё одна шпаргалка по администрированию UNIX серверов. На этот раз рассмотрим несколько полезных команд для создания бекапа базы данных из консоли. Нам понадобится команда mysqldump, которая позволяет создавать sql скрипт базы или её части. Создание ...

Читать

 

Комментарии к «SQL join в примерах с описанием»

Понравилась статья? Есть вопросы? - пишите в комментариях.



Комментарий:

Много комментариев в “SQL join в примерах с описанием”

  1. Анна:

    Откуда взялись сокращения p. ps. и pos.? Учитывая, что таблицы две Persons и Positions, не понятно что где и, главное, зачем сокращено.

    • Manul74:

      Сокращать можно и нужно когда таблиц много и у них длинные имена. Тогда получается много текста и взглядом трудно понять что где. Сокращения помогут избавится от лишней писанины.
      Это прийдет с опытом. После написания пару десятков сложных запросов с кучей join. Сами начнете сокращать.

    • Андрей:

      Хорошая была бы заметка. Вот только вместе с борьбой за JOIN еще приходится распутывать загадки уважаемого автора с этими сокращениями. Две таблицы, для начинающих можно обойтись без умствований и загадочных сокращений.

      • Виталий:

        Поддерживаю ;), пока пытаешься разобраться с основами забываешь зачем начал их смотреть перекидывая силы на расшифровку, которая к сожалению в предусловия не выписана.

    • Специально для «лиги лени» переписал статью.
      Если вы действительно хотите разобраться с языком запросов, то надо посвятить этому какое то время. Лучше всего попробовать реализовать какие то практические задачи.

      • Kiko:

        Ув. автор, вы зря переписали статью, надо было просто добавить строчку под * откуда взялось сокращение и для чего оно и все было бы сразу же понятно, вре равно сокр. придется использовать, просто надо было осветить этот момент и все.

  2. Данил:

    Спасибо, все четко и понятно. Освежил знания!

  3. Дмитрий:

    «INNER JOIN
    Внутреннее присоединение. Равносильно просто JOIN или CROSS JOIN.»

    Мне кажется тут нужно поправить. CROSS JOIN, оно же декартово произведение, это совсем не INNER JOIN.

    • Спасибо за замечание! Здесь действительно необходимо уточнить, что эти три варианта синтаксически эквивалентны в MYSQL. Тогда как в SQL стандарте — INNER JOIN используется с выражением ON, а CROSS в прочих случаях.

  4. Михаил:

    Вообще половину не понял. Откуда взялась например левая таблица, правая таблица , что за муть ?? Было две таблицы зачем их рядом ставить на картинке и делать запрос из левой таблицы (вообще в голове вот конкретно это не укладывается. ). Что значит Внешнее присоединение «слева», присоединение чего и куда ??

    • Дмитрий:

      Это самая подробная статья с объяснением и картинками, которую я видел), если тебе не понятно, что это и зачем, может это не твое, может оно тебе и не надо?)

      • Михаил, видимо, находится на одной из начальных фаз изучения вопроса. И тут можно сравнить это со стадиями принятия неизбежного. Т.е. он в «отрицании» или «гневе». Ребята из лиги лени дошли то «торга». Впереди еще возможна «депрессия», но я думаю он справится.

  5. Владимир:

    Автор, спасибо, конечно, за статью, но что за косяки такие с «<code>»? Оно же прямо в тексте примеров. Вы за собой не проверяете результат редактирования?
    Вот что я вижу на странице в примерах:

    …FROM persons
    LEFT OUTER JOIN
    positions ON ...

    Можете устранить пожалуйста? Выглядит очень неряшливо, а для непосвящённых так вообще дико.

  6. Steel:

    Спасибо, познавательно! :)

    P.S.: дизайн поплыл что-то, а фон классный

  7. Андрей:

    Зачем вы разным цветом делаете строки, текст? сразу появляются вопросы и читать неудобно.
    в последних запросах есть скобки, в ранних нет. поему нет комментария, зачем/почему?!

    дислайк.

  8. Будь:

    Здорово живём. Верно ли понимаю, что «левой» принимается таблица, введённая/после оператором/-ра FROM (в примере это везде ‘persons’)? И уже если данные добавляются из неё — это LEFT JOIN, а ежели добавляется из другой («правой», упомянутой после JOIN) — RIGHT JOIN?

    • LEFT и RIGHT — указывают в присоединении какая таблица является основной. LEFT — таблица слева, RIGHT — та что справа. Я бы не называл сами таблицы «левой» или «правой», т.к. это атрибут присоединения (оператора JOIN), а не самой таблицы.
      В целом, да верно — левая — та, что определена первой, как бы «слева» для языков, где принято писать слева-направо. И наоборот для правой.

  9. Мурат:

    Спасибо! все четко и понятно без лишней «воды»