Соединение таблиц SQL. Внутренние разъемы, внешний INNER OUTER JOIN

1. Объединение таблиц SQL - общие правила
2. INNER JOIN - внутренняя связь
3. Логика объединения таблиц INNER JOIN
4. Потенциальные проблемы и ошибки
5. OUTER JOIN - внешнее соединение
6. LEFT OUTER JOIN - соединение слева открыто
7. RIGHT OUTER JOIN - внешнее подключение справа
8. FULL OUTER JOIN - полное внешнее соединение
9. CROSS JOIN - декартово произведение
10. SELF JOIN - соединяя стол с собой
11. Совместимость синтаксиса для объединения таблиц со стандартами ANSI SQL

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

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

В этой статье вы найдете описание всего спектра возможностей FROM, то есть всех методов объединения таблиц, наборов и определения условий соединения. Помните, что это могут быть таблицы, представления, общие выражения таблиц (CTE), переменные и табличные функции или подзапросы. Для простоты я буду называть их взаимозаменяемо - таблицы / коллекции - имея в виду все табличные объекты, на которые мы можем ссылаться в FROM.

Объединение таблиц SQL - общие правила

Напоминаем, что FROM - это первый шаг в обработке запросов. Каждый последующий основан на промежуточной, результирующей виртуальной таблице, предыдущей. Набор, полученный после обработки всего шага (например, ОТ), является следующим вводом (ГДЕ).

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

Например, набор результатов (VT) операции объединения трех таблиц с внутренним соединением INNER JOIN будет описан всеми столбцами, тремя входными таблицами.

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

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

Объединение нескольких наборов (три и более) сводится к нескольким объединениям двух таблиц . В следующей главе этого курса я опишу детали обработка запросов к нескольким таблицам ,

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

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

Исходный код структур, на которых я представляю конкретные примеры, для скачивания здесь ,

INNER JOIN - внутренняя связь

В результате внутреннего соединения (INNER JOIN) мы получаем таблицу результатов (VT), состоящую из всех столбцов входных таблиц.

Таблица результатов будет содержать только те элементы, для которых будут выполнены условия внутреннего соединения (в трехвалентной логике результат должен быть ИСТИНА).
Предложение FROM вместе с условиями, указанными в ON, является первым местом для фильтрации записей. Все элементы, для которых результат не будет достигнут (FALSE и UNKNOWN), будут отклонены.

Логика объединения таблиц INNER JOIN

Понимание принципов внутренних соединений имеет решающее значение. Это общая часть всех типов соединений ВНУТРЕННЕГО и ВНЕШНЕГО СОЕДИНЕНИЯ.

На первом этапе выполняется декартово произведение обеих таблиц. Это комбинация каждого элемента множества A со всем множеством B.

Возможно, вам будет трудно принять :), но я вас успокою - логическая обработка запроса и его физическая реализация совершенно разные. Реляционный движок прекрасно справляется с этим. Это не означает, что каждый раз, когда вы обращаетесь к таблице, все ее записи читаются. Однако, просматривая призму правил SQL, логические шаги - мы предполагаем, что это так.

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

После определения декартового произведения для каждой строки вычисляется результат (или условие), указанный в ON. Здесь мы встречаемся с трехвалентной логикой . Результат может быть получен = ИСТИНА, невыполнен = ЛОЖЬ или неизвестен НЕИЗВЕСТНО (например, сравнение с НУЛЕВЫМ - больше по этой теме в статья о NULL ).

Последний шаг - отклонить все строки, которые не соответствуют условиям в ON. В наборе результатов остаются только элементы, для которых result = TRUE .

Вся диаграмма процесса внутреннего связывания проиллюстрирована на следующей диаграмме:

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

SELECT * FROM dbo. EMP как e INNER JOIN. АВТОМОБИЛЬ как c ON е. IdPrac = C. IdPrac

ВНУТРЕННЕЕ СОЕДИНЕНИЕ является симметричным соединением, и не имеет значения, объединяем ли мы таблицу А с В или наоборот. Аналогично с условиями в ON. Для полноты картины имеет смысл сохранить порядок атрибутов в положении ON с той же стороны, что и при указании исходных таблиц для оператора JOIN.

Следующие условия комбинации эквивалентны:

ОТ ДБО. EMP e INNER JOIN dbo. CAR c ON е. IdPrac = c. IdPrac ОТ dbo. EMP e INNER JOIN dbo. CAR c ON C. IdPrac = е. IdPrac ОТ dbo. АВТОМОБИЛЬ С ВНУТРЕННИМ ПРИСОЕДИНЕНИЕМ DBO EMP e ON e. IdPrac = C. IdPrac FROM dbo. АВТОМОБИЛЬ c ВНУТРЕННИМ РЕЙТИНГОМ dbo. EMP e ON c. IdPrac = е. IdPrac

Потенциальные проблемы и ошибки

Вот как мы пишем условия объединений, точно так же, как в ГДЕ может повлиять производительность (использование функций и т. Д.).

Как правило, INNER JOIN не создает никаких проблем. Тем не менее, давайте посмотрим на условие присоединения таким образом сохранено:

SELECT * FROM dbo. EMP e INNER JOIN dbo. АВТОМОБИЛЬ c ВКЛ. Е. IdPrac = 1

Поскольку только одна строка таблицы dbo.EMP соответствует условию, указанному в ON (IdPrac = 1), все строки, являющиеся результатом декартового произведения этой строки, будут возвращены со всем набором записей таблицы dbo.CAR.

Это доказывает, что декартово произведение фактически выполняется при логической обработке объединений. Сначала A x B, затем фильтрация. Мы можем пойти дальше и сохранить условие в этой арке как ON 1 = 1 - тогда полный декартовой продукт гарантирован, потому что ничего не будет отфильтровано.

OUTER JOIN - внешнее соединение

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

1. Сначала выполняется декартово произведение таблицы A и таблицы B (мы соединяем каждое с каждым).
2. Для каждой строки определяется результат условий подключения (определенный в ON) - TRUE, FALSE или UNKNOWN.
3. Затем удалите все элементы из промежуточного набора результатов, для которых результат соединения (из п.2) отличается от ИСТИНА /

Во внутренних связях это было все. Дополнительный шаг добавлен во внешний.

В зависимости от типа - LEFT, RIGHT или FULL JOIN, выполняется завершение набора, все элементы таблицы появляются после оператора LEFT, RIGHT или BOTH JOIN, для которого не был выполнен результат условий (FALSE или UNKNOWN).

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

LEFT OUTER JOIN - соединение слева открыто

Мы действуем так же, как в INNER JOIN. Наконец, мы завершаем набор результатов (INNER JOIN - это только элемент C) со всеми элементами таблицы, стоящими слева от оператора JOIN (это будут записи A и B).

Поскольку дополнительные строки также должны быть описаны во всех столбцах соединенных таблиц, значения атрибутов в этом случае TableB (справа от JOIN) будут неизвестны, поэтому они будут просто NULL.

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

ВЫБЕРИТЕ е. Имя, е. Фамилия, е. Должность, в. Бренд - LEFT JOIN и LEFT OUTER JOIN означают одно и то же ОТ dbo. EMP e LEFT JOIN dbo. АВТОМОБИЛЬ c ВКЛ. Е. IdPrac = C. IdPrac

RIGHT OUTER JOIN - внешнее подключение справа

Та же история, что и LEFT JOIN только в другую сторону :). Две коллекции интегрированы внутри (INNER JOIN), в конце все несопоставленные элементы таблицы добавляются на правой стороне оператора JOIN (элементы D и E). Поскольку значения столбцов слева неизвестны, они будут NULL.

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

выберите C. Марка, C. NrRej, C. Ежегодник, е. Имя + '' + е. Фамилия в качестве сотрудника из dbo. EMP e RIGHT JOIN DBO. АВТОМОБИЛЬ c на е. IdPrac = C. IdPrac

Как вы можете видеть, внешние швы LEFT и RIGHT не симметричны. Результат зависит от положения таблиц относительно оператора JOIN. Не важно записывать условия (то есть, что после ВКЛ). Вышеупомянутый запрос может быть сохранен так же, как и соединение LEFT JOIN, и результат будет идентичным.

- Я только изменил порядок таблиц CAR и EMP и использовал LEFT JOIN SELECT c. Бренд, c. NrRej, c. Ежегодник, e. Имя + '' + е. Фамилия как Employee FROM dbo. CAR C LEFT JOIN DBO. EMP e на е. IdPrac = C. IdPrac

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

Если вы уже знаете, как работают INNER, LEFT, RIGHT - тогда вы знаете, как работает FULL JOIN! Дополнением набора результатов являются все элементы обоих комбинированных наборов. Как и прежде, мы не будем придумывать неизвестные значения. Дополнительные элементы набора A (стоящие слева от оператора JOIN) будут дополняться атрибутами таблицы B NULLami. Точно так же он будет дополнен вторым набором.

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

ВЫБЕРИТЕ е. Имя, е. Фамилия, е. Должность, в. Марка - FULL JOIN - это сокращение от FULL OUTER JOIN FROM dbo. EMP e FULL JOIN dbo. АВТОМОБИЛЬ c ВКЛ. Е. IdPrac = C. IdPrac

CROSS JOIN - декартово произведение

Комбинация коллекций используется редко. Его метод работы тривиален - он связывает каждую строку таблицы A с каждой строкой таблицы B. Как единственный, невозможно создать условия подключения в ON, потому что он предназначен для соединения всего со всем.

Эффект объединения двух таблиц, содержащих 100 записей, - это таблица с 10000 строк, описанная со всеми столбцами. Так что, если вы хотите проверить силу DBA, вы можете попробовать соединить несколько таблиц среднего размера :)

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

SELECT * FROM dbo. EMP, дбо. CAR

Поэтому мы должны избегать использования условий подключения в WHERE (очень старый способ, несовместимый с ANSI SQL: 92).

SELF JOIN - соединяя стол с собой

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

Вызовы SELF JOIN всегда относятся к числу уже известных - INNER, CROSS или OUTER JOIN, в T-SQL запись SELF JOIN не используется . В структуре таблицы X dbo.EMP у нас есть определенная ссылка между столбцами IdManager и IdPrac.

выберите IdPrac, имя, фамилию, должность, IdManager из EMP

Давайте отобразим основные данные для всех сотрудников вместе с информацией о непосредственном руководителе.
Поскольку есть один человек (БОСС), у которого нет руководителя, мы должны использовать внешнее соединение в этой задаче. Чтобы иметь возможность комбинировать две идентичные таблицы, мы должны обязательно использовать псевдонимы (e1 и e2).

ВЫБЕРИТЕ е1. Имя + '' + e1. Фамилия сотрудника, e1. Положение, е2. Имя + '' + e2. Фамилия менеджера, e2. Положение как ManManage ОТ dbo. EMP e1 ЛЕВОЕ НАРУЖНОЕ СОЕДИНЕНИЕ dbo. EMP e2 на e1. IdManager = e2. IdPrac

Этот пример основан на существующем отношении, внешнем ключе таблицы dbo.EMP к себе, но его не нужно определять явно и постоянно.

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

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

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

Наконец, более «продвинутый» пример, сочетающий в себе различные приемы написания запросов, представлен в этой главе курса. Он иллюстрирует внутреннее соединение SELF JOIN таблицы dbo.HIST, в которой хранится информация о пробеге автомобилей в виде «журнала».

Сценарий таков, что каждый месяц каждый сотрудник должен вводить статус счетчика своей служебной машины. Запрос состоит в том, чтобы отобразить ежемесячный отчет о пробеге для каждого автомобиля за период отпуска. Для этого я буду использовать методы выражения общего массива , внутреннее соединение и функция ROW_NUMBER () , Обратите внимание, что я делаю арифметические операции при объединении атрибутов (добавляет 1, чтобы получить смещения при чтении) и комбинирую несколько условий в предложении ON.

С LogTab as (- вы можете запустить тестирование только содержимого CTE, - чтобы проверить, что возвращает и что будет ядром (Table LogTab), которое затем я использую SELECT *, ROW_NUMBER () OVER (разделение по номеру Order by DtMomiar) как IdUniqueRange FROM dbo. HIST, где DtPomiaru между «2012-06-01» и «2012-08-31») ВЫБЕРИТЕ l1. NrRej, YEAR (l1. Dt. Measurement) как год, MONTH (L1. Dt. Measurement) как Miesiac, l1. Пробег как Пробег, Старт, l2. Пробег как ПробегEND, l1. Пробег - l2. Пробег как Дельта ОТ LogTab l1 ВНУТРЕННЯЯ СОЕДИНЕНИЕ LogTab l2 ON l1. IdUniqueRange = l2. IdUniqueRange + 1 и l1. № Розы = l2. NrRej ЗАКАЗАТЬ ПО l1. NrRej, l1. DtPomiaru

Совместимость синтаксиса для объединения таблиц со стандартами ANSI SQL

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

Статья, представленная в этой статье (INNER, LEFT OUTER, CROSS, условие ON), соответствует стандарту ANSI SQL: 92 и должна использоваться в производственных базах данных. Информативно, есть другие, более старые методы записи, которые не должны больше использоваться.

Внутреннее соединение с условием вместо ВКЛ - в ГДЕ.

- эквивалентно INNER JOIN - условие соединения только при фильтрации в ГДЕ - ИЗБЕГАЙТЕ !!! потому что это легко для ошибки, и декартово произведение готово :) ВЫБЕРИТЕ е. Имя, е. Фамилия, е. Стенд, IS NULL (c. Marka, '-') как Car FROM dbo. EMP e, dbo. АВТОМОБИЛЬ c ГДЕ е. IdPrac = c. IdPrac

Похожие

Комментарии