Оператор join – SQL JOIN — соединение таблиц базы данных

Содержание

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_IDPartCat
1Квартиры505
2Автомашины205
3Доски10
4Шкафы30
5Книги160

Таблица Categories:

Cat_IDCat_namePrice
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

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

PartCatPrice
Квартиры505210,00
Автомашины205160,00
Доски10105,00
Шкафы3077,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

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

PartCatPrice
Квартиры505210,00
Автомашины205160,00
Доски10105,00
Шкафы3077,00
Книги160NULL

В результирующей таблице, в отличие от таблицы из примера 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

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

PartCatPrice
Квартиры505210,00
Автомашины205160,00
Доски10105,00
Шкафы3077,00
NULL4565,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

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

PartCatPrice
Квартиры505210,00
Автомашины205160,00
Доски10105,00
Шкафы3077,00
Книги160NULL
NULL4565,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_IdPart_IDDate_startDate_endText
211'2018-02-11''2018-04-20'"Продаю..."
221'2018-02-11''2018-05-12'"Продаю..."
...............
271'2018-02-11''2018-04-02'"Продаю..."
282'2018-02-11''2018-04-21'"Продаю..."
292'2018-02-11''2018-04-02'"Продаю..."
303'2018-02-11''2018-04-22'"Продаю..."
314'2018-02-11''2018-05-02'"Продаю..."
324'2018-02-11''2018-04-13'"Продаю..."
333'2018-02-11''2018-04-12'"Продаю..."
344'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_IDCat_namePricePart_IDPartCat
10Стройматериалы105,001Квартиры505
10Стройматериалы105,002Автомашины205
10Стройматериалы105,003Доски10
10Стройматериалы105,004Шкафы30
10Стройматериалы105,005Книги160
..................
45Техника65,001Квартиры505
45Техника65,002Автомашины205
45Техника65,003Доски10
45Техника65,004Шкафы30
45Техника65,005Книги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:

PartCatPrice
Квартиры505210,00
Автомашины205160,00
Доски10105,00
Шкафы3077,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_id

SQL запрос выберет следующие строки:

city_id
айди города
city_name
название города
city_country_id
айди страны
country_id
айди страны
country_name
название страны
1Минск11Беларусь
2Витебск11Беларусь
3Москва22Россия
4Питер22Россия
5Лондон0NULL

Пример . 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_id

SQL запрос выберет следующие строки:

city_id
айди города
city_name
название города
city_country_id
айди страны
country_id
айди страны
country_name
название страны
1Минск11Беларусь
2Витебск11Беларусь
3Москва22Россия
4Питер22Россия
NULLNULLNULL3Украина

Пример . 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_id

SQL запрос выберет следующие строки:

city_id
айди города
city_name
название города
city_country_id
айди страны
country_id
айди страны
country_name
название страны
1Минск11Беларусь
2Витебск11Беларусь
3Москва22Россия
4Питер22Россия

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_idsupplier_name
10000IBM
10001Hewlett Packard
10002Microsoft
10003NVIDIA

У нас есть еще одна таблица orders с тремя полями (order_id, supplier_id и order_date). Она содержит следующие данные:

order_idsupplier_idorder_date
5001251000005.05.2015
5001261000108.02.2016
5001271000406.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_idnameorder_date
10000IBM05.05.2015
10001Hewlett Packard08.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_idsupplier_name
10000IBM
10001Hewlett Packard
10002Microsoft
10003NVIDIA

У нас есть еще одна таблица orders с тремя полями (order_id, supplier_id и order_date). Она содержит следующие данные:

order_idsupplier_idorder_date
5001251000005.05.2015
5001261000108.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_idnameorder_date
10000IBM05.05.2015
10001Hewlett Packard08.02.2016
10002Microsoftnull
10003NVIDIAnull

Строки для 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_idsupplier_name
10000Apple
10001Google

У нас есть вторая таблица orders с тремя полями (order_id, supplier_id и order_date). Она содержит следующие данные:

order_idsupplier_idorder_date
5001251000012.05.2016
5001261000114.05.2016
5001271000218.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_idorder_datesupplier_name
50012512.05.2016Apple
50012614.05.2016Google
50012718.05.2016null

Строка для 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_idsupplier_name
10000IBM
10001Hewlett Packard
10002Microsoft
10003NVIDIA

У нас есть вторая таблица orders с тремя полями (order_id, supplier_id и order_date), которая содержит следующие данные:

order_idsupplier_idorder_date
5001251000012.05.2016
5001261000114.05.2016
5001271000418.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_idsupplier_nameorder_date
10000IBM12.05.2016
10001Hewlett Packard14.05.2016
10002Microsoftnull
10003NVIDIAnull
nullnull18.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

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

City (Города)
IdName
1Москва
2Санкт-Петербург
3Казань
Person (Люди)
NameCityId
Андрей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.NamePerson.CityIdCity.IdCity.Name
Андрей11Москва
Леонид22Санкт-Петербург
Сергей11Москва

OUTER JOIN

Соединение двух таблиц, в результат которого обязательно входят все строки либо одной, либо обеих таблиц.

LEFT OUTER JOIN

Оператор левого внешнего соединения LEFT OUTER JOIN соединяет две таблицы. Порядок таблиц для оператора важен, поскольку оператор не является симметричным.

Заголовок таблицы-результата является объединением (конкатенацией) заголовков соединяемых таблиц.

Тело результата логически формируется следующим образом. Пусть выполняется соединение левой и правой таблиц по предикату (условию) p.

  1. В результат включается внутреннее соединение (INNER JOIN) левой и правой таблиц по предикату p.
  2. Затем в результат добавляются те записи левой таблицы, которые не вошли во внутреннее соединение на шаге 1. Для таких записей поля, соответствующие правой таблице, заполняются значениями NULL.
SELECT *
FROM
  Person -- Левая таблица
  LEFT OUTER JOIN
  City   -- Правая таблица
    ON Person.CityId = City.Id

Результат:

Person.NamePerson.CityIdCity.IdCity.Name
Андрей11Москва
Леонид22Санкт-Петербург
Сергей11Москва
Григорий4NULLNULL
RIGHT OUTER JOIN

Оператор правого внешнего соединения RIGHT OUTER JOIN соединяет две таблицы. Порядок таблиц для оператора важен, поскольку оператор не является симметричным.

Заголовок таблицы-результата является объединением (конкатенацией) заголовков соединяемых таблиц.

Тело результата логически формируется следующим образом. Пусть выполняется соединение левой и правой таблиц по предикату (условию) p.

  1. В результат включается внутреннее соединение (INNER JOIN) левой и правой таблиц по предикату p.
  2. Затем в результат добавляются те записи правой таблицы, которые не вошли во внутреннее соединение на шаге 1. Для таких записей поля, соответствующие левой таблице, заполняются значениями NULL.
SELECT *
FROM
  Person -- Левая таблица
  RIGHT OUTER JOIN
  City   -- Правая таблица
    ON Person.CityId = City.Id

Результат:

Person.NamePerson.CityIdCity.IdCity.Name
Андрей11Москва
Сергей11Москва
Леонид22Санкт-Петербург
NULLNULL3Казань
FULL OUTER JOIN

Оператор полного внешнего соединения FULL OUTER JOIN соединяет две таблицы. Порядок таблиц для оператора неважен, поскольку оператор является симметричным.

Заголовок таблицы-результата является объединением (конкатенацией) заголовков соединяемых таблиц.

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

  1. В результат включается внутреннее соединение (INNER JOIN) первой и второй таблиц по предикату p.
  2. В результат добавляются те записи первой таблицы, которые не вошли во внутреннее соединение на шаге 1. Для таких записей поля, соответствующие второй таблице, заполняются значениями NULL.
  3. В результат добавляются те записи второй таблицы, которые не вошли во внутреннее соединение на шаге 1. Для таких записей поля, соответствующие первой таблице, заполняются значениями NULL.
SELECT *
FROM
  Person
  FULL OUTER JOIN
  City
    ON Person.CityId = City.Id

Результат:

Person.NamePerson.CityIdCity.IdCity.Name
Андрей11Москва
Сергей11Москва
Леонид22Санкт-Петербург
NULLNULL3Казань
Григорий4NULLNULL

CROSS JOIN

Оператор перекрёстного соединения, или декартова произведения CROSS JOIN соединяет две таблицы. Порядок таблиц для оператора неважен, поскольку оператор является симметричным.

Заголовок таблицы-результата является объединением (конкатенацией) заголовков соединяемых таблиц.

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

SELECT *
FROM
  Person
  CROSS JOIN
  City

или

SELECT *
FROM
  Person,
  City

Результат:

Person.NamePerson.CityIdCity.IdCity.Name
Андрей11Москва
Андрей12Санкт-Петербург
Андрей13Казань
Леонид21Москва
Леонид22Санкт-Петербург
Леонид23Казань
Сергей11Москва
Сергей12Санкт-Петербург
Сергей13Казань
Григорий41Москва
Григорий42Санкт-Петербург
Григорий43Казань

Если в предложении 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

Отправить ответ

avatar
  Подписаться  
Уведомление о