Язык SQL – объединение JOIN
Продолжаем изучать основы SQL, и пришло время поговорить о простых объединениях JOIN. И сегодня мы рассмотрим, как объединяются данные по средствам операторов LEFT JOIN, RIGHT JOIN, CROSS JOIN и INNER JOIN, другими словами, научимся писать запросы, которые объединяют данные, и как обычно изучать все это будем на примерах.
Объединения JOIN очень важны в SQL, так как без умения писать запросы с объединением данных разных объектов, просто не обойтись программисту SQL, да и просто админу который время от времени выгружает какие-то данные из базы данных, поэтому это относится к основам SQL и каждый человек, который имеет дело с SQL, должен иметь представление, что это такое.
Примечание! Все примеры будем писать в Management Studio SQL Server 2008.
Мы с Вами уже давно изучаем основы SQL, и если вспомнить начинали мы с оператора select, и вообще было уже много материала на этом сайте по SQL, например:
И много другого, даже уже рассматривали объединения union и union all, но, так или иначе, более подробно именно об объединениях join мы с Вами не разговаривали, поэтому сегодня мы восполним этот пробел в наших знаниях.
И начнем мы как обычно с небольшой теории.
Объединения JOIN — это объединение двух или более объектов базы данных по средствам определенного ключа или ключей или в случае cross join и вовсе без ключа. Под объектами здесь подразумевается различные таблицы, представления (views), табличные функции или просто подзапросы sql, т.е. все, что возвращает табличные данные.
Объединение SQL LEFT и RIGHT JOIN
LEFT JOIN – это объединение данных по левому ключу, т.е. допустим, мы объединяем две таблицы по left join, и это значит что все данные из второй таблицы подтянутся к первой, а в случае отсутствия ключа выведется NULL значения, другими словами выведутся все данные из левой таблицы и все данные по ключу из правой таблицы.
RIGHT JOIN – это такое же объединение как и Left join только будут выводиться все данные из правой таблицы и только те данные из левой таблицы в которых есть ключ объединения.
Теперь давайте рассматривать примеры, и для начала создадим две таблицы:
Вот такие простенькие таблицы, И я для примера заполнил их вот такими данными:
Теперь давайте напишем запрос с объединением этих таблиц по ключу number, для начала по LEFT:
Как видите, здесь данные из таблицы t1 вывелись все, а данные из таблицы t2 не все, так как строки с number = 4 там нет, поэтому и вывелись NULL значения.
А что будет, если бы мы объединяли по средствам right join, а было бы вот это:
Другими словами, вывелись все строки из таблицы t2 и соответствующие записи из таблицы t1, так как все те ключи, которые есть в таблице t2, есть и в таблице t1, и поэтому у нас нет NULL значений.
Объединение SQL INNER JOIN
Inner join – это объединение когда выводятся все записи из одной таблицы и все соответствующие записи из другой таблице, а те записи которых нет в одной или в другой таблице выводиться не будут, т.е. только те записи которые соответствуют ключу. Кстати сразу скажу, что inner join это то же самое, что и просто join без Inner. Пример:
А теперь давайте попробуем объединить наши таблицы по двум ключам, для этого немного вспомним, как добавлять колонку в таблицу и как обновить данные через update, так как в наших таблицах всего две колонки, и объединять по текстовому полю как-то не хорошо. Для этого добавим колонки:
Обновим наши данные, просто проставим в колонку number2 значение 1:
И давайте напишем запрос с объединением по двум ключам:
И результат будет таким же, как и в предыдущем примере:
Но если мы, допустим во второй таблице в одной строке изменим, поле number2 на значение скажем 2, то результат будет уже совсем другой.
Запрос тот же самый, а вот результат:
Как видите, по второму ключу у нас одна строка не вывелась.
Объединение SQL CROSS JOIN
CROSS JOIN – это объединение SQL по которым каждая строка одной таблицы объединяется с каждой строкой другой таблицы. Лично у меня это объединение редко требуется, но все равно иногда требуется, поэтому Вы также должны уметь его использовать. Например, в нашем случае получится, конечно, не понятно что, но все равно давайте попробуем, тем более синтаксис немного отличается:
Здесь у нас каждой строке таблицы test_table соответствует каждая строка из таблицы test_table_2, т.е. в таблице test_table у нас 4 строки, а в таблице test_table_2 3 строки 4 умножить 3 и будет 12, как и у нас вывелось 12 строк.
И напоследок, давайте покажу, как можно объединять несколько таблиц, для этого я, просто для примера, несколько раз объединю нашу первую таблицу со второй, смысла в объединение в данном случае, конечно, нет но, Вы увидите, как можно это делать и так приступим:
Как видите, я здесь объединяю и по left и по right и по inner просто, для того чтобы это было наглядно.
С объединениями я думаю достаточно, тем более ничего сложного в них нет. Но на этом изучение SQL не закончено в следующих статьях мы продолжим, а пока тренируйтесь и пишите свои запросы. Удачи!
Заметка! Для комплексного изучения языка T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, в которых используется последовательная методика обучения и рассматриваются все конструкции языка SQL и T-SQL.
Crossjoin (многомерные выражения)
Возвращает перекрестное произведение двух или нескольких наборов.
Синтаксис
Аргументы
Set_Expression1
Допустимое многомерное выражение, возвращающее набор.
Set_Expression2
Допустимое многомерное выражение, возвращающее набор.
Remarks
Функция с перекрестным соединением Возвращает перекрестное произведение двух или более заданных наборов. Порядок кортежей в результирующем наборе зависит от порядка соединяемых наборов и от порядка их элементов. Например, если первый набор состоит из
Если наборы в перекрестном соединении состоят из кортежей из различных иерархий атрибутов одного измерения, функция вернет только те кортежи, которые действительно существуют. Дополнительные сведения см. в разделе Основные понятия в многомерных выражениях (Analysis Services).
Примеры
В следующем запросе показаны простые примеры использования функции Crossjoin по осям Columns и Rows запроса:
FROM [Adventure Works]
WHERE Measures.[Internet Sales Amount]
В следующем примере показана автоматическая фильтрация, которая выполняется при перекрестном соединении различных иерархий из одного измерения:
Measures.[Internet Sales Amount]
//Only the dates in Calendar Years 2003 and 2004 will be returned here
FROM [Adventure Works]
В следующих трех примерах возвращается одинаковый результат — значение меры Internet Sales Amount по штатам в США. В первых двух примерах используются два синтаксиса перекрестного соединения, а в третьем демонстрируется применение предложения WHERE для извлечения тех же сведений.
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).
Оператор языка 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 |
