Что такое outer join

В чем различия между INNER JOIN и OUTER JOIN?

5 ответов 5

Предположим, вы хотите выполнить соединение по столбцам без дубликатов, что встречается довольно часто:

Внутреннее соединение A и B: A пересекает B, т.е. внутренняя часть пересечения диаграммы Венна.

Внешнее соединение A и B: A соединяется с B, т.е. внешняя часть соединения на диаграмме Венна.

Примеры

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

Обратите внимание, что (1,2) уникальны для A, (3,4) – общие элементы, а (5,6) уникальны для B.

Внутреннее соединение

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

Левое внешнее соединение

Результат левого внешнего соединения – все строки таблицы A плюс все строки таблицы B, имеющие совпадение со строками таблицы А.

Правое внешнее соединение

Результат правого внешнего соединения – все строки таблицы В плюс все строки таблицы А, имеющие совпадение со строками таблицы В.

Полное внешнее соединение

Результат полного внешнего соединения – соединение таблиц A и B, т.е. все строки A и все строки B. Если какой-либо элемент таблицы A не имеет соответствия в таблице B, эта часть B пуста, и наоборот.

Источник

Осмысляем работу джойнов в SQL: от реляционной алгебры до наглядных картинок

Выбираем, какие фильмы посмотреть, с помощью соединения данных в SQL.

скриншот из игры team fortress 2 / valve

Опять эта проблема — выбрать кино на вечер. Благодаря стриминговым сервисам доступны едва ли не все фильмы мира: это бесконечное полотно с постерами и фильтры, фильтры, фильтры…

МОЗГ: Поставлю-ка я фильтр по стране: пусть будет Дания, и добавлю ограничение по жанру — триллер… Ну вот — другое дело, относительно небольшой список.

— Мозг, а знаешь почему? Да потому что здесь только фильмы, которые сняты в Дании И помечены как триллеры.

— Да не знаю я, как задать такие критерии в этом сервисе. Вот если бы можно было писать на SQL — тут бы решение нашлось для любой комбинации признаков.

— Легко! Ещё и картинки будут. У меня и база фильмов уже спарсена — тренируйся не хочу.

Фулстек-разработчик. Любимый стек: Java + Angular, но в хорошей компании готова писать хоть на языке Ада.

Договоримся об обозначениях

Назовём множество датских фильмов — D, а множество триллеров — T. У каждого фильма будет уникальный номер, он же ключ. Раз ключ — пусть зовётся Key.

Заодно вспомним, как на SQL пишется простой запрос для связывания данных из двух таблиц:

INNER JOIN

Если не уточнить тип соединения ( JOIN), то по умолчанию применяется INNER JOIN — как раз тот вариант, который сработал в нашем кинофильтре. Это он выбирает и триллеры, и датские фильмы одновременно.

Источник

MySQL и JOINы

Поводом для написания данной статьи послужили некоторые дебаты в одной из групп linkedin, связанной с MySQL, а также общение с коллегами и хабролюдьми 🙂

В данной статье хотел написать что такое вообще JOINы в MySQL и как можно оптимизировать запросы с ними.

Что такое JOINы в MySQL

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

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

Прмер обычного запроса с INNER JOIN

где Р — условия склейки таблиц и фильтры в WHERE условии.

Можно представить такой псевдокод выполнения такого запроса.

где конструкция t1||t2||t3 означает конкатенацию столбцов из разных таблиц.

Если в запросе встречаются OUTER JOINs, например, LEFT OUTER JOIN

то алгоритм выполнения этого запроса MySQL будет выглядеть как-то так

Итак, как мы видим, JOINы это просто группа вложенных циклов. Так почему же в MySQL и UNION и SELECT и запросы с SUBQUERY тоже джоины?

MySQL оптимизатор старается приводить запросы к тому виду к которому ему удобней обрабатывать и выполнять запросы по стандартной схеме.

С SELECT все понятно — просто цикл без вложенных циклов. Все UNION выполняются как отдельные запросы и результаты складываются во временную таблицу, и потом MySQL работает уже с этой таблицей, т.е. проходясь циклом по записям в ней. С Subquery та же история.

Приводя все к одному шаблону, например, МySQL переписывает все RIGHT JOIN запросы на LEFT JOIN эквиваленты.

Но стратегия выполнения запросов через вложенные циклы накладывает некоторые ограничения, например, в связи с такой схемой MySQL не поддерживает выполнение FULL OUTER JOIN запросов.

Но результат такого запроса можно получить с помощью UNION двух запросов на LEFT JOIN и на RIGHT JOIN
Пример самого запроса можно посмотреть по ссылке на вики.

План выполнения JOIN запросов

В отличии от других СУРБД MySQL не генерирует байткод для выполнения запроса, вместо этого MySQL генерирует список инструкций в древовидной форме, которых придерживается engine выполнения запроса выполняя запрос.
Это дерево имеет следующий вид и имеет название «left-deep tree»

В отличии от сбалансированных деревьев (Bushy plan), которые применяются в других СУБД (например Oracle)

JOIN оптимизация

Теперь перейдем к самому интересному — к оптимизации джоинов.
MySQL оптимизатор, а именно та его часть, которая отвечает за оптимизацию JOIN-ов выбирает порядок в котором будет производиться склейка имеющихся таблиц, т.к. можно получить один и тот же результат (датасет) при различном порядке таблиц в склейке. MySQL оптимизатор оценивает стоимость различных планов и выбирает с наименьшей стоимостью. Единицей оценки является операция единичного чтения страницы данных размером в 4 килобайта из произвольного места на диске.

Для выбранного плана можно узнать стоимость путем выполнения команды

