SQL JOIN — соединение таблиц базы данных
Оператор языка SQL JOIN предназначен для соединения двух или более таблиц базы данных по совпадающему условию. Этот оператор существует только в реляционных базах данных. Именно благодаря JOIN реляционные базы данных обладают такой мощной функциональностью, которая позволяет вести не только хранение данных, но и их, хотя бы простейший, анализ с помощью запросов. Разберём основные нюансы написания SQL-запросов с оператором JOIN, которые являются общими для всех СУБД (систем управления базами данных). Для соединения двух таблиц оператор SQL JOIN имеет следующий синтаксис:
SELECT ИМЕНА_СТОЛБЦОВ (1..N) FROM ИМЯ_ТАБЛИЦЫ_1 JOIN ИМЯ_ТАБЛИЦЫ_2 ON УСЛОВИЕ
После одного или нескольких звеньев с оператором JOIN может следовать необязательная секция WHERE или HAVING, в которой, также, как в простом SELECT-запросе, задаётся условие выборки. Общим для всех СУБД является то, что в этой конструкции вместо JOIN может быть указано INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, CROSS JOIN (или, как вариант, запятая).
Запрос с оператором INNER JOIN предназначен для соединения таблиц и вывода результирующей таблицы, в которой данные полностью пересекаются по условию, указанному после ON.
То же самое делает и просто JOIN. Таким образом, слово INNER — не обязательное.
Пример 1. Есть база данных портала объявлений. В ней есть таблица Categories (категории объявлений) и Parts (части, или иначе — рубрики, которые и относятся к категориям). Например, части Квартиры, Дачи относятся к категории Недвижимость, а части Автомобили, Мотоциклы — к категории Транспорт. Эти таблицы с заполненными данными имеют следующий вид.
Таблица Parts:
| Part_ID | Part | Cat |
| 1 | Квартиры | 505 |
| 2 | Автомашины | 205 |
| 3 | Доски | 10 |
| 4 | Шкафы | 30 |
| 5 | Книги | 160 |
Таблица Categories:
| Cat_ID | Cat_name | Price |
| 10 | Стройматериалы | 105,00 |
| 505 | Недвижимость | 210,00 |
| 205 | Транспорт | 160,00 |
| 30 | Мебель | 77,00 |
| 45 | Техника | 65,00 |
Заметим, что в таблице Parts Книги имеют Cat — ссылку на категорию, которой нет в таблице Categories, а в таблице Categories Техника имеет Cat_ID — первичный ключ, ссылки на который нет в таблице Parts. Требуется соединить данные этих двух таблиц так, чтобы в результирующей таблице были поля Part (Часть), Cat (Категория) и Price (Цена подачи объявления) и чтобы данные полностью пересекались по условию. Условие — совпадение идентификатора категории в таблице Categories и ссылки на категорию в таблице Parts. Для этого пишем следующий запрос:
SELECT PARTS.Part, CATEGORIES.Cat_ID AS Cat, CATEGORIES.Price FROM PARTS INNER JOIN CATEGORIES ON PARTS.Cat = CATEGORIES.Cat_ID
Результатом выполнения запроса будет следующая таблица:
| Part | Cat | Price |
| Квартиры | 505 | 210,00 |
| Автомашины | 205 | 160,00 |
| Доски | 10 | 105,00 |
| Шкафы | 30 | 77,00 |
В результирующей таблице нет Книг, так как эта запись ссылается на категорию, которой нет в таблице Categories, и Техники, так как эта запись имеет внешний ключ в таблице Categories, на который нет ссылки в таблице Parts.
В ряде случаев при соединениях таблиц составить менее громоздкие запросы можно с помощью предиката EXISTS и без использования JOIN.
Есть база данных «Театр». Таблица Play содержит данные о постановках. Таблица Team — о ролях актёров. Таблица Actor — об актёрах. Таблица Director — о режиссёрах. Поля таблиц, первичные и внешние ключи можно увидеть на рисунке ниже (для увеличения нажать левой кнопкой мыши).
Пример 2. Определить самого востребованного актёра за последние 5 лет.
Оператор JOIN использовать 2 раза. Использовать COUNT(), CURDATE(), LIMIT 1.
Правильное решение и ответ
Пример 3. Вывести список актеров, которые в одном спектакле играют более одной роли, и количество их ролей.
Оператор JOIN использовать 1 раз. Использовать HAVING, GROUP BY.
Подсказка. Оператор HAVING применяется к числу ролей, подсчитанных агрегатной функцией COUNT.
Правильное решение и ответ.
Запрос с оператором LEFT OUTER JOIN предназначен для соединения таблиц и вывода результирующей таблицы, в которой данные полностью пересекаются по условию, указанному после ON, и дополняются записями из первой по порядку (левой) таблицы, даже если они не соответствуют условию. У записей левой таблицы, которые не соответствуют условию, значение столбца из правой таблицы будет NULL (неопределённым).
Пример 4. База данных и таблицы — те же, что и в примере 1.
Для получения результирующей таблицы, в которой данные из двух таблиц полностью пересекаются по условию и дополняются всеми данными из таблицы Parts, которые не соответствуют условию, пишем следующий запрос:
SELECT PARTS.Part, CATEGORIES.Cat_ID AS Cat, CATEGORIES.Price FROM PARTS LEFT OUTER JOIN CATEGORIES ON PARTS.Cat = CATEGORIES.Cat_ID
Результатом выполнения запроса будет следующая таблица:
| Part | Cat | Price |
| Квартиры | 505 | 210,00 |
| Автомашины | 205 | 160,00 |
| Доски | 10 | 105,00 |
| Шкафы | 30 | 77,00 |
| Книги | 160 | NULL |
В результирующей таблице, в отличие от таблицы из примера 1, есть Книги, но значение столбца Цены (Price) у них — NULL, так как эта запись имеет идентификатор категории, которой нет в таблице Categories.
Запрос с оператором RIGHT OUTER JOIN предназначен для соединения таблиц и вывода результирующей таблицы, в которой данные полностью пересекаются по условию, указанному после ON, и дополняются записями из второй по порядку (правой) таблицы, даже если они не соответствуют условию. У записей правой таблицы, которые не соответствуют условию, значение столбца из левой таблицы будет NULL (неопределённым).
Пример 5. База данных и таблицы — те же, что и в предыдущих примерах.
Для получения результирующей таблицы, в которой данные из двух таблиц полностью пересекаются по условию и дополняются всеми данными из таблицы Categories, которые не соответствуют условию, пишем следующий запрос:
SELECT PARTS.Part, CATEGORIES.Cat_ID AS Cat, CATEGORIES.Price FROM PARTS RIGHT OUTER JOIN CATEGORIES ON PARTS.Cat = CATEGORIES.Cat_ID
Результатом выполнения запроса будет следующая таблица:
| Part | Cat | Price |
| Квартиры | 505 | 210,00 |
| Автомашины | 205 | 160,00 |
| Доски | 10 | 105,00 |
| Шкафы | 30 | 77,00 |
| NULL | 45 | 65,00 |
В результирующей таблице, в отличие от таблицы из примера 1, есть запись с категорией 45 и ценой 65,00, но значение столбца Части (Part) у неё — NULL, так как эта запись имеет идентификатор категории, на которую нет ссылок в таблице Parts.
Запрос с оператором FULL OUTER JOIN предназначен для соединения таблиц и вывода результирующей таблицы, в которой данные полностью пересекаются по условию, указанному после ON, и дополняются записями из первой (левой) и второй (правой) таблиц, даже если они не соответствуют условию. У записей, которые не соответствуют условию, значение столбцов из другой таблицы будет NULL (неопределённым).
Пример 6. База данных и таблицы — те же, что и в предыдущих примерах.
Для получения результирующей таблицы, в которой данные из двух таблиц полностью пересекаются по условию и дополняются всеми данными как из таблицы Parts, так и из таблицы Categories, которые не соответствуют условию, пишем следующий запрос:
SELECT PARTS.Part, CATEGORIES.Cat_ID AS Cat, CATEGORIES.Price FROM PARTS FULL OUTER JOIN CATEGORIES ON PARTS.Cat = CATEGORIES.Cat_ID
Результатом выполнения запроса будет следующая таблица:
| Part | Cat | Price |
| Квартиры | 505 | 210,00 |
| Автомашины | 205 | 160,00 |
| Доски | 10 | 105,00 |
| Шкафы | 30 | 77,00 |
| Книги | 160 | NULL |
| NULL | 45 | 65,00 |
В результирующей таблице есть записи Книги (из левой таблицы) и с категорией 45 (из правой таблицы), причём у первой из них неопределённая цена (столбец из правой таблицы), а у второй — неопределённая часть (столбец из левой таблицы).
В предыдущих запросах мы указывали с названиями извлекаемых столбцов из разных таблиц полные имена этих таблиц. Такие запросы выглядят громоздко: одно и то же слово повторяется несколько раз. Нельзя ли как-то упростить конструкцию? Оказывается, можно. Для этого следует использовать псевдонимы таблиц — их сокращённые имена. Псевдоним может состоять и из одной буквы. Возможно любое количество букв в псевдониме, главное, чтобы запрос после сокращения был понятен Вам самим. Общее правило: в секции запроса, определяющей соединение, то есть вокруг слова JOIN нужно указать полные имена таблиц, а за каждым именем должен следовать псевдоним таблицы.
Пример 7. Переписать запрос из примера 1 с использованием псевдонимов соединяемых таблиц.
Запрос будет следующим:
SELECT P.Part, C.Cat_ID AS Cat, C.Price FROM PARTS P INNER JOIN CATEGORIES C ON P.Cat = C.Cat_ID
Запрос вернёт то же самое, что и запрос в примере 1, но он гораздо компактнее.
Реляционные базы данных должны подчиняться требованиям целостности и неизбыточности данных, в связи с чем данные об одном бизнес-процессе могут содержаться не только в одной, двух, но и в трёх и более таблицах. В этих случаях для анализа данных используются цепочки соединённых таблиц: например, в одной (первой) таблице содержится некоторый количественный показатель, вторую таблицу с первой и третьей связывают внешние ключи — данные пересекаются, но только третья таблица содержит условие, в зависимости от которого может быть выведен количественный показатель из первой таблицы. И таблиц может быть ещё больше. При помощи оператора SQL JOIN в одном запросе можно соединить большое число таблиц. В таких запросах за одной секцией соединения следует другая, причём каждый следующий JOIN соединяет со следующей таблицей таблицу, которая была второй в предыдущем звене цепочки. Таким образом, синтаксис SQL запроса для соединения более двух таблиц следующий:
SELECT ИМЕНА_СТОЛБЦОВ (1..N) FROM ИМЯ_ТАБЛИЦЫ_1 JOIN ИМЯ_ТАБЛИЦЫ_2 ON УСЛОВИЕ JOIN ИМЯ_ТАБЛИЦЫ_3 ON УСЛОВИЕ … JOIN ИМЯ_ТАБЛИЦЫ_M ON УСЛОВИЕ
Пример 8. База данных — та же, что и в предыдущих примерах. К таблицам Categories и Parts в этом примере добавится таблица Ads, содержащая данные об опубликованных на портале объявлениях. Приведём фрагмент таблицы Ads, в котором среди записей есть записи о тех объявлениях, срок публикации которых истекает 2018-04-02.
| 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’ | «Продаю…» |
Представим, что сегодня ‘2018-04-02’, то есть это значение принимает функция CURDATE() — текущая дата. Требуется узнать, к каким категориям принадлежат объявления, срок публикации которых истекает сегодня. Названия категорий есть только в таблице CATEGORIES, а даты истечения срока публикации объявлений — только в таблице ADS. В таблице PARTS — части категорий (или проще, подкатегории) опубликованных объявлений. Но внешним ключом Cat_ID таблица PARTS связана с таблицей CATEGORIES, а таблица ADS связана внешним ключом Part_ID с таблицей PARTS. Поэтому соединяем в одном запросе три таблицы и этот запрос можно с максимальной корректностью назвать цепочкой.
Запрос будет следующим:
SELECT C.cat_name FROM categories C JOIN parts P ON P.cat_id=C.cat_id JOIN ads A ON A.part_id=P.part_id WHERE A.date_end=CURDATE()
Результат запроса — таблица, содержащая названия двух категорий — «Недвижимость» и «Транспорт»:
| Cat_name |
| Недвижимость |
| Транспорт |
Использование оператора SQL CROSS JOIN в наиболее простой форме — без условия соединения — реализует операцию декартова произведения в реляционной алгебре. Результатом такого соединения будет сцепление каждой строки первой таблицы с каждой строкой второй таблицы. Таблицы могут быть записаны в запросе либо через оператор CROSS JOIN, либо через запятую между ними.
Пример 9. База данных — всё та же, таблицы — Categories и Parts. Реализовать операцию декартова произведения этих двух таблиц.
Запрос будет следующим:
SELECT (*) Categories CROSS JOIN Parts
Или без явного указания CROSS JOIN — через запятую:
SELECT (*) Categories, Parts
Запрос вернёт таблицу из 5 * 5 = 25 строк, фрагмент которой приведён ниже:
| Cat_ID | 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.
Пример 10. Та же база данных портала объявлений, таблицы Categories и Parts. Используя перекрестное соединение, соединить таблицы так, чтобы данные полностью пересекались по условию. Условие — совпадение идентификатора категории в таблице Categories и ссылки на категорию в таблице Parts.
Запрос будет следующим:
SELECT P.Part, C.Cat_ID AS Cat, C.Price FROM PARTS P, CATEGORIES C WHERE P.Cat = C.Cat_ID
Запрос вернёт то же самое, что и запрос в примере 1:
| Part | Cat | Price |
| Квартиры | 505 | 210,00 |
| Автомашины | 205 | 160,00 |
| Доски | 10 | 105,00 |
| Шкафы | 30 | 77,00 |
И это совпадение не случайно. Запрос c перекрестным соединением по условию соединения полностью аналогичен запросу с внутренним соединением — INNER JOIN — или, учитывая, что слово INNER — не обязательное, просто JOIN.
Таким образом, какой вариант запроса использовать — вопрос стиля или даже привычки специалиста по работе с базой данных. Возможно, перекрёстное соединение с условием для двух таблиц может представляться более компактным. Но преимущество перекрестного соединения для более чем двух таблиц (это также возможно) весьма спорно. В этом случае WHERE-условия пересечения перечисляются через слово AND. Такая конструкция может быть громоздкой и трудной для чтения, если в конце запроса есть также секция WHERE с условиями выборки.
Поделиться с друзьями
Реляционные базы данных и язык SQL
function-x.ru
SQL join в примерах с описанием
Присоединение таблиц в запросах — это базовый инструмент в работе с базами данных. Давайте рассмотрим какие присоединения (JOIN) бывают, и что от этого меняется в результатах запроса.
Для начала создадим две таблицы, над которыми будем проводить опыты. Это таблица с именами сотрудников и словарь с перечнем должностей.
Persons (Сотрудники)
Positions (должности)
Т.е. чтобы узнать должность сотрудника, нужно присоединить соответствующие данные. Далее мы рассмотрим все варианты присоединений. Данные специально подобраны так, чтобы продемонстрировать отличия в результатах разных запросов.
INNER JOIN
Внутреннее присоединение. Равносильно просто JOIN или CROSS 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
SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность` FROM `persons` p INNER JOIN `positions` ps ON ps.id = p.post_id |
Такое присоединение покажет нам данные из таблиц только если условие связывания соблюдается — т.е. для сотрудника указан существующий в словаре идентификатор должности.
Если поменять порядок соединения таблиц — получим тот же результат.
Условно представим себе эти таблицы, как пересекающиеся множества, где пересечение — это наличие связи между таблицами. Получим картинку:
Далее проследим как получить разные части (подмножества) данного множества.
OUTER 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
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 |
«Левая» таблица persons, содержит строку id#3 — «Александр», где указан идентификатор должности, отсутствующей в словаре.
На картинке это можно показать вот так:
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
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 |
Словарь должностей (правая таблица) содержит неиспользуемую запись с id#3 — «программист». Теперь она попала в результат запроса.
Полное множество
MySQL не знает соединения FULL OUTER JOIN. Что если нужно получить полное множество?
Первый способ — объединение запросов 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)
(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) |
При таком вызове 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)
(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
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 |
В нашем примере — это специалисты, у которых не задана должность или нет должности с указанным ключом.
Правое подмножество
Точно также выделяем правую часть.
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
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 |
В нашем случае получим должности, которые никому не назначены.
Всё кроме пересечения
Остался один вариант, тот когда исключено пересечение множеств. Его можно сложить из двух предыдущих запросов через 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)
shra.ru
Команды JOIN, INNER JOIN, LEFT JOIN, RIGHT JOIN — связывание таблиц
Команды JOIN, INNER JOIN, LEFT JOIN, RIGHT JOIN используются для связывания таблиц по определенным полям связи.
Синтаксис
SELECT поля FROM имя_таблицы
LEFT JOIN имя_связанной_таблицы ON условие_связи
WHERE условие_выборкиПримеры
Все примеры будут по таблицам countries и cities, если не сказано иное.
Таблица countries:
| id айди | name имя |
|---|---|
| 1 | Беларусь |
| 2 | Россия |
| 3 | Украина |
Таблица cities:
| id айди | name имя | country_id айди страны |
|---|---|---|
| 1 | Минск | 1 |
| 2 | Витебск | 1 |
| 3 | Москва | 2 |
| 4 | Питер | 2 |
| 5 | Лондон | 0 |
Пример . LEFT JOIN
В данном примере …:
SELECT
cities.id as city_id, cities.name as city_name, cities.country_id as city_country_id,
countries.id as country_id, countries.name as country_name
FROM cities
LEFT JOIN countries ON countries.id=cities.country_idSQL запрос выберет следующие строки:
| city_id айди города | city_name название города | city_country_id айди страны | country_id айди страны | country_name название страны |
|---|---|---|---|---|
| 1 | Минск | 1 | 1 | Беларусь |
| 2 | Витебск | 1 | 1 | Беларусь |
| 3 | Москва | 2 | 2 | Россия |
| 4 | Питер | 2 | 2 | Россия |
| 5 | Лондон | 0 | NULL |
Пример . RIGHT JOIN
В данном примере … Лондон не выберется, а Украина наоборот
SELECT
cities.id as city_id, cities.name as city_name, cities.country_id as city_country_id,
countries.id as country_id, countries.name as country_name
FROM cities
RIGHT JOIN countries ON countries.id=cities.country_idSQL запрос выберет следующие строки:
| city_id айди города | city_name название города | city_country_id айди страны | country_id айди страны | country_name название страны |
|---|---|---|---|---|
| 1 | Минск | 1 | 1 | Беларусь |
| 2 | Витебск | 1 | 1 | Беларусь |
| 3 | Москва | 2 | 2 | Россия |
| 4 | Питер | 2 | 2 | Россия |
| NULL | NULL | NULL | 3 | Украина |
Пример . INNER JOIN
В данном примере … Лондон и Украина не выберется
SELECT
cities.id as city_id, cities.name as city_name, cities.country_id as city_country_id,
countries.id as country_id, countries.name as country_name
FROM cities
INNER JOIN countries ON countries.id=cities.country_idSQL запрос выберет следующие строки:
| city_id айди города | city_name название города | city_country_id айди страны | country_id айди страны | country_name название страны |
|---|---|---|---|---|
| 1 | Минск | 1 | 1 | Беларусь |
| 2 | Витебск | 1 | 1 | Беларусь |
| 3 | Москва | 2 | 2 | Россия |
| 4 | Питер | 2 | 2 | Россия |
code.mu
Операторы слияния таблиц SQL JOIN в Excel PowerQuery
Смотрите видео к статье:
Или операторы объединения таблиц SQL JOIN в Excel Power Query (начиная с Excel 2016)
Как известно самым популярным и эффективным инструментом работы с табличными данными (или просто таблицами) является программа Microsoft Excel.
При этом также известно, что самым мощным и распространённым языком программирования для работы с табличными данными является язык Structured Query Language = SQL = Язык структурированных запросов
Исходя из этого факта, логично предположить, что Microsoft Excel должен поддерживать язык SQL по умолчанию. Но, как правило, SQL поддерживается только базами данных (СУБД).
Несмотря на это, для Excel (начиная с 2010 версии) появилась бесплатная надстройка Power Query, которая позволяет имитировать часть полезного функционала языка SQL, а именно:
В языке SQL есть очень интересный оператор «SELECT», который позволяет делать запросы к табличным данным в базе данных. В результате запроса возвращается набор данных (выборка из базы данных), удовлетворяющий заданному условию выборки.
Как правило, выборка данных делается из нескольких таблиц в базе данных. Для связи таблиц в языке программирования SQL существует оператор «JOIN», который выполняет различные операции соединения реляционных таблиц (в основе этого принципа лежат законы реляционной алгебры)
Различают следующие виды оператора «JOIN»:
- INNER JOIN — Оператор внутреннего соединения двух таблиц
- LEFT OUTER JOIN — Оператор левого внешнего соединения двух таблиц
- RIGHT OUTER JOIN — Оператор правого внешнего соединения двух таблиц
- FULL OUTER JOIN — Оператор полного внешнего соединения двух таблиц
- CROSS JOIN — Оператор перекрёстного соединения (декартово произведение) двух таблиц
С выходом надстройки Power Query для Excel (это один из инструментов уровня Self-Service BI) в Excel появилась поддержка функционала всех видов операторов «JOIN» языка SQL:
Рассмотрим операторов «JOIN» в Excel на примерах:
Представим, что у нас в Excel есть две Таблицы: A «Люди» и B «Города»
Теперь давайте объединим данные таблицы с помощью различных операторов «JOIN»:
(объединяем таблицы через столбцы: A.Cityid = B.id)
Оператор INNER JOIN вернет следующий результат:
Оператор LEFT JOIN вернет следующий результат:
Оператор RIGHT JOIN вернет следующий результат:
Оператор FULL OUTER JOIN вернет следующий результат:
Оператор CROSS JOIN вернет следующий результат:
В надстройке Power Query для Excel данная функция «JOIN» называется «Слияние» — слияние запросов, где:
Оператору слияния INNER JOIN соответствует тип соединения: Внутреннее (только совпадающие строки)
Оператору слияния LEFT JOIN соответствует тип соединения: Внешнее соединение слева (все из первой таблицы, совпадающие из второй)
Оператору слияния RIGHT JOIN соответствует тип соединения: Внешнее соединение справа (все из второй таблицы, совпадающие из первой)
Оператору слияния FULL OUTER JOIN соответствует тип соединения: Полное внешнее (все строки из обеих таблиц)
Оператора слияния CROSS JOIN в интерфейсе Power Query нет, но его можно создать из оператора слияния FULL OUTER JOIN, убрав связи таблиц
Скачать Excel файл с примерами объединения SQL JOIN (функция «Слияние» в Power Query) можно скачать здесь
Пошаговая инструкция использования функции «Слияние»/«Объединения» в Power Query находится в видеоуроке к данной статье
biweb.ru
MySQL: оператор JOIN — Индустрия веб разработки
Большинство начинающих веб программистов начинает свое изучение MySQL с простейших операторов SELECT, UPDATE и DELETE. Данными операторами вполне можно описать весь необходимый функционал простого сайта, но, как можно догадаться, на этом возможности языка SQL далеко не заканчиваются. В процессе разработки обязательно потребуется объединение данных из нескольких таблиц. И для этих целей существует оператор JOIN. Данный оператор является основным оператором стандарта SQL92 и поддерживается большинством СУБД.
Общий синтаксис JOIN выглядит следующим образом:SELECT FIELD [,... n]
FROM MainTable
{INNER | {LEFT | RIGHT | FULL} OUTER | CROSS } JOIN JoinTable ON <conditions>Однако, сразу следует отметить, что СУБД MySQL поддерживает только два наиболее популярных выражения: INNER JOIN и LEFT JOIN.
Рассмотрим как работает каждый из операторов, для этого создадим две таблицы: TableA и TableB.
id name id name -- ---- -- ---- 1 Pirate 1 Rutabaga 2 Monkey 2 Pirate 3 Ninja 3 Darth Vader 4 Spaghetti 4 Ninja
Попробуем объединить данные из этих таблиц используя различные варианты конструкции оператора JOIN.
1. INNER JOIN – внутреннее соединение. Объединяет две таблицы, где каждая строка обоих таблиц в точности соответствует условию. Если для строки одной таблицы не найдено соответствия в другой таблице, строка не включается в набор.
SELECT * FROM TableA INNER JOIN TableB ON TableA.name = TableB.name
id name id name -- ---- -- ---- 1 Pirate 2 Pirate 3 Ninja 4 Ninja
INNER JOIN
Выбор по первичному ключу и индексу положительно сказывается на скорости выборки.
2. OUTER JOIN – внешнее объединение.
Присоединение таблицы с необязательным присутствием записи в таблице. Также как и в случае с inner join, условие по индексированным полям и первичному ключу ускоряет все виды outer join’ов.
2.1 LEFT OUTER JOIN или LEFT JOIN-левое внешнее объединения. Левосторонние объединения позволяют извлекать данные из левой таблицы, дополняя их по возможности данными из правой таблицы, поля правой таблицы заполняются значениями NULL.
SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name
id name id name -- ---- -- ---- 1 Pirate 2 Pirate 2 Monkey null null 3 Ninja 4 Ninja 4 Spaghetti null null
LEFT OUTER JOIN
Если дополнить предыдущий запрос условием на проверку несуществования, то можно получить список записей, которые не имеют пары в таблице TableB:
SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name WHERE TableB.id IS null
id name id name -- ---- -- ---- 2 Monkey null null 4 Spaghetti null null
LEFT OUTER JOIN
2.2 RIGHT OUTER JOIN или RIGHT JOIN — Правостороннее внешнее объединение
Этот вид объединений практически ничем не отличается от левостороннего объединения, за тем исключением, что данные берутся из второй таблицы, которая находится справа от конструкции JOIN, и сравниваются с данными, которые находятся в таблице, указанной перед конструкцией.
2.3 FULL OUTER JOIN – комбинация правого и левого объединений. К левой таблице присоединяются все записи из правой, соответствующие условию (по правилам inner join), плюс все не вошедшие записи из правой таблицы, поля левой таблицы заполняются значениями NULL и плюс все не вошедшие записи из левой таблицы, поля правой таблицы заполняются значениями NULL.
SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name
id name id name -- ---- -- ---- 1 Pirate 2 Pirate 2 Monkey null null 3 Ninja 4 Ninja 4 Spaghetti null null null null 1 Rutabaga null null 3 Darth Vader
FULL OUTER JOIN
SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name WHERE TableA.id IS null OR TableB.id IS null
id name id name -- ---- -- ---- 2 Monkey null null 4 Spaghetti null null null null 1 Rutabaga null null 3 Darth Vader
FULL OUTER JOIN
3. CROSS JOIN – перекрестное объединение (декартово произведение), выводятся все возможные сочетания из обеих таблиц. Для этого типа оператора JOIN условия не указывается.
SELECT * FROM TableA CROSS JOIN TableB
id name id name -- ---- -- ---- 1 Pirate 1 Rutabaga 2 Monkey 2 Rutabaga 3 Ninja 3 Rutabaga 4 Spaghetti 4 Rutabaga 1 Pirate 1 Pirate 2 Monkey 2 Pirate 3 Ninja 3 Pirate 4 Spaghetti 4 Pirate 1 Pirate 1 Darth Vader 2 Monkey 2 Darth Vader 3 Ninja 3 Darth Vader 4 Spaghetti 4 Darth Vader 1 Pirate 1 Ninja 2 Monkey 2 Ninja 3 Ninja 3 Ninja 4 Spaghetti 4 Ninja
Данная конструкция, по причине своей не нужности, не поддерживается почти ни в одной БД
blog.kogtev.com
JOINS — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite
Это учебное пособие Oracle объясняет, как использовать JOINS (INNER и OUTER) в Oracle с синтаксисом и примерами.
Описание
Oracle JOINS используются для извлечения данных из нескольких таблиц. JOIN выполняется всякий раз, когда две или более таблиц объединяются в SQL предложении.
Есть 4 различных типа присоединения Oracle:
Рассмотрим синтаксис Oracle JOIN, а также изучим примеры Oracle JOIN.
INNER JOIN (простое соединение)
Скорее всего, вы уже писали запросы в которых используются Oracle INNER JOIN. Это наиболее распространенный тип соединения. Oracle INNER JOINS возвращает все строки из нескольких таблиц, где выполняется условия соединения.
Синтаксис
Синтаксис INNER JOIN в Oracle/PLSQL:
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
В этом рисунке, Oracle INNER JOIN возвращает затененную область:
Oracle INNER JOIN будет возвращать записи, где table1 и table2 будут пересекаться.
Пример
Ниже приведен пример Oracle INNER JOIN:
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers INNER JOIN orders ON suppliers.supplier_id = orders.supplier_id;
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers INNER JOIN orders ON suppliers.supplier_id = orders.supplier_id; |
Этот пример Oracle INNER JOIN возвращает все строки из таблиц suppliers и orders, где имеются соответствующие значение поля supplier_id в обоих таблицах.
Рассмотрим некоторые данные, чтобы понять, как работает INNER JOIN:
У нас есть таблица suppliers с двумя полями (supplier_id и supplier_name) которая содержит следующие данные:
| supplier_id | supplier_name |
|---|---|
| 10000 | IBM |
| 10001 | Hewlett Packard |
| 10002 | Microsoft |
| 10003 | NVIDIA |
У нас есть еще одна таблица orders с тремя полями (order_id, supplier_id и order_date). Она содержит следующие данные:
| order_id | supplier_id | order_date |
|---|---|---|
| 500125 | 10000 | 05.05.2015 |
| 500126 | 10001 | 08.02.2016 |
| 500127 | 10004 | 06.01.2017 |
Если мы выполним Oracle оператор SELECT (который содержит INNER JOIN) ниже:
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers INNER JOIN orders ON suppliers.supplier_id = orders.supplier_id;
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers INNER JOIN orders ON suppliers.supplier_id = orders.supplier_id; |
Наш результирующий набор будет выглядеть следующим образом:
| supplier_id | name | order_date |
|---|---|---|
| 10000 | IBM | 05.05.2015 |
| 10001 | Hewlett Packard | 08.02.2016 |
Строки для Microsoft и NVIDIA из таблицы suppliers будут опущены, так как значения supplier_id 10002 и 10003 не существует в обеих таблицах. Строка order_id 500127 из таблицы orders будет опущена, так как supplier_id 10004 не существует в таблице suppliers.
Старый Синтаксис
В качестве последнего примечания, стоит отметить, что приведенный выше пример Oracle INNER JOIN можно переписать, используя старый неявный синтаксис следующим образом (но рекомендуется использовать синтаксис INNER JOIN):
SELECT suppliers.supplier_id,
suppliers.supplier_name,
orders.order_date
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id;
LEFT OUTER JOIN
Другой тип соединения называется Oracle LEFT OUTER JOIN. Этот тип соединения возвращает все строки из таблиц с левосторонним соединением, указанным в условии ON, и только те строки из другой таблицы, где объединяемые поля равны.
Синтаксис
Синтаксис для Oracle LEFT OUTER JOIN:
SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;
В некоторых базах данных LEFT OUTER JOIN заменяется на LEFT JOIN.
На этом рисунке, Oracle LEFT OUTER JOIN возвращает затененную область:
Oracle LEFT OUTER JOIN возвратит все записи из table1 и только те записи из table2, которые пересекаются с table1.
Пример
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers LEFT OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id;
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers LEFT OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id; |
Этот пример LEFT OUTER JOIN возвратит все строки из таблицы suppliers, и только те строки из таблицы orders, где объединяемые поля равны.
Если значение supplier_id в таблице suppliers не существует в таблице orders, все поля таблицы orders будут отображаться в результирующем наборе как NULL.
Рассмотрим некоторые данные, чтобы понять, как работает LEFT OUTER JOIN:
У нас есть таблица suppliers с двумя полями (supplier_id и supplier_name) которая содержит следующие данные:
| supplier_id | supplier_name |
|---|---|
| 10000 | IBM |
| 10001 | Hewlett Packard |
| 10002 | Microsoft |
| 10003 | NVIDIA |
У нас есть еще одна таблица orders с тремя полями (order_id, supplier_id и order_date). Она содержит следующие данные:
| order_id | supplier_id | order_date |
|---|---|---|
| 500125 | 10000 | 05.05.2015 |
| 500126 | 10001 | 08.02.2016 |
Если мы выполним Oracle оператор SELECT (который содержит LEFT OUTER JOIN) ниже:
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers LEFT OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id;
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers LEFT OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id; |
Результирующий набор будет выглядеть следующим образом:
| supplier_id | name | order_date |
|---|---|---|
| 10000 | IBM | 05.05.2015 |
| 10001 | Hewlett Packard | 08.02.2016 |
| 10002 | Microsoft | null |
| 10003 | NVIDIA | null |
Строки для Microsoft и NVIDIA будут включены, так как был использован LEFT OUTER JOIN. Тем не менее, вы заметите, что поле order_date для этих записей содержит значение NULL.
RIGHT OUTER JOIN
Другой тип соединения называется Oracle RIGHT OUTER JOIN. Этот тип соединения возвращает все строки из таблиц с правосторонним соединением, указанным в условии ON, и только те строки из другой таблицы, где объединяемые поля равны.
Синтаксис
Синтаксис Oracle RIGHT OUTER JOIN:
SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;
В некоторых базах данных, RIGHT OUTER JOIN заменяется на RIGHT JOIN.
На этом рисунке, Oracle RIGHT OUTER JOIN возвращает затененную область:
Oracle RIGHT OUTER JOIN возвратит все записи из table2 и только те записи из table1, которые пересекаются с table2.
Пример
Ниже приведен пример Oracle RIGHT OUTER JOIN:
SELECT orders.order_id, orders.order_date, suppliers.supplier_name FROM suppliers RIGHT OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id;
SELECT orders.order_id, orders.order_date, suppliers.supplier_name FROM suppliers RIGHT OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id; |
Этот пример RIGHT OUTER JOIN возвращает все строки из таблицы orders и только те строки из таблицы suppliers, где объединяемые поля равны.
Если значение supplier_id в таблице orders не существует в таблице suppliers, все поля в таблице suppliers будут отображаться в результирующем наборе как NULL.
Рассмотрим некоторые данные, чтобы понять, как работает RIGHT OUTER JOIN:
У нас есть таблица suppliers с двумя полями (supplier_id и supplier_name) которая содержит следующие данные:
| supplier_id | supplier_name |
|---|---|
| 10000 | Apple |
| 10001 |
У нас есть вторая таблица orders с тремя полями (order_id, supplier_id и order_date). Она содержит следующие данные:
| order_id | supplier_id | order_date |
|---|---|---|
| 500125 | 10000 | 12.05.2016 |
| 500126 | 10001 | 14.05.2016 |
| 500127 | 10002 | 18.05.2016 |
Если мы выполним Oracle оператор SELECT (который содержит RIGHT OUTER JOIN) ниже:
SELECT orders.order_id, orders.order_date, suppliers.supplier_name FROM suppliers RIGHT OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id;
SELECT orders.order_id, orders.order_date, suppliers.supplier_name FROM suppliers RIGHT OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id; |
Результирующий набор будет выглядеть следующим образом:
| order_id | order_date | supplier_name |
|---|---|---|
| 500125 | 12.05.2016 | Apple |
| 500126 | 14.05.2016 | |
| 500127 | 18.05.2016 | null |
Строка для order_id 500127 будет включена, так как был использован RIGHT OUTER JOINS. Тем не менее, вы заметите, что поле supplier_name для этой записи содержит значение NULL.
FULL OUTER JOIN
Другой тип соединения называется Oracle FULL OUTER JOIN. Этот тип соединения возвращает все строки из левой таблицы и правой таблицы с NULL — значениями в месте, где условие объединения не выполняется.
Синтаксис
Синтаксис для Oracle FULL OUTER JOIN:
SELECT columns
FROM table1
FULL [OUTER] JOIN table2
ON table1.column = table2.column;
В некоторых базах данных, FULL OUTER JOIN заменяются FULL JOIN.
На этом рисунке, FULL OUTER JOIN возвращает затененную область:
Oracle FULL OUTER JOIN будет возвращать все записи из обеих таблиц table1 и table2.
Пример
Ниже приведен пример Oracle FULL OUTER JOIN:
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers FULL OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id;
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers FULL OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id; |
Этот пример FULL OUTER JOIN возвратит все строки из таблицы suppliers и все строки из таблицы orders и всякий раз, когда условие соединения не выполняется, то поля в результирующем наборе будут принимать значения NULL.
Если значение поля supplier_id в таблице suppliers не существует в таблице orders, то все поля в таблице orders будут отображаться в результирующем наборе как NULL. Если значение supplier_id в таблице orders не существует в таблице suppliers, то все поля в таблице suppliers будут отображаться результирующем наборе как NULL .
Рассмотрим некоторые данные, чтобы понять, как работает FULL OUTER JOIN:
У нас есть таблица suppliers с двумя полями (supplier_id и supplier_name). Она содержит следующие данные:
| supplier_id | supplier_name |
|---|---|
| 10000 | IBM |
| 10001 | Hewlett Packard |
| 10002 | Microsoft |
| 10003 | NVIDIA |
У нас есть вторая таблица orders с тремя полями (order_id, supplier_id и order_date), которая содержит следующие данные:
| order_id | supplier_id | order_date |
|---|---|---|
| 500125 | 10000 | 12.05.2016 |
| 500126 | 10001 | 14.05.2016 |
| 500127 | 10004 | 18.05.2016 |
Если мы выполним Oracle оператор SELECT (который содержит FULL OUTER JOIN) ниже:
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers FULL OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id;
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers FULL OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id; |
Результирующий набор будет выглядеть следующим образом:
| supplier_id | supplier_name | order_date |
|---|---|---|
| 10000 | IBM | 12.05.2016 |
| 10001 | Hewlett Packard | 14.05.2016 |
| 10002 | Microsoft | null |
| 10003 | NVIDIA | null |
| null | null | 18.05.2016 |
Строки для Microsoft и NVIDIA будут включены, так как используется FULL OUTER JOIN. Тем не менее, вы заметите, что поле order_date для этих записей содержит значение NULL.
Строка для supplier_id 10004 также будет включена, так как используется FULL OUTER JOIN. Тем не менее, вы заметите, что supplier_id и поле supplier_name для этих записей содержат значение NULL.
oracleplsql.ru
Join (SQL) — Википедия. Что такое Join (SQL)
JOIN — оператор языка SQL, который является реализацией операции соединения реляционной алгебры. Входит в предложение FROM операторов SELECT, UPDATE и DELETE.
Операция соединения, как и другие бинарные операции, предназначена для обеспечения выборки данных из двух таблиц и включения этих данных в один результирующий набор. Отличительными особенностями операции соединения являются следующие:
- в схему таблицы-результата входят столбцы обеих исходных таблиц (таблиц-операндов), то есть схема результата является «сцеплением» схем операндов;
- каждая строка таблицы-результата является «сцеплением» строки из одной таблицы-операнда со строкой второй таблицы-операнда.
Определение того, какие именно исходные строки войдут в результат и в каких сочетаниях, зависит от типа операции соединения и от явно заданного условия соединения. Условие соединения, то есть условие сопоставления строк исходных таблиц друг с другом, представляет собой логическое выражение (предикат).
При необходимости соединения не двух, а нескольких таблиц, операция соединения применяется несколько раз (последовательно).
Описание оператора
FROM
Table1
{INNER | {LEFT | RIGHT | FULL} OUTER | CROSS } JOIN
Table2
{ON <condition> | USING (field_name [,... n])}
В большинстве СУБД при указании слов LEFT, RIGHT, FULL слово OUTER можно опустить.
Слово INNER также в большинстве СУБД можно опустить.
В общем случае СУБД при выполнении соединения проверяет условие (предикат) condition. Если названия столбцов по которым происходит соединение таблиц совпадают, то вместо ON можно использовать USING. Для CROSS JOIN условие не указывается.
Для перекрёстного соединения (декартова произведения) CROSS JOIN в некоторых реализациях SQL используется оператор «запятая» (,):
Виды оператора JOIN
Для дальнейших пояснений будут использоваться следующие таблицы:
| Id | Name |
|---|---|
| 1 | Москва |
| 2 | Санкт-Петербург |
| 3 | Казань |
| Name | CityId |
|---|---|
| Андрей | 1 |
| Леонид | 2 |
| Сергей | 1 |
| Григорий | 4 |
INNER JOIN
Оператор внутреннего соединения INNER JOIN соединяет две таблицы. Порядок таблиц для оператора неважен, поскольку оператор является симметричным.
Заголовок таблицы-результата является объединением (конкатенацией) заголовков соединяемых таблиц.
Тело результата логически формируется следующим образом. Каждая строка одной таблицы сопоставляется с каждой строкой второй таблицы, после чего для полученной «соединённой» строки проверяется условие соединения (вычисляется предикат соединения). Если условие истинно, в таблицу-результат добавляется соответствующая «соединённая» строка.
Описанный алгоритм действий является строго логическим, то есть он лишь объясняет результат, который должен получиться при выполнении операции, но не предписывает, чтобы конкретная СУБД выполняла соединение именно указанным образом. Существует множество способов реализации операции соединения, например, соединение вложенными циклами (англ. inner loops join), соединение хэшированием (англ. hash join), соединение слиянием (англ. merge join). Единственное требование состоит в том, чтобы любая реализация логически давала такой же результат, как при применении описанного алгоритма.
SELECT *
FROM
Person
INNER JOIN
City
ON Person.CityId = City.Id
Результат:
| Person.Name | Person.CityId | City.Id | City.Name |
|---|---|---|---|
| Андрей | 1 | 1 | Москва |
| Леонид | 2 | 2 | Санкт-Петербург |
| Сергей | 1 | 1 | Москва |
OUTER JOIN
Соединение двух таблиц, в результат которого обязательно входят все строки либо одной, либо обеих таблиц.
LEFT OUTER JOIN
Оператор левого внешнего соединения LEFT OUTER JOIN соединяет две таблицы. Порядок таблиц для оператора важен, поскольку оператор не является симметричным.
Заголовок таблицы-результата является объединением (конкатенацией) заголовков соединяемых таблиц.
Тело результата логически формируется следующим образом. Пусть выполняется соединение левой и правой таблиц по предикату (условию) p.
- В результат включается внутреннее соединение (
INNER JOIN) левой и правой таблиц по предикату p. - Затем в результат добавляются те записи левой таблицы, которые не вошли во внутреннее соединение на шаге 1. Для таких записей поля, соответствующие правой таблице, заполняются значениями
NULL.
SELECT *
FROM
Person -- Левая таблица
LEFT OUTER JOIN
City -- Правая таблица
ON Person.CityId = City.Id
Результат:
| Person.Name | Person.CityId | City.Id | City.Name |
|---|---|---|---|
| Андрей | 1 | 1 | Москва |
| Леонид | 2 | 2 | Санкт-Петербург |
| Сергей | 1 | 1 | Москва |
| Григорий | 4 | NULL | NULL |
RIGHT OUTER JOIN
Оператор правого внешнего соединения RIGHT OUTER JOIN соединяет две таблицы. Порядок таблиц для оператора важен, поскольку оператор не является симметричным.
Заголовок таблицы-результата является объединением (конкатенацией) заголовков соединяемых таблиц.
Тело результата логически формируется следующим образом. Пусть выполняется соединение левой и правой таблиц по предикату (условию) p.
- В результат включается внутреннее соединение (
INNER JOIN) левой и правой таблиц по предикату p. - Затем в результат добавляются те записи правой таблицы, которые не вошли во внутреннее соединение на шаге 1. Для таких записей поля, соответствующие левой таблице, заполняются значениями
NULL.
SELECT *
FROM
Person -- Левая таблица
RIGHT OUTER JOIN
City -- Правая таблица
ON Person.CityId = City.Id
Результат:
| Person.Name | Person.CityId | City.Id | City.Name |
|---|---|---|---|
| Андрей | 1 | 1 | Москва |
| Сергей | 1 | 1 | Москва |
| Леонид | 2 | 2 | Санкт-Петербург |
| NULL | NULL | 3 | Казань |
FULL OUTER JOIN
Оператор полного внешнего соединения FULL OUTER JOIN соединяет две таблицы. Порядок таблиц для оператора неважен, поскольку оператор является симметричным.
Заголовок таблицы-результата является объединением (конкатенацией) заголовков соединяемых таблиц.
Тело результата логически формируется следующим образом. Пусть выполняется соединение первой и второй таблиц по предикату (условию) p. Слова «первой» и «второй» здесь не обозначают порядок в записи (который неважен), а используются лишь для различения таблиц.
- В результат включается внутреннее соединение (
INNER JOIN) первой и второй таблиц по предикату p. - В результат добавляются те записи первой таблицы, которые не вошли во внутреннее соединение на шаге 1. Для таких записей поля, соответствующие второй таблице, заполняются значениями
NULL. - В результат добавляются те записи второй таблицы, которые не вошли во внутреннее соединение на шаге 1. Для таких записей поля, соответствующие первой таблице, заполняются значениями
NULL.
SELECT *
FROM
Person
FULL OUTER JOIN
City
ON Person.CityId = City.Id
Результат:
| Person.Name | Person.CityId | City.Id | City.Name |
|---|---|---|---|
| Андрей | 1 | 1 | Москва |
| Сергей | 1 | 1 | Москва |
| Леонид | 2 | 2 | Санкт-Петербург |
| NULL | NULL | 3 | Казань |
| Григорий | 4 | NULL | NULL |
CROSS JOIN
Оператор перекрёстного соединения, или декартова произведения CROSS JOIN соединяет две таблицы. Порядок таблиц для оператора неважен, поскольку оператор является симметричным.
Заголовок таблицы-результата является объединением (конкатенацией) заголовков соединяемых таблиц.
Тело результата логически формируется следующим образом. Каждая строка одной таблицы соединяется с каждой строкой второй таблицы, давая тем самым в результате все возможные сочетания строк двух таблиц.
SELECT * FROM Person CROSS JOIN City
или
SELECT * FROM Person, City
Результат:
| Person.Name | Person.CityId | City.Id | City.Name |
|---|---|---|---|
| Андрей | 1 | 1 | Москва |
| Андрей | 1 | 2 | Санкт-Петербург |
| Андрей | 1 | 3 | Казань |
| Леонид | 2 | 1 | Москва |
| Леонид | 2 | 2 | Санкт-Петербург |
| Леонид | 2 | 3 | Казань |
| Сергей | 1 | 1 | Москва |
| Сергей | 1 | 2 | Санкт-Петербург |
| Сергей | 1 | 3 | Казань |
| Григорий | 4 | 1 | Москва |
| Григорий | 4 | 2 | Санкт-Петербург |
| Григорий | 4 | 3 | Казань |
Если в предложении WHERE добавить условие соединения (предикат p), то есть ограничения на сочетания кортежей, то результат эквивалентен операции INNER JOIN с таким же условием:
SELECT * FROM Person, City WHERE Person.CityId = City.Id
Таким образом, выражения t1, t2 WHERE p и t1 INNER JOIN t2 ON p синтаксически являются альтернативными формами записи одной и той же логической операции внутреннего соединения по предикату p. Синтаксис CROSS JOIN + WHERE для операции соединения называют устаревшим, его не рекомендует стандарт SQL ANSI[1][2].
Примечания
Ссылки
wiki.sc