Объяснение SQL объединений JOIN: LEFT/RIGHT/INNER/OUTER
Разберем пример. Имеем две таблицы: пользователи и отделы.
В результате отсутствуют:
Внутреннее объединение INNER JOIN (синоним JOIN, ключевое слово INNER можно опустить).
Выбираются только совпадающие данные из объединяемых таблиц.
Чтобы получить данные, которые подходят по условию частично, необходимо использовать
Такое объединение вернет данные из обеих таблиц (совпадающие по условию объединения) ПЛЮС дополнит выборку оставшимися данными из внешней таблицы, которые по условию не подходят, заполнив недостающие данные значением NULL.
Получаем полный список пользователей и сопоставленные департаменты.
в выборке останется только 3#Александр, так как у него не назначен департамент.
рис. Left outer join с фильтрацией по полю
RIGHT OUTER JOIN вернет полный список департаментов (правая таблица) и сопоставленных пользователей.
Дополнительно можно отфильтровать данные, проверяя их на NULL.
В нашем примере указав WHERE u.id IS null, мы выберем департаменты, в которых не числятся пользователи. (3#Финансы)
Все примеры вы можете протестировать здесь:
Cross/Full Join
FULL JOIN возвращает `объединение` объединений LEFT и RIGHT таблиц, комбинируя результат двух запросов.
CROSS JOIN возвращает перекрестное (декартово) объединение двух таблиц. Результатом будет выборка всех записей первой таблицы объединенная с каждой строкой второй таблицы. Важным моментом является то, что для кросса не нужно указывать условие объединения.
Дублирование строк при использовании JOIN
При использовании объединения новички часто забывают что результирующая выборка может содержать дублирующиеся данные!
Если вам нужна одна запись, делайте объединение с подзапросом
Self Join
Выборка из одной и той же таблицы для нескольких условий.
Рассмотрим задачку от яндекса:
Есть таблица товаров.
Она содержит следующие значения.
Напишите запрос, выбирающий уникальные пары `id` товаров с одинаковыми `name`, например:
При решении задачи необходимо учесть, что пары (x,y) и (y,x) — одинаковы.
— или без группировки (быстрее)
Объединяем таблицы ya_goods по одинаковому полю `name`, группируем по уникальным idентификаторам и получаем результат.
Множественное объединение multi join
Пригодится нам, если необходимо выбрать более одного значения из таблиц для нескольких условий.
Пример: выбрать товары,
добавленные после 17/01/2009 в следующих вариантах:
— вес=310, объем=300
— вес=35, объем=15
— вес=45, объем=25
— вес=200, объем=250
INNER JOIN product2options p2o1 ON p.id = p2o1.product_id
INNER JOIN product_options po1 ON po1.id = p2o1.option_id
INNER JOIN product2options p2o2 ON p.id = p2o2.product_id
INNER JOIN product_options po2 ON po2.id = p2o2.option_id
id title created_at P1 value P2 value
2 Ложка 2009-01-18 20:00:00 Вес 35 Объем 15
3 Тарелка 2009-01-19 20:00:00 Вес 310 Объем 300
2 Ложка 2009-01-18 20:00:00 Вес 45 Объем 25
— не попадает по дате
1 Кружка 2009-01-17 20:00:00 Объем 250 Вес 200
UPDATE и JOIN
Объединение можно использовать совместно с UPDATE.
Например, имеем таблицу houses (id, title, area). Нужно выбрать title, если в нем встречается `число м2`, заменить поле area, если оно меньше. Т.к. в mysql отстутсутствует поддержка регулярных выражений, нужно немного поколдовать с locate и substr.
В подзапросе выбираем интересующие нас данные, и в финальной стадии осуществляем обновление данных подходящий по критерию (p5 > area).
FROM ga_pageviews
WHERE title like ‘ % м2 % ‘
) calc USING ( `id` )
SET base. area = calc.p5
WHERE base. area calc.p5
DELETE и JOIN
Рассмотрим пример с удалением дубликатов. Есть таблица tableWithDups (id, email). Нужно удалить строки с одинаковыми email:
Последние два примера не совместимы с ANSI SQL, но работают в mySQL.
За бортом статьи остались смежные объединениям (а также специфичные для определенных базданных темы):
SELF JOIN, FULL OUTER JOIN, CROSS JOIN (CROSS [OUTER] APPLY), операции над множествами UNION [ALL], INTERSECT, EXCEPT и т.д.
@tags: sql, mysql, sql server, oracle, sqlite, postgresql
Оператор языка SQL JOIN предназначен для соединения двух или более таблиц базы данных по совпадающему условию. Этот оператор существует только в реляционных базах данных. Именно благодаря JOIN реляционные базы данных обладают такой мощной функциональностью, которая позволяет вести не только хранение данных, но и их, хотя бы простейший, анализ с помощью запросов. Разберём основные нюансы написания SQL-запросов с оператором JOIN, которые являются общими для всех СУБД (систем управления базами данных). Для соединения двух таблиц оператор SQL JOIN имеет следующий синтаксис:
После одного или нескольких звеньев с оператором JOIN может следовать необязательная секция WHERE или HAVING, в которой, также, как в простом SELECT-запросе, задаётся условие выборки. Общим для всех СУБД является то, что в этой конструкции вместо JOIN может быть указано INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, CROSS JOIN (или, как вариант, запятая).
INNER JOIN (внутреннее соединение)
Запрос с оператором INNER JOIN предназначен для соединения таблиц и вывода результирующей таблицы, в которой данные полностью пересекаются по условию, указанному после ON.
Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке .
Таблицы этой базы данных с заполненными данными имеют следующий вид.
| Catnumb | Cat_name | Price |
| 10 | Стройматериалы | 105,00 |
| 505 | Недвижимость | 210,00 |
| 205 | Транспорт | 160,00 |
| 30 | Мебель | 77,00 |
| 45 | Техника | 65,00 |
| Part_ID | Part | Cat |
| 1 | Квартиры | 505 |
| 2 | Автомашины | 205 |
| 3 | Доски | 10 |
| 4 | Шкафы | 30 |
| 5 | Книги | 160 |
Результатом выполнения запроса будет следующая таблица:
| Part | Cat | Price |
| Квартиры | 505 | 210,00 |
| Автомашины | 205 | 160,00 |
| Доски | 10 | 105,00 |
| Шкафы | 30 | 77,00 |
В результирующей таблице нет Книг, так как эта запись ссылается на категорию, которой нет в таблице Categories, и Техники, так как эта запись имеет внешний ключ в таблице Categories, на который нет ссылки в таблице Parts.
В ряде случаев при соединениях таблиц составить менее громоздкие запросы можно с помощью предиката EXISTS и без использования JOIN.
Написать запросы SQL с JOIN самостоятельно, а затем посмотреть решения
Пример 2. Определить самого востребованного актёра за последние 5 лет.
Оператор JOIN использовать 2 раза. Использовать COUNT(), CURDATE(), LIMIT 1.
Пример 3. Вывести список актеров, которые в одном спектакле играют более одной роли, и количество их ролей.
Оператор JOIN использовать 1 раз. Использовать HAVING, GROUP BY.
Подсказка. Оператор HAVING применяется к числу ролей, подсчитанных агрегатной функцией COUNT.
LEFT OUTER JOIN (левое внешнее соединение)
Запрос с оператором LEFT OUTER JOIN предназначен для соединения таблиц и вывода результирующей таблицы, в которой данные полностью пересекаются по условию, указанному после ON, и дополняются записями из первой по порядку (левой) таблицы, даже если они не соответствуют условию. У записей левой таблицы, которые не соответствуют условию, значение столбца из правой таблицы будет NULL (неопределённым).
Для получения результирующей таблицы, в которой данные из двух таблиц полностью пересекаются по условию и дополняются всеми данными из таблицы Parts, которые не соответствуют условию, пишем следующий запрос:
Результатом выполнения запроса будет следующая таблица:
| Part | Cat | Price |
| Квартиры | 505 | 210,00 |
| Автомашины | 205 | 160,00 |
| Доски | 10 | 105,00 |
| Шкафы | 30 | 77,00 |
| Книги | 160 | NULL |
RIGHT OUTER JOIN (правое внешнее соединение)
Запрос с оператором RIGHT OUTER JOIN предназначен для соединения таблиц и вывода результирующей таблицы, в которой данные полностью пересекаются по условию, указанному после ON, и дополняются записями из второй по порядку (правой) таблицы, даже если они не соответствуют условию. У записей правой таблицы, которые не соответствуют условию, значение столбца из левой таблицы будет NULL (неопределённым).
Для получения результирующей таблицы, в которой данные из двух таблиц полностью пересекаются по условию и дополняются всеми данными из таблицы Categories, которые не соответствуют условию, пишем следующий запрос:
Результатом выполнения запроса будет следующая таблица:
| Part | Cat | Price |
| Квартиры | 505 | 210,00 |
| Автомашины | 205 | 160,00 |
| Доски | 10 | 105,00 |
| Шкафы | 30 | 77,00 |
| NULL | 45 | 65,00 |
FULL OUTER JOIN (полное внешнее соединение)
Запрос с оператором FULL OUTER JOIN предназначен для соединения таблиц и вывода результирующей таблицы, в которой данные полностью пересекаются по условию, указанному после ON, и дополняются записями из первой (левой) и второй (правой) таблиц, даже если они не соответствуют условию. У записей, которые не соответствуют условию, значение столбцов из другой таблицы будет NULL (неопределённым).
Для получения результирующей таблицы, в которой данные из двух таблиц полностью пересекаются по условию и дополняются всеми данными как из таблицы Parts, так и из таблицы Categories, которые не соответствуют условию, пишем следующий запрос:
Результатом выполнения запроса будет следующая таблица:
| Part | Cat | Price |
| Квартиры | 505 | 210,00 |
| Автомашины | 205 | 160,00 |
| Доски | 10 | 105,00 |
| Шкафы | 30 | 77,00 |
| Книги | 160 | NULL |
| NULL | 45 | 65,00 |
Псевдонимы соединяемых таблиц
Пример 7. Переписать запрос из примера 1 с использованием псевдонимов соединяемых таблиц.
Запрос будет следующим:
Запрос вернёт то же самое, что и запрос в примере 1, но он гораздо компактнее.
JOIN и соединение более двух таблиц
| A_Id | Part_ID | Date_start | Date_end | Text |
| 21 | 1 | ‘2018-02-11’ | ‘2018-04-20’ | «Продаю. « |
| 22 | 1 | ‘2018-02-11’ | ‘2018-05-12’ | «Продаю. « |
| . | . | . | . | . |
| 27 | 1 | ‘2018-02-11’ | ‘2018-04-02’ | «Продаю. « |
| 28 | 2 | ‘2018-02-11’ | ‘2018-04-21’ | «Продаю. « |
| 29 | 2 | ‘2018-02-11’ | ‘2018-04-02’ | «Продаю. « |
| 30 | 3 | ‘2018-02-11’ | ‘2018-04-22’ | «Продаю. « |
| 31 | 4 | ‘2018-02-11’ | ‘2018-05-02’ | «Продаю. « |
| 32 | 4 | ‘2018-02-11’ | ‘2018-04-13’ | «Продаю. « |
| 33 | 3 | ‘2018-02-11’ | ‘2018-04-12’ | «Продаю. « |
| 34 | 4 | ‘2018-02-11’ | ‘2018-04-23’ | «Продаю. « |
Запрос будет следующим:
| Cat_name |
| Недвижимость |
| Транспорт |
CROSS JOIN (перекрестное соединение)
Запрос будет следующим:
Запрос вернёт таблицу из 5 * 5 = 25 строк, фрагмент которой приведён ниже:
| Catnumb | Cat_name | Price | Part_ID | Part | Cat |
| 10 | Стройматериалы | 105,00 | 1 | Квартиры | 505 |
| 10 | Стройматериалы | 105,00 | 2 | Автомашины | 205 |
| 10 | Стройматериалы | 105,00 | 3 | Доски | 10 |
| 10 | Стройматериалы | 105,00 | 4 | Шкафы | 30 |
| 10 | Стройматериалы | 105,00 | 5 | Книги | 160 |
| . | . | . | . | . | . |
| 45 | Техника | 65,00 | 1 | Квартиры | 505 |
| 45 | Техника | 65,00 | 2 | Автомашины | 205 |
| 45 | Техника | 65,00 | 3 | Доски | 10 |
| 45 | Техника | 65,00 | 4 | Шкафы | 30 |
| 45 | Техника | 65,00 | 5 | Книги | 160 |
Как видно из примера, если результат такого запроса и имеет какую-либо ценность, то это, возможно, наглядная ценность в некоторых случаях, когда не требуется вывести структурированную информацию, тем более, даже самую простейшую аналитическую выборку. Кстати, можно указать выводимые столбцы из каждой таблицы, но и тогда информационная ценность такого запроса не повысится.
Но для CROSS JOIN можно задать условие соединения! Результат будет совсем иным. При использовании оператора «запятая» вместо явного указания CROSS JOIN условие соединения задаётся не словом ON, а словом WHERE.
Запрос будет следующим:
Запрос вернёт то же самое, что и запрос в примере 1:
| Part | Cat | Price |
| Квартиры | 505 | 210,00 |
| Автомашины | 205 | 160,00 |
| Доски | 10 | 105,00 |
| Шкафы | 30 | 77,00 |
Осмысляем работу джойнов в SQL: от реляционной алгебры до наглядных картинок
Выбираем, какие фильмы посмотреть, с помощью соединения данных в SQL.
скриншот из игры team fortress 2 / valve
Опять эта проблема — выбрать кино на вечер. Благодаря стриминговым сервисам доступны едва ли не все фильмы мира: это бесконечное полотно с постерами и фильтры, фильтры, фильтры…
МОЗГ: Поставлю-ка я фильтр по стране: пусть будет Дания, и добавлю ограничение по жанру — триллер… Ну вот — другое дело, относительно небольшой список.
— Мозг, а знаешь почему? Да потому что здесь только фильмы, которые сняты в Дании И помечены как триллеры.
— Да не знаю я, как задать такие критерии в этом сервисе. Вот если бы можно было писать на SQL — тут бы решение нашлось для любой комбинации признаков.
— Легко! Ещё и картинки будут. У меня и база фильмов уже спарсена — тренируйся не хочу.
Фулстек-разработчик. Любимый стек: Java + Angular, но в хорошей компании готова писать хоть на языке Ада.
Договоримся об обозначениях
Назовём множество датских фильмов — D, а множество триллеров — T. У каждого фильма будет уникальный номер, он же ключ. Раз ключ — пусть зовётся Key.
Заодно вспомним, как на SQL пишется простой запрос для связывания данных из двух таблиц:
INNER JOIN
Если не уточнить тип соединения ( JOIN), то по умолчанию применяется INNER JOIN — как раз тот вариант, который сработал в нашем кинофильтре. Это он выбирает и триллеры, и датские фильмы одновременно.
JOIN в MySQL: INNER, LEFT, RIGHT, FULL, CROSS. Что это и как использовать
Опубликовано: 20 Июня 2019
Оператор JOIN, формирует результирующую таблицу по заданным условиям, из одной или нескольких уже существующих таблиц SQL. В зависимости от необходимого алгоритма формирования таблицы, к оператору можно подставлять ключевые слова: INNER, CROSS, FULL, LEFT, RIGHT.
Данный оператор полезен для возможности выстраивать более гибкие таблицы в SQL. Чтобы иметь возможность выносить «необязательные» или повторяющиеся данные в отдельные таблицы.
Обобщенный синтаксис оператора выглядит так:
Ниже более подробно разобрано как работает каждый из этих методов, на примере двух таблиц: списка брендов автомобилей и списка возможных цветов.
Таблица со списком брендов:
Таблица со списком цветов:
INNER JOIN / CROSS JOIN
В некоторых SQL базах INNER JOIN не может идти без условия, но в MySQL это возможно, поэтому INNER JOIN и CROSS JOIN в данной SQL системе идентичны, как и JOIN, который является синонимом для INNER JOIN.
Простая выборка, без условий, подставит ко всем вариантам из левой таблицы, все варианты из правой таблицы (перекрестное соединение):
Тот же самый результат можно получить путем следующих записей, которые идентичны:
К выборке можно добавить условие, это актуально как для CROSS, так и для INNER JOIN. Выборку можно производить следующими способами:
В таблице ниже, сопоставилены строки из разных таблиц, но имеющие одинаковый id. В этом случае для BMW и зеленого цвета пары не нашлось, и они не попали в результирующую таблицу:
Ту же самую таблицу можно получить следущими записями:
Если бы столбец id у таблицы с цветами назывался бы color_id, то запись для ON и WHERE была бы следующей:
LEFT JOIN / RIGHT JOIN / FULL JOIN
LEFT JOIN, RIGHT JOIN и FULL JOIN считаются внешними соединениями (OUTER JOIN), поэтому у них также есть синонимы: LEFT OUTER JOIN, RIGHT OUTER JOIN и FULL OUTER JOIN.
LEFT JOIN и RIGHT JOIN отличаются от INNER JOIN тем, что к результирующей таблице добавляются строки не имеющие совпадений в соседней таблице. Если используется LEFT JOIN, добавляются все записи из таблицы указанной по левую сторону от оператора, если RIGHT JOIN, то из таблицы по правую сторону от оператора. В пару к таким строкам устанавливается значение NULL. Оба оператора не возможно использовать без какого-либо условия.
Это используется если, к примеру, надо вывести все доступные бренды машин, не зависимо от того указан у них цвет или нет:
Или все возможные цвета, независимо от того есть ли у брендов такой цвет в наличии:
Можно дополнить запрос условием на проверку несуществования соседних данных, и получить список записей, которые не имеют пары, при этом поля, которые необходимо вывести, можно указать, как и при обычном SELECT запросе:
FULL JOIN объединяет в себе LEFT JOIN и RIGHT JOIN.
В MySQL он используется без условий, результат использования этого оператора будет таким:
Но, при добавлении сравнения USING в MySQL, результат будет аналогичен INNER JOIN:
Другие условия с оператором FULL JOIN в MySQL использовать нельзя, по крайней мере на момент написания статьи.
Сложные и многотабличные запросы
В примере для простоты использовалось только 2 простых таблицы, в реальности же количество условий и таблиц может быть значительно больше. При необходимости таблицу так же можно объединять саму с собой, но в этом случае стоит использовать алиас для имени таблицы:
В 1 запросе можно использовать разные типы объединений, и разное количество запросов, но стоит учесть, что это значительно увеличивает время на обработку запроса. В случае если приходиться использовать длинные запросы, возможно стоит подумать над правильностью организации архитектуры таблиц.
Объединения таблиц можно использовать не только в выборке, но и при удалении и изменении таблиц (UPDATE, DELETE).
Понимание джойнов сломано. Продолжение. Попытка альтернативной визуализации
Многие из вас читали предыдущую статью про то, как неправильная визуализация для объяснения работы JOIN-ов в некоторых случаях может запутать. Круги Венна не могут полноценно проиллюстрировать некоторые моменты, например, если значения в таблице повторяются.
При подготовке к записи шестого выпуска подкаста «Цинковый прод» (где мы договорились обсудить статью) кажется удалось нащупать один интересный вариант визуализации. Кроме того, в комментариях к изначальной статье тоже предлагали похожий вариант.
Все желающие приглашаются под кат
Итак, визуализация. Как мы выяснили в комментах к предыдущей статье, join — это скорее декартово произведение, чем пересечение. Если посмотреть, как иллюстрируют декартово произведение, то можно заметить, что зачастую это прямоугольная таблица, где по одной оси идет первое отношение, а по другой — второе. Таким образом элементы таблицы будут представлять собой все комбинации всего.
Сложно абстрактно это нарисовать, поэтому придется на примере.
Допустим, у нас есть две таблицы. В одной из них
Сразу disclaimer: я назвал поле словом «id» просто для краткости. Многие в прошлой статье возмущались, как это так — id повторяются, безобразие. Не стоит сильно переживать, ну
представьте, например, что это таблица с ежедневной статистикой, где для каждого дня и каждого юзера есть данные по посещению какого-нибудь сайта. В общем, не суть.
Итак, мы хотим узнать, что же получится при различных джойнах таблиц. Начнем с CROSS JOIN:
CROSS JOIN
CROSS JOIN — это все все возможные комбинации, которые можно получить из двух таблиц.
Визуализировать это можно так: по оси x — одна таблица, по оси y — другая, все клеточки внутри (выделены оранжевым) — это результат
INNER JOIN
INNER JOIN (или просто JOIN) — это тот же самый CROSS JOIN, у которого оставлены только те элементы, которые удовлетворяют условию, записанному в конструкции «ON». Обратите внимание на ситуацию, когда записи дублируются — результатов с единичками будет четыре штуки.
LEFT JOIN
LEFT OUTER JOIN (или просто LEFT JOIN) — это тоже самое, что и INNER JOIN, но дополнительно мы добавляем null для строк из первой таблицы, для которой ничего не нашлось во второй
RIGHT JOIN
RIGHT OUTER JOIN ( или RIGHT JOIN) — это тоже самое, что и LEFT JOIN, только наоборот. Т.е. это INNER JOIN + null для строк из второй таблицы, для которой ничего не нашлось в первой
→ Поиграть с запросами можно здесь
Выводы
Вроде бы получилась простая визуализация. Хотя в ней есть ограничения: здесь показан случай, когда в ON записано равенство, а не что-то хитрое (любое булево выражение). Кроме того не рассмотрен случай, когда среди значений таблицы есть null. Т.е. это всё равно некоторое упрощение, но вроде бы получилось лучше и точнее, чем круги Венна.
Подписывайтесь на наш подкаст «Цинковый прод», там мы обсуждаем базы данных, разработку софта и прочие интересные штуки.