SHOW SESSION STATUS LIKE ‘Last_query_cost’;

после выполнения интересующего нас запроса. Переменная Last_query_cost является сессионной переменной. Описание переменной Last_query_cost в MySQL документации можно найти здесь — dev.mysql.com/doc/refman/5.1/en/server-status-variables.html#option_mysqld_Last_query_cost

Оценка основана на статистике: количество страниц памяти, занимаемое таблицей и/или индексами для этой таблицы, cardinality (число уникальных значений) индексов, длинна записей и индексов, их распределение и т.д. Во время своей оценки оптимизатор не рассчитывает на то, что какие-то части попадут в кеш, оптимизатор предполагает, что каждая операция чтения это обращение к диску.

Иногда анализатор-оптимизатор не может проанализировать все возможные планы выполнения и выбирает неправильный. Например, если у нас INNER JOIN по 3м таблицам, то возможных вариантов у анализатора — 3! = 6, а если у нас склейка по 10 таблицам, то тут возможных вариантов уже 10! = 3628800… MySQL не может проанализировать столько вариантов, поэтому в таком случае он использует алгоритм «жадного» поиска.

И вот как раз для решения данной проблемы, нам может пригодиться конструкция STRAIGHT_JOIN. На самом деле я противник подобных хаков как FORCE INDEX и STRAIGH_JOIN, точней против их бездумного использования везде где только можно и нельзя. В данном случае — можно 🙂 Выяснив (либо экспериментальным путем делая запросы с STRAIGH_JOIN и оценивая Last_query_cost, либо эмпирическим путем) нужный порядок джоинов можно переписать запрос с таблицами в соответствующем порядке и добавить STRAIGH_JOIN к данному запросу, таким образом мы сразу убьем двух зайцев — определим правильный план выполнения запроса (это главный заяц) и сэкономим время на стадии «Statistic» (Все стадии выполнения запроса можно посмотреть установив профайлинг запросов командой SET PROFILING =1, я описывал это в своей предыдущей статье по теме профайлинга запросов в MySQL )

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

Также, как уже говорилось выше, результаты джоинов помещаются во временные таблицы, поэтому зачастую уместно применять «derived table» в котором мы накладываем все необходимые нам условия на выборку, а также указываем LIMIT и порядок сортировки. В данном случае мы избавимся от избыточности данных во временной таблице, а также проведем сортировку на раннем этапе (по результату одной выборки, а не финальной склейки, что уменьшит размеры записей которые будут сортироваться).

Стандартный пример подхода описанного выше. Простая выборка для отношения много к многим: новости и теги к ним.

Ну и на последок небольшая задачка, которую я иногда задаю на собеседованиях 🙂

Есть новостной блоггерный сайт. Есть такие сущности как новости и комментарии к ним.

Задача — нужно написать запрос, который выводит список из 10 новостей определенного типа (задается пользователем) отсортированные по времени издания в хронологическом порядке, а также к каждой из этих новостей показать не более 10 последних коментариев, т.е. если коментариев больше — показываем только последние 10.

Все нужно сделать одним запросом. Да, это, может, и не самый лучший способ, и вы вольны предложить другое решение 🙂

Источник

Объяснение 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, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке .

Таблицы этой базы данных с заполненными данными имеют следующий вид.

Результатом выполнения запроса будет следующая таблица:

В результирующей таблице нет Книг, так как эта запись ссылается на категорию, которой нет в таблице 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, которые не соответствуют условию, пишем следующий запрос:

Результатом выполнения запроса будет следующая таблица:

RIGHT OUTER JOIN (правое внешнее соединение)

Запрос с оператором RIGHT OUTER JOIN предназначен для соединения таблиц и вывода результирующей таблицы, в которой данные полностью пересекаются по условию, указанному после ON, и дополняются записями из второй по порядку (правой) таблицы, даже если они не соответствуют условию. У записей правой таблицы, которые не соответствуют условию, значение столбца из левой таблицы будет NULL (неопределённым).

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

Результатом выполнения запроса будет следующая таблица:

FULL OUTER JOIN (полное внешнее соединение)

Запрос с оператором FULL OUTER JOIN предназначен для соединения таблиц и вывода результирующей таблицы, в которой данные полностью пересекаются по условию, указанному после ON, и дополняются записями из первой (левой) и второй (правой) таблиц, даже если они не соответствуют условию. У записей, которые не соответствуют условию, значение столбцов из другой таблицы будет NULL (неопределённым).

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

Результатом выполнения запроса будет следующая таблица:

Псевдонимы соединяемых таблиц

Пример 7. Переписать запрос из примера 1 с использованием псевдонимов соединяемых таблиц.

Запрос будет следующим:

Запрос вернёт то же самое, что и запрос в примере 1, но он гораздо компактнее.

JOIN и соединение более двух таблиц

Запрос будет следующим:

CROSS JOIN (перекрестное соединение)

Запрос будет следующим:

Запрос вернёт таблицу из 5 * 5 = 25 строк, фрагмент которой приведён ниже:

Как видно из примера, если результат такого запроса и имеет какую-либо ценность, то это, возможно, наглядная ценность в некоторых случаях, когда не требуется вывести структурированную информацию, тем более, даже самую простейшую аналитическую выборку. Кстати, можно указать выводимые столбцы из каждой таблицы, но и тогда информационная ценность такого запроса не повысится.

Но для CROSS JOIN можно задать условие соединения! Результат будет совсем иным. При использовании оператора «запятая» вместо явного указания CROSS JOIN условие соединения задаётся не словом ON, а словом WHERE.

Запрос будет следующим:

Запрос вернёт то же самое, что и запрос в примере 1:

Источник