В поисках работы, которая затягивает?

SQL JOIN в Примерах: Практическое Обучение

  • 40 мин.

SQL Join в примерах



Присоединение таблиц в запросах – это базовый инструмент в работе с базами данных. Давайте рассмотрим какие присоединения (JOIN) бывают, и что от этого меняется в результатах запроса.

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

Persons (Сотрудники)

Таблица сотрудники

Positions (должности)

Таблица сотрудники

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

INNER JOIN

            
SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность`
FROM `persons` p
INNER JOIN `positions` ps ON ps.id = p.post_id
            
        

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

Inner join table

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

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

Inner join result

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

OTHER JOIN

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

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

LEFT JOIN

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

            
SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность`
FROM `persons` p
LEFT OUTER JOIN `positions` ps ON ps.id = p.post_id
            
        

Left join table

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

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

Left join result

RIGHT JOIN

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

            
SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность`
FROM `persons` p
RIGHT OUTER JOIN `positions` ps ON ps.id = p.post_id
            
        

Right join table

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

Right join result

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

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

Left right join result

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

            
(SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность`
FROM `persons` p
LEFT OUTER JOIN `positions` ps ON ps.id = p.post_id)
UNION
(SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность`
FROM `persons` p
RIGHT OUTER JOIN `positions` ps ON ps.id = p.post_id)
            
        

Left right join table

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

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

            
(SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность`
FROM `persons` p
LEFT OUTER JOIN `positions` ps ON ps.id = p.post_id)
UNION ALL
(SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность`
FROM `persons` p
RIGHT OUTER JOIN `positions` ps ON ps.id = p.post_id
WHERE p.id IS NULL)
            
        

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

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

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

            
SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность`
FROM `persons` p
LEFT OUTER JOIN `positions` ps ON ps.id = p.post_id
WHERE ps.id is NULL
            
        

Left subset join result

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

Left subset join table

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

Точно также выделяем правую часть.

            
SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность`
FROM `persons` p
RIGHT OUTER JOIN `positions` ps ON ps.id = p.post_id
WHERE p.id is NULL
            
        

Right subset join result

Right subset join result

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

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

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

            
(SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность`
FROM `persons` p
LEFT OUTER JOIN `positions` ps ON ps.id = p.post_id
WHERE ps.id is NULL)
UNION ALL
(SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность`
FROM `persons` p
RIGHT OUTER JOIN `positions` ps ON ps.id = p.post_id
WHERE p.id is NULL)
            
        

Except crossing join result

Материал взят с сайта shra.ru