Sql join синтаксис. Оператор соединения JOIN

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

Выборки данных из таблиц

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

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

Если же будет необходимость выбрать данные из нескольких таблиц, можно использовать одну из стандартных конструкций для объединения нескольких таблиц.

Способы подключения дополнительных таблиц

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

1. Оператор Inner Join.
2. Left Join или, это второй способ записи, Left Outer Join.
3. Cross Join.
4. Full Join.

Использование операторов объединения таблиц на практике можно усвоить, рассмотрев применение оператора SQL - Inner Join. Пример его использования будет выглядеть следующим образом:

Язык SQL и оператор Join Inner Join можно использовать не только для объединения двух и более таблиц, но и для подключения иных подзапросов, что значительно облегчает работу администраторов базы данных и, как правило, может значительно ускорить выполнение определенных, сложных по структуре запросов.

Объединение данных в таблицах построчно

Если рассматривать подключение большого количества подзапросов и сборку данных в единую таблицу строка за строкой, то можно использовать также операторы Union, и Union All.

Применение этих конструкций будет зависеть от поставленной перед разработчиком задачи и результата, которого он хочет достичь в итоге.

Описание оператора Inner Join

В большинстве случаев для объединения нескольких таблиц в языке SQL используется оператор Inner Join. Описание Inner Join в SQL довольно простое для понимания среднестатистического программиста, который только начинает разбираться в базах данных. Если рассмотреть описание механизма работы этой конструкции, то получим следующую картину. Логика оператора в целом построена на возможности пересечения и выборки только тех данных, которые есть в каждой из входящих в запрос таблиц.

Если рассмотреть такую работу с точки зрения графической интерпретации, то получим структуру оператора SQL Inner Join, пример которой можно показать с помощью следующей схемы:

К примеру, мы имеем две таблицы, схема которых показана на рисунке. Они в свою очередь, имеют разное количество записей. В каждой из таблиц есть поля, которые связаны между собой. Если попытаться пояснить работу оператора исходя из рисунка, то возвращаемый результат будет в виде набора записей из двух таблиц, где номера связанных между собой полей совпадают. Проще говоря, запрос вернет только те записи (из таблицы номер два), данные о которых есть в таблице номер один.

Синтаксис оператора Inner Join

Как уже говорилось ранее, оператор Inner Join, а именно его синтаксис, необычайно прост. Для организации связей между таблицами в пределах одной выборки достаточно будет запомнить и использовать следующую принципиальную схему построения оператора, которая прописывается в одну строчку программного SQL-кода, а именно:

• Inner Join [Имя таблицы] on [ключевое поле из таблицы, к которой подключаем] = [Ключевому полю подключаемой таблицы].

Для связи в данном операторе используются главные ключи таблиц. Как правило, в группе таблиц, которые хранят информацию о сотрудниках, ранее описанные Person и Subdivision имеют хотя бы по одной похожей записи. Итак, рассмотрим подробнее Inner Join, пример которого был показан несколько ранее.

Пример и описание подключения к выборке одной таблицы

У нас есть таблица Person, где хранится информация обо всех сотрудниках, работающих в компании. Сразу отметим, что главным ключем данной таблицы является поле - Pe_ID. Как раз по нему и будет идти связка.

Вторая таблица Subdivision будет хранить информацию о подразделениях, в которых работают сотрудники. Она, в свою очередь, связана с помощью поля Su_Person с таблицей Person. О чем это говорит? Исходя из схемы данных можно сказать, что в таблице подразделений для каждой записи из таблицы «Сотрудники» будет информация об отделе, в котором они работают. Именно по этой связи и будет работать оператор Inner Join.

Для более понятного использования рассмотрим оператор SQL Inner Join (примеры его использования для одной и двух таблиц). Если рассматривать пример для одной таблицы, то тут все довольно просто:

Пример подключения двух таблиц и подзапроса

Оператор SQL Inner Join, примеры использования которого для выборки данных из нескольких таблиц можно организовать вышеуказанным образом, работает по чуть усложненному принципу. Для двух таблиц усложним задачу. Скажем, у нас есть таблица Depart, в которой хранится информация обо всех отделах в каждом из подразделений. В в эту таблицу записан номер подразделения и номер сотрудника и нужно дополнить выборку данных названием каждого отдела. Забегая вперед, стоит сказать, что для решения этой задачи можно воспользоваться двумя методами.

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

Стоит отметить, что такая конструкция не всегда может ускорить работу запроса. Иногда бывают случаи, когда приходится использовать дополнительно выборку данных во временную таблицу (если их объем слишком большой), а потом ее объединять с основной выборкой.

Пример использования оператора Inner Join для выборок из большого количества таблиц

Построение сложных запросов подразумевает использование для выборки данных значительного количества таблиц и подзапросов, связанных между собой. Этим требованиям может удовлетворить SQL Inner Join синтаксис. Примеры использования оператора в данном случаем могут усложняться не только выборками из многих мест хранения данных, но и с большого количества вложенных подзапросов. Для конкретного примера можно взять выборку данных из системных таблиц (оператор Inner Join SQL). Пример - 3 таблицы - в этом случае будет иметь довольно сложную структуру.

В данном случае подключено (к основной таблице) еще три дополнительно и введено несколько условий выбора данных.

При использовании оператора Inner 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 (внутреннее соединение)

Запрос с оператором INNER JOIN предназначен для соединения таблиц и вывода результирующей таблицы, в которой данные полностью пересекаются по условию, указанному после ON.

То же самое делает и просто JOIN. Таким образом, слово INNER - не обязательное.

Пример 1. Есть база данных портала объявлений. В ней есть таблица Categories (категории объявлений) и Parts (части, или иначе - рубрики, которые и относятся к категориям). Например, части Квартиры, Дачи относятся к категории Недвижимость, а части Автомобили, Мотоциклы - к категории Транспорт. Эти таблицы с заполненными данными имеют следующий вид.

Таблица Parts:

Заметим, что в таблице 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

В результирующей таблице нет Книг, так как эта запись ссылается на категорию, которой нет в таблице Categories, и Техники, так как эта запись имеет внешний ключ в таблице Categories, на который нет ссылки в таблице Parts.

В ряде случаев при соединениях таблиц составить менее громоздкие запросы можно с помощью предиката EXISTS и без использования JOIN.

Есть база данных "Театр". Таблица Play содержит данные о постановках. Таблица Team - о ролях актёров. Таблица Actor - об актёрах. Таблица Director - о режиссёрах. Поля таблиц, первичные и внешние ключи можно увидеть на рисунке ниже (для увеличения нажать левой кнопкой мыши).

Пример 3. Вывести список актеров, которые в одном спектакле играют более одной роли, и количество их ролей.

Оператор JOIN использовать 1 раз. Использовать HAVING, GROUP BY .

Подсказка. Оператор HAVING применяется к числу ролей, подсчитанных агрегатной функцией COUNT.

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

Запрос с оператором 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

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

В результирующей таблице, в отличие от таблицы из примера 1, есть Книги, но значение столбца Цены (Price) у них - NULL, так как эта запись имеет идентификатор категории, которой нет в таблице Categories.

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

Запрос с оператором 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

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

В результирующей таблице, в отличие от таблицы из примера 1, есть запись с категорией 45 и ценой 65,00, но значение столбца Части (Part) у неё - NULL, так как эта запись имеет идентификатор категории, на которую нет ссылок в таблице Parts.

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

Запрос с оператором 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

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

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

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

Реляционные базы данных должны подчиняться требованиям целостности и неизбыточности данных, в связи с чем данные об одном бизнес-процессе могут содержаться не только в одной, двух, но и в трёх и более таблицах. В этих случаях для анализа данных используются цепочки соединённых таблиц: например, в одной (первой) таблице содержится некоторый количественный показатель, вторую таблицу с первой и третьей связывают внешние ключи - данные пересекаются, но только третья таблица содержит условие, в зависимости от которого может быть выведен количественный показатель из первой таблицы. И таблиц может быть ещё больше. При помощи оператора 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.

Представим, что сегодня "2018-04-02", то есть это значение принимает функция CURDATE() - текущая дата . Требуется узнать, к каким категориям принадлежат объявления, срок публикации которых истекает сегодня. Названия категорий есть только в таблице CATEGORIES, а даты истечения срока публикации объявлений - только в таблице ADS. В таблице PARTS - части категорий (или проще, подкатегории) опубликованных объявлений. Но внешним ключом Cat_ID таблица PARTS связана с таблицей CATEGORIES, а таблица ADS связана внешним ключом Part_ID с таблицей PARTS. Поэтому соединяем в одном запросе три таблицы и этот запрос можно с максимальной корректностью назвать цепочкой.

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

Результат запроса - таблица, содержащая названия двух категорий - "Недвижимость" и "Транспорт":

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

Использование оператора SQL CROSS JOIN в наиболее простой форме - без условия соединения - реализует операцию декартова произведения в реляционной алгебре . Результатом такого соединения будет сцепление каждой строки первой таблицы с каждой строкой второй таблицы. Таблицы могут быть записаны в запросе либо через оператор CROSS JOIN, либо через запятую между ними.

Пример 9. База данных - всё та же, таблицы - Categories и Parts. Реализовать операцию декартова произведения этих двух таблиц.

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

SELECT (*) Categories CROSS JOIN Parts

Или без явного указания CROSS JOIN - через запятую:

SELECT (*) Categories , Parts

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

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

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

Пример 10. Та же база данных портала объявлений, таблицы Categories и Parts. Используя перекрестное соединение, соединить таблицы так, чтобы данные полностью пересекались по условию. Условие - совпадение идентификатора категории в таблице Categories и ссылки на категорию в таблице Parts.

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

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

И это совпадение не случайно. Запрос c перекрестным соединением по условию соединения полностью аналогичен запросу с внутренним соединением - INNER JOIN - или, учитывая, что слово INNER - не обязательное, просто JOIN.

Таким образом, какой вариант запроса использовать - вопрос стиля или даже привычки специалиста по работе с базой данных. Возможно, перекрёстное соединение с условием для двух таблиц может представляться более компактным. Но преимущество перекрестного соединения для более чем двух таблиц (это также возможно) весьма спорно. В этом случае WHERE-условия пересечения перечисляются через слово AND. Такая конструкция может быть громоздкой и трудной для чтения, если в конце запроса есть также секция WHERE с условиями выборки.

Реляционные базы данных и язык SQL

Если тип_соединения явно не указан, то подразумевается INNER JOIN. Обратите внимание, что существует много типов соединений, каждый из которых имеет свои правила и функциональность, описанные в разделе «Общие правила».

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

Мы, как правило, не рекомендуем использовать экономящие труд ключевые слова типа NATURAL, поскольку это предложение автоматически не обновляется при изменении структуры базовых таблиц. Следовательно, инструкции, в которых используются данные предложения, могут приводить к появлению ошибок при изменении структуры таблицы, если структура запроса не изменяется.

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

Платформа DB2 поддерживает соединения INNER и OUTER с использованием предложения ON. Не поддерживаются синтаксические конструкции NATURAL и CROSS, а также USING. Синтаксис предложения JOIN в DB2 выглядит следующим образом.

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

MySQL

Платформа MySQL поддерживает большую часть синтаксиса ANSI, за исключением того, что соединения с префиксом NATURAL поддерживаются только для внешних, но не для внутренних соединений. Синтаксис предложения JOIN в MySQL следующий.

FROM таблица { | {{ | |

| [{LEFT | RIGHT | FULL} ]} JOIN соединяемая_таблица

{ON условие_соединения1 [{AND|OR} условие_соединения2] […]] | USING (столбец1 [, …])}}} […]

STRAIGHT JOIN

Заставляет оптимизатор соединять таблицы в том порядке, как они упоминаются в предложении FROM.

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

Oracle

Платформа Oracle полностью поддерживает стандартный синтаксис ANSI для предложения JOIN. Однако в Oracle поддержка синтаксиса ANSI появилась, только начиная с версии 9. Соответственно в более старом коде Oracle используются исключительно соединения с предложением WHERE. В старом синтаксисе Oracle для внешних тета-соединений поддерживалось добавление знака + к именам столбцов на стороне, противоположной внешней таблице. Старый синтаксис внешних тета-соединений в Oracle исходит из того факта, что в таблицу, поставляющую строки с пустыми значениями, эти строки были добавлены. Платформа Oracle отражает этот факт, добавляя «+» к имени столбца на стороне, противоположной направлению соединения.

Например, в следующем запросе производится соединение RIGHT OUTER JOIN таблиц authors и publishers. Старый синтаксис Oracle будет выглядеть следующим образом.

SELECT a.au_lname AS "first name", a.au_fname AS "last name", p.pub_name AS "publisher" FROM authors a, publishers p WHERE a.city(+)=p.city ORDER BY a.au_lname DESC;

Тот же самый запрос, написанный с использованием синтаксиса ANSI, будет выглядеть так.

SELECT a.au_lname AS "first name", a.au_fname AS "last name", p.pub_name AS "publisher" FROM authors AS a RIGHT OUTER JOIN publishers AS p ON a.city=p.city ORDER BY a.au_lname DESC;

Дополнительные примеры с использованием предложения JOIN приведены в разделе «Общие правила».

Платформа Oracle - единственная, которая позволяет использовать секционированные внешние соединения (partitioned outer joins), которые полезны для заполнения пустых мест в результирующих наборах, возникающих из-за фрагментированного хранения данных. Например, предположим, что записи о продукции хранятся в таблице manufacturing с двумя ключами - день и идентификатор продукта. В таблице хранятся записи, показывающие количество произведенного продукта в любой день, когда продукция производилась, но отсутствуют строки, соответствующие дням, когда продукция не производилась. Такая структура считается фрагментированной, поскольку в списке строк не отражены все дни и все продукты. Для проведения расчетов и создания отчетов хорошо бы создать такой набор данных, в котором производство каждого продукта было бы отражено по всем дням, независимо от того, производился продукт или нет. Это можно просто сделать при помощи секционированного внешнего соединения, поскольку оно позволяет определить логический раздел и применить внешнее соединение к каждому значению этого раздела. Ниже приводится пример с таблицей manufacturing, которая соединяется при помощи внешнего секционированного соединения с таблицей times, чтобы создать для каждого значения product_id полный набор дат в указанном диапазоне.

SELECT times.time_id AS time, product_id AS id, quantity AS qty FROM manufacturing PARTITION BY (product_id) RIGHT OUTER JOIN times ON (manufacturing.time_id=times.time_id) WHERE manufacturing, time_id BETWEEN TO_DATE("01/10/05", "DD/MM/YY") AND TO_DATE("06/10/05", "DD/MM/YY") ORDER BY 2, 1;

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

PostgreSQL

Платформа PostgreSQL полностью поддерживает стандарт ANSI. Примеры приводятся в разделе «Общие правила».

SQL Server

Платформа SQL Server поддерживает соединения типов INNER, OUTER и CROSS с использованием предложения ON. SQL Server не поддерживает синтаксические конструкции NATURAL и USING. Синтаксис инструкции JOIN в SQL Server следующий.

FROM таблица

{{ | | | [{LEFT | RIGHT | FULL} ]} JOIN соединяемая_таблица

{ON условие_соединения! [{AND|OR} условие_соединения2] […]]} […]

Примеры приведены в разделе «Общие правила».

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

Эквисоединение

При эквисоединении (equi-join) две или более таблиц соединяются на основании условия равенства между столбцами. Другими словами, один и тот же столбец имеет одинаковое значение во всех соединяемых таблицах. Ниже приведен пример применения эквисоединения:

SQL> SELECT e.last_name, d.dept FROM emp e, dept d WHERE e.emp_id = d.emp_id;

Для показанного выше оператора соединения также можно использовать и следующий новый синтаксис:

SQL> SELECT e.last_name, d.dept FROM emp e JOIN dept d USING (emp_id);

При желании соединить несколько столбцов, можно перечислить их имена в виде разделенного запятыми списка, например: USING (dept_id , emp_name).

Естественное соединение

Естественным соединением (natural join) называется эквисоединение, при котором столбцы, которые должны сопоставляться для выполнения соединения, специально не указываются. Oracle автоматически определяет подлежащие соединению столбцы на основании совпадающих столбцов в двух таблицах. Ниже приведен пример применения естественного соединения:

SQL> SELECT e.last_name, d.dept FROM emp e NATURAL JOIN dept d;

В этом примере условием для выполнения соединения служит наличие идентичных значений в столбце last_name в таблицах emp и dept .

Рефлексивное соединение

Под рефлексивным соединением (self join) подразумевается соединение таблицы с самой собой за счет использования псевдонимов. В следующем примере осуществляется соединение таблицы employees с самой собой при помощи псевдонима с удалением всех дублированных строк.

SQL> DELETE FROM employees X WHERE ROWID > 2 (select MIN(rowid) FROM employees Y 3 where X.key_values = Y.key_values);

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

Внутреннее соединение (inner join), также называемое простым соединением (simple join), предусматривает возврат всех строк, которые удовлетворяют указанному условию соединения. Раньше в синтаксисе внутреннего соединения для указания того, каким образом должны соединяться таблицы, нужно было использовать конструкцию WHERE , например, так:

SQL> SELECT e.flast_name, d.dept FROM emp e, dept d WHERE e.emp_id = d.emp_id;

Теперь Oracle позволяет задавать критерии соединения в синтаксисе внутреннего (или простого) соединения за счет применения новой конструкции ON или USING , например:

SQL> SELECT DISTINCT NVL(dname, "No Dept"), COUNT(empno) nbr_emps FROM emp JOIN DEPT ON emp.deptno = dept.deptno WHERE emp.job IN ("MANAGER", "SALESMAN", "ANALYST") GROUP BY dname;

Внешнее соединение

Внешнее соединение (outer join) применяется для возврата всех строк, которые удовлетворяют указанному условию соединения, плюс некоторых или всех строк из таблицы, в которой нет подходящих строк, удовлетворяющих указанному условию соединения. Существуют три вида внешнего соединения: левое внешнее соединение (left outer join), правое внешнее соединение (right outer join) и полное внешнее соединение (full outer join). В операторе полного внешнего соединения слово OUTER обычно опускается.

This Oracle tutorial explains how to use JOINS (inner and outer) in Oracle with syntax, visual illustrations, and examples.

Description

Oracle JOINS are used to retrieve data from multiple tables. An Oracle JOIN is performed whenever two or more tables are joined in a SQL statement.

There are 4 different types of Oracle joins:

• Oracle INNER JOIN (or sometimes called simple join)
• Oracle LEFT OUTER JOIN (or sometimes called LEFT JOIN)
• Oracle RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)
• Oracle FULL OUTER JOIN (or sometimes called FULL JOIN)

So let"s discuss Oracle JOIN syntax, look at visual illustrations of Oracle JOINS, and explore Oracle JOIN examples.

INNER JOIN (simple join)

Chances are, you"ve already written a statement that uses an Oracle INNER JOIN. It is the most common type of join. Oracle INNER JOINS return all rows from multiple tables where the join condition is met.

Syntax

The syntax for the INNER JOIN in Oracle/PLSQL is:

SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.column;

Visual Illustration

In this visual diagram, the Oracle INNER JOIN returns the shaded area:

The Oracle INNER JOIN would return the records where table1 and table2 intersect.

Example

Here is an example of an Oracle INNER JOIN:

This Oracle INNER JOIN example would return all rows from the suppliers and orders tables where there is a matching supplier_id value in both the suppliers and orders tables.

Let"s look at some data to explain how the INNER JOINS work:

We have a table called suppliers

We have another table called orders

If we run the Oracle SELECT statement (that contains an INNER JOIN) below:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers INNER JOIN orders ON suppliers.supplier_id = orders.supplier_id;

The rows for Microsoft and NVIDIA from the supplier table would be omitted, since the supplier_id"s 10002 and 10003 do not exist in both tables. The row for 500127 (order_id) from the orders table would be omitted, since the supplier_id 10004 does not exist in the suppliers table.

Old Syntax

As a final note, it is worth mentioning that the Oracle INNER JOIN example above could be rewritten using the older implicit syntax as follows (but we still recommend using the INNER JOIN keyword syntax):

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers, orders WHERE suppliers.supplier_id = orders.supplier_id;

LEFT OUTER JOIN

Another type of join is called an Oracle LEFT OUTER JOIN. This type of join returns all rows from the LEFT-hand table specified in the ON condition and only

Syntax

The syntax for the Oracle LEFT OUTER JOIN is:

SELECT columns FROM table1 LEFT JOIN table2 ON table1.column = table2.column;

In some databases, the LEFT OUTER JOIN keywords are replaced with LEFT JOIN.

Visual Illustration

In this visual diagram, the Oracle LEFT OUTER JOIN returns the shaded area:

The Oracle LEFT OUTER JOIN would return the all records from table1 and only those records from table2 that intersect with table1 .

Example

Here is an example of an Oracle LEFT OUTER JOIN:

This LEFT OUTER JOIN example would return all rows from the suppliers table and only those rows from the orders table where the joined fields are equal.

> in the result set.

Let"s look at some data to explain how LEFT OUTER JOINS work:

We have a table called suppliers with two fields (supplier_id and supplier_name). It contains the following data:

orders with three fields (order_id, supplier_id, and order_date). It contains the following data:

If we run the SELECT statement (that contains a LEFT OUTER JOIN) below:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers LEFT OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id;

Our result set would look like this:

The rows for Microsoft and NVIDIA would be included because a LEFT OUTER JOIN was used. However, you will notice that the order_date field for those records contains a value.

Old Syntax

As a final note, it is worth mentioning that the LEFT OUTER JOIN example above could be rewritten using the older implicit syntax that utilizes the outer join operator (+) as follows (but we still recommend using the LEFT OUTER JOIN keyword syntax):

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers, orders WHERE suppliers.supplier_id = orders.supplier_id(+);

RIGHT OUTER JOIN

Another type of join is called an Oracle RIGHT OUTER JOIN. This type of join returns all rows from the RIGHT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).

Syntax

The syntax for the Oracle RIGHT OUTER JOIN is:

SELECT columns FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;

In some databases, the RIGHT OUTER JOIN keywords are replaced with RIGHT JOIN.

Visual Illustration

In this visual diagram, the Oracle RIGHT OUTER JOIN returns the shaded area:

The Oracle RIGHT OUTER JOIN would return the all records from table2 and only those records from table1 that intersect with table2 .

Example

Here is an example of an Oracle RIGHT OUTER JOIN:

This RIGHT OUTER JOIN example would return all rows from the orders table and only those rows from the suppliers table where the joined fields are equal.

If a supplier_id value in the orders table does not exist in the suppliers table, all fields in the suppliers table will display as > in the result set.

Let"s look at some data to explain how RIGHT OUTER JOINS work:

We have a table called suppliers with two fields (supplier_id and supplier_name). It contains the following data:

We have a second table called orders with three fields (order_id, supplier_id, and order_date). It contains the following data:

If we run the SELECT statement (that contains a RIGHT OUTER JOIN) below:

SELECT orders.order_id, orders.order_date, suppliers.supplier_name FROM suppliers RIGHT OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id;

Our result set would look like this:

The row for 500127 (order_id) would be included because a RIGHT OUTER JOIN was used. However, you will notice that the supplier_name field for that record contains a value.

Old Syntax

As a final note, it is worth mentioning that the RIGHT OUTER JOIN example above could be rewritten using the older implicit syntax that utilizes the outer join operator (+) as follows (but we still recommend using the RIGHT OUTER JOIN keyword syntax):

SELECT orders.order_id, orders.order_date, suppliers.supplier_name FROM suppliers, orders WHERE suppliers.supplier_id(+) = orders.supplier_id;

FULL OUTER JOIN

Another type of join is called an Oracle FULL OUTER JOIN. This type of join returns all rows from the LEFT-hand table and RIGHT-hand table with nulls in place where the join condition is not met.

Syntax

The syntax for the Oracle FULL OUTER JOIN is:

SELECT columns FROM table1 FULL JOIN table2 ON table1.column = table2.column;

In some databases, the FULL OUTER JOIN keywords are replaced with FULL JOIN.

Visual Illustration

In this visual diagram, the Oracle FULL OUTER JOIN returns the shaded area:

The Oracle FULL OUTER JOIN would return the all records from both table1 and table2 .

Example

Here is an example of an Oracle FULL OUTER JOIN:

This FULL OUTER JOIN example would return all rows from the suppliers table and all rows from the orders table and whenever the join condition is not met, would be extended to those fields in the result set.

If a supplier_id value in the suppliers table does not exist in the orders table, all fields in the orders table will display as > in the result set. If a supplier_id value in the orders table does not exist in the suppliers table, all fields in the suppliers table will display as in the result set.

Let"s look at some data to explain how FULL OUTER JOINS work:

We have a table called suppliers with two fields (supplier_id and supplier_name). It contains the following data:

We have a second table called orders with three fields (order_id, supplier_id, and order_date). It contains the following data:

If we run the SELECT statement (that contains a FULL OUTER JOIN) below:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers FULL OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id;

Our result set would look like this:

The rows for Microsoft and NVIDIA would be included because a FULL OUTER JOIN was used. However, you will notice that the order_date field for those records contains a value.

The row for supplier_id 10004 would be also included because a FULL OUTER JOIN was used. However, you will notice that the supplier_id and supplier_name field for those records contain a value.

Old Syntax

As a final note, it is worth mentioning that the FULL OUTER JOIN example above could not have been written in the old syntax without using a