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 (т.к. подмножества не пересекаются).

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

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

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

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

Как получить номер строки в MySql?

Ноябрь 5, 2016 г.

Что делать, если нужно пронумеровать строки прямо в запросе? Полистав руководство MySql 5.5, нужной функции я не нашел. Мне нужно было сохранять позиции пользователей в рейтинге, т.е. пронумеровать пользователей в зависимости от значения их рейтинга. ...

Читать

Как изменить значение AUTO_INCREMENT таблицы в MySQL

Август 3, 2022 г.

Наиболее простой ответ - это использование запроса alter, где вы задаёте новое значение для AUTO_INCREMENT нужной вам таблицы: [crayon-65f93697de79c693014638/] Вы можете достичь такого же эффекта изменениями соответствующего столбца вашей таблицы: ...

Читать

 

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

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



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

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

  1. Egor:

    Спасибо автору! мне очень помогло я егор

  2. Александр:

    может всетаки тут 6 должна быть ? http://prntscr.com/ky49nk

  3. Зоха:

    Чувак ты меня спас!

  4. роман:

    как-то страноо. LEFT OUTER JOIN показывает левое множество и оно же показывает левое без пересечения.

  5. kirill:

    Подскажите пожалуйста, можно ли соединить 3 таблицы чтобы вывести 1 столбец из третей таблицы. к примеру :
    Таблица City :
    id , namecity.
    Таблица events :
    id, year.
    Таблица posts :
    id, title, date, description, text.

    Можно ли объединив эти таблицы вывести столбец text из posts?

  6. Misha:

    Спасибо автору! мне очень помогло я миша

  7. Юрий:

    Спасибо! С множествами в виде графики всё стало понятно.

  8. Саша:

    Автор, добра тебе! Очень понятно и наглядно все разъяснил:)

  9. Elena:

    Большое спасибо за понятное и наглядное объяснение.

  10. Михаил:

    Спасибо. Очень наглядно и доходчиво.