Присоединение таблиц в запросах — это базовый инструмент в работе с базами данных. Давайте рассмотрим какие присоединения (JOIN) бывают, и что от этого меняется в результатах запроса.
Для начала создадим две таблицы, над которыми будем проводить опыты. Это таблица с именами сотрудников и словарь с перечнем должностей.
Т.е. чтобы узнать должность сотрудника, нужно присоединить соответствующие данные. Далее мы рассмотрим все варианты присоединений. Данные специально подобраны так, чтобы продемонстрировать отличия в результатах разных запросов.
INNER JOIN
Внутреннее присоединение. Равносильно просто JOIN или CROSS JOIN.
Такое присоединение покажет нам данные из таблиц только если условие связывания соблюдается — т.е. для сотрудника указан существующий в словаре идентификатор должности.
Если поменять порядок соединения таблиц — получим тот же результат.
Условно представим себе эти таблицы, как пересекающиеся множества, где пересечение — это наличие связи между таблицами. Получим картинку:
Далее проследим как получить разные части (подмножества) данного множества.
OUTER JOIN
Внешнее присоединение. Различают LEFT OUTER JOIN и RIGHT OUTER JOIN, и обычно опускают слово «OUTER».
Внешнее присоединение включает в себя результаты запроса INNER и добавляются «неиспользованные» строки из одной из таблиц. Какую таблицу использовать в качестве «добавки» — указывает токен LEFT или RIGHT.
Ранее мы рассмотрели применение инструкции SELECT для выборки данных из одной таблицы базы данных. Если бы возможности языка Transact-SQL ограничивались поддержкой только таких простых инструкций SELECT, то присоединение в запросе двух или больше таблиц для выборки из них данных было бы невозможно. Следственно, все данные базы данных требовалось бы хранить в одной таблице. Хотя такой подход является вполне возможным, ему присущ один значительный недостаток — хранимые таким образом данные характеризуются высокой избыточностью.
Язык Transact-SQL устраняет этот недостаток, предоставляя для этого оператор соединения JOIN, который позволяет извлекать данные более чем из одной таблицы. Этот оператор, наверное, является наиболее важным оператором для реляционных систем баз данных, поскольку благодаря ему имеется возможность распределять данные по нескольким таблицам, обеспечивая, таким образом, важное свойство систем баз данных — отсутствие избыточности данных.
Оператор UNION, который мы рассмотрели ранее, также позволяет выполнять запрос по нескольким таблицам. Но этот оператор позволяет присоединить несколько инструкций SELECT, тогда как оператор соединения JOIN соединяет несколько таблиц с использованием всего лишь одной инструкции SELECT. Кроме этого, оператор UNION объединяет строки таблиц, в то время как оператор JOIN соединяет столбцы.
Оператор соединения также можно применять с базовыми таблицами и представлениями. Оператор соединения JOIN имеет несколько разных форм. В этой статье рассматриваются следующие основные формы этого оператора:
декартово произведение или перекрестное соединение;
тета-соединение, самосоединение и полусоединение.
Прежде чем приступить к рассмотрению разных форм соединений, в этом разделе мы рассмотрим разные варианты оператора соединения JOIN.
Две синтаксические формы реализации соединений
Для соединения таблиц можно использовать две разные синтаксические формы оператора соединения:
явный синтаксис соединения (синтаксис соединения ANSI SQL:1992);
неявный синтаксис соединения (синтаксис соединения "старого стиля").
Синтаксис соединения ANSI SQL:1992 был введен стандартом SQL92 и определяет операции соединения явно, т.е. используя соответствующее имя для каждого типа операции соединения. При явном объявлении соединения используются следующие ключевые слова:
LEFT [OUTER] JOIN;
RIGHT [OUTER] JOIN;
FULL [OUTER] JOIN.
Ключевое слово CROSS JOIN определяет декартово произведение двух таблиц. Ключевое слово INNER JOIN определяет естественное соединение двух таблиц, а LEFT OUTER JOIN и RIGHT OUTER JOIN определяют одноименные операции соединения. Наконец, ключевое слово FULL OUTER JOIN определяет соединение правого и левого внешнего соединений. Все эти операции соединения рассматриваются в последующих разделах.
Неявный синтаксис оператора соединения является синтаксисом "старого стиля", где каждая операция соединения определяется неявно посредством предложения WHERE, используя так называемые столбцы соединения.
Для операций соединения рекомендуется использовать явный синтаксис, т.к. это повышает надежность запросов. По этой причине во всех примерах далее, связанных с операциями соединения, используются формы явного синтаксиса. Но в нескольких первых примерах также будет продемонстрирован и синтаксис "старого стиля".
Естественное соединение
Термины "естественное соединение" (natural join) и "соединение по эквивалентности" (equi-join) часто используют синонимично, но между ними есть небольшое различие. Операция соединения по эквивалентности всегда имеет одну или несколько пар столбцов с идентичными значениями в каждой строке. Операция, которая устраняет такие столбцы из результатов операции соединения по эквивалентности, называется естественным соединением. Наилучшим способом объяснить естественное соединение можно посредством примера:
Запрос возвращает всю информацию обо всех сотрудниках: имя и фамилию, табельный номер, а также имя, номер и местонахождение отдела, при этом для номера отдела отображаются дубликаты столбцов из разных таблиц.
В этом примере в инструкции SELECT для выборки указаны все столбцы таблиц для сотрудника Employee и отдела Department. Предложение FROM инструкции SELECT определяет соединяемые таблицы, а также явно указывает тип операции соединения — INNER JOIN. Предложение ON является частью предложения FROM и указывает соединяемые столбцы в обеих таблицах. Выражение "Employee.DepartamentNumber = Department.Number" определяет условие соединения, а оба столбца условия называются столбцами соединения.
Результат выполнения этого запроса:
Эквивалентный запрос с применением неявного синтаксиса ("старого стиля") будет выглядеть следующим образом:
Эта форма синтаксиса имеет два значительных различия с явной формой: список соединяемых таблиц указывается в предложении FROM, а соответствующее условие соединения указывается в предложении WHERE посредством соединяемых столбцов.
Настоятельно рекомендуется использовать подстановочный знак * в списке выбора SELECT только при работе с SQL в интерактивном режиме, и избегать его применения в прикладных программах.
На предыдущих примерах можно проиллюстрировать принцип работы операции соединения. Но при этом следует иметь в виду, что это всего лишь представление о процессе соединения, т.к. в действительности компонент Database Engine выбирает реализацию операции соединения из нескольких возможных стратегий. Представьте себе, что каждая строка таблицы Employee соединена с каждой строкой таблицы Department. В результате получится таблица с семью столбцами (4 столбца из таблицы Employee и 3 из таблицы Department) и 21 строкой.
Далее, из этой таблицы удаляются все строки, которые не удовлетворяют условию соединения "Employee.Number = Department.Number". Оставшиеся строки представляют результат первого примера выше. Соединяемые столбцы должны иметь идентичную семантику, т.е. оба столбца должны иметь одинаковое логическое значение. Соединяемые столбцы не обязательно должны иметь одинаковое имя (или даже одинаковый тип данных), хотя часто так и бывает.
Система базы данных не может определить логическое значение столбца. Например, она не может определить, что между столбцами номера проекта и табельного номера сотрудника нет ничего общего, хотя оба они имеют целочисленный тип данных. Поэтому система базы данных может только проверить тип данных и длину строк. Компонент Database Engine требует, что соединяемые столбцы имели совместимые типы данных, например INT и SMALLINT.
База данных SampleDb содержит три пары столбцов, где каждый столбец в паре имеет одинаковое логическое значение (а также одинаковые имена). Таблицы Employee и Department можно соединить по столбцам Employee.DepartmentNumber и Department.Number. Столбцами соединения таблиц Employee и Works_on являются столбцы Employee.Id и Works_on.EmpId. Наконец, таблицы Project и Works_on можно соединить по столбцам Project.Number и Works_on.ProjectNumber.
Имена столбцов в инструкции SELECT можно уточнить. В данном контексте под уточнением имеется в виду, что во избежание неопределенности относительно того, какой таблице принадлежит столбец, в имя столбца включается имя его таблицы (или псевдоним таблицы), отделенное точкой:
В большинстве инструкций SELECT столбцы не требуют уточнения, хотя обычно рекомендуется применять уточнение столбцов с целью улучшения понимания кода. Если же имена столбцов в инструкции SELECT неоднозначны (как, например, столбцы Number в таблицах Project и Department) использование уточненных имен столбцов является обязательным.
В инструкции SELECT с операцией соединения, кроме условия соединения предложение WHERE может содержать и другие условия, как это показано в примере ниже:
Использование уточненного имени столбца Project.Number в примере выше не является обязательным, поскольку в данном случае нет никакой двусмысленности в отношении их имен. В дальнейшем во всех примерах будет использоваться только явный синтаксис соединения.
В примере ниже показано еще одно применение внутреннего соединения:
Соединение более чем двух таблиц
Теоретически количество таблиц, которые можно соединить в инструкции SELECT, неограниченно. (Но одно условие соединения совмещает только две таблицы!) Однако для компонента Database Engine количество соединяемых таблиц в инструкции SELECT ограничено 64 таблицами.
В примере ниже показано соединение трех таблиц базы данных SampleDb:
В этом примере происходит выборка имен и фамилий всех аналитиков (Job = ‘Аналитик’), чей отдел находится в Санкт-Петербурге (Location = ‘Санкт-Петербург’). Результат запроса, приведенного в примере выше, можно получить только в том случае, если соединить, по крайней мере, три таблицы: Works_on, Employee и Department. Эти таблицы можно соединить, используя две пары столбцов соединения:
Обратите внимание, что для осуществления естественного соединения трех таблиц используется два условия соединения, каждое из которых соединяет по две таблицы. А при соединении четырех таблиц таких условий соединения требуется три. В общем, чтобы избежать получения декартового продукта при соединении n таблиц, требуется применять n — 1 условий соединения. Конечно же, допустимо использование более чем n — 1 условий соединения, а также других условий, для того чтобы еще больше уменьшить количество элементов в результирующем наборе данных.
Декартово произведение
В предшествующем разделе мы рассмотрели возможный способ создания естественного соединения. На первом шаге этого процесса каждая строка таблицы Employee соединяется с каждой строкой таблицы Department. Эта операция называется декартовым произведением (cartesian product). Запрос для создания соединения таблиц Employee и Department, используя декартово произведение, показан в примере ниже:
Декартово произведение соединяет каждую строку первой таблицы с каждой строкой второй. В общем, результатом декартового произведения первой таблицы с n строками и второй таблицы с m строками будет таблица с n*m строками. Таким образом, результирующий набор запроса в примере выше имеет 7 х 3 = 21 строку (эти строки содержат дублированные значения).
На практике декартово произведение применяется крайне редко. Иногда пользователи получают декартово произведение двух таблиц, когда они забывают включить условие соединения в предложении WHERE при использовании неявного синтаксиса соединения "старого стиля". В таком случае полученный результат не соответствует ожидаемому, т.к. содержит лишние строки. Наличие неожидаемо большого количества строк в результате служит признаком того, что вместо требуемого естественного соединения двух таблиц было получено декартово произведение.
Внешнее соединение
В предшествующих примерах естественного соединения, результирующий набор содержал только те строки с одной таблицы, для которых имелись соответствующие строки в другой таблице. Но иногда кроме совпадающих строк бывает необходимым извлечь из одной или обеих таблиц строки без совпадений. Такая операция называется внешним соединением (outer join).
В примере ниже показана выборка всей информации для сотрудников, которые проживают и работают в одном и том же городе. Здесь используется таблица EmployeeEnh, которую мы создали в статье "Инструкция SELECT: расширенные возможности" при обсуждении оператора UNION.
Результат выполнения этого запроса:
В этом примере получение требуемых строк осуществляется посредством естественного соединения. Если бы в этот результат потребовалось включить сотрудников, проживающих в других местах, то нужно было применить левое внешнее соединение. Данное внешнее соединение называется левым потому, что оно возвращает все строки из таблицы с левой стороны оператора сравнения, независимо от того, имеются ли совпадающие строки в таблице с правой стороны. Иными словами, данное внешнее соединение возвратит строку с левой таблицы, даже если для нее нет совпадения в правой таблице, со значением NULL соответствующего столбца для всех строк с несовпадающим значением столбца другой, правой, таблицы. Для выполнения операции левого внешнего соединения компонент Database Engine использует оператор LEFT OUTER JOIN.
Операция правого внешнего соединения аналогична левому, но возвращаются все строки таблицы с правой части выражения. Для выполнения операции правого внешнего соединения компонент Database Engine использует оператор RIGHT OUTER JOIN.
В этом примере происходит выборка сотрудников (с включением полной информации) для таких городов, в которых сотрудники или только проживают (столбец City в таблице EmployeeEnh), или проживают и работают. Результат выполнения этого запроса:
Как можно видеть в результате выполнения запроса, когда для строки из левой таблицы (в данном случае EmployeeEnh) нет совпадающей строки в правой таблице (в данном случае Department), операция левого внешнего соединения все равно возвращает эту строку, заполняя значением NULL все ячейки соответствующего столбца для несовпадающего значения столбца правой таблицы. Применение правого внешнего соединения показано в примере ниже:
В этом примере происходит выборка отделов (с включением полной информации о них) для таких городов, в которых сотрудники или только работают, или проживают и работают. Результат выполнения этого запроса:
Кроме левого и правого внешнего соединения, также существует полное внешнее соединение, которое является объединением левого и правого внешних соединений. Иными словами, результирующий набор такого соединения состоит из всех строк обеих таблиц. Если для строки одной из таблиц нет соответствующей строки в другой таблице, всем ячейкам строки второй таблицы присваивается значение NULL. Для выполнения операции полного внешнего соединения используется оператор FULL OUTER JOIN.
Любую операцию внешнего соединения можно эмулировать, используя оператор UNION совместно с функцией NOT EXISTS. Таким образом, запрос, показанный в примере ниже, эквивалентен запросу левого внешнего соединения, показанному ранее. В данном запросе осуществляется выборка сотрудников (с включением полной информации) для таких городов, в которых сотрудники или только проживают или проживают и работают:
Первая инструкция SELECT объединения определяет естественное соединение таблиц EmployeeEnh и Department по столбцам соединения City и Location. Эта инструкция возвращает все города для всех сотрудников, в которых сотрудники и проживают и работают. Дополнительно, вторая инструкция SELECT объединения возвращает все строки таблицы EmployeeEnh, которые не отвечают условию в естественном соединении.
Другие формы операций соединения
В предшествующих разделах мы рассмотрели наиболее важные формы соединения. Но существуют и другие формы этой операции, которые мы рассмотрим в следующих подразделах.
Тета-соединение
Условие сравнения столбцов соединения не обязательно должно быть равенством, но может быть любым другим сравнением. Соединение, в котором используется общее условие сравнения столбцов соединения, называется тета-соединением. В примере ниже показана операция тета-соединения, в которой используется условие "меньше чем". Данный запрос возвращает все комбинации информации о сотрудниках и отделах для тех случаев, когда место проживания сотрудника по алфавиту идет перед месторасположением любого отдела, в котором работает этот служащий:
Результат выполнения этого запроса:
В этом примере сравниваются соответствующие значения столбцов City и Location. В каждой строке результата значение столбца City сравнивается в алфавитном порядке с соответствующим значением столбца Location.
Самосоединение, или соединение таблицы самой с собой
Кроме соединения двух или больше разных таблиц, операцию естественного соединения можно применить к одной таблице. В данной операции таблица соединяется сама с собой, при этом один столбец таблицы сравнивается сам с собой. Сравнивание столбца с самим собой означает, что в предложении FROM инструкции SELECT имя таблицы употребляется дважды. Поэтому необходимо иметь возможность ссылаться на имя одной и той же таблицы дважды. Это можно осуществить, используя, по крайней мере, один псевдоним. То же самое относится и к именам столбцов в условии соединения в инструкции SELECT. Для того чтобы различить столбцы с одинаковыми именами, необходимо использовать уточненные имена.
Соединение таблицы с самой собой демонстрируется в примере ниже:
В этом примере происходит выборка всех отделов (с полной информацией), расположенных в том же самом месте, как и, по крайней мере, один другой отдел. Результат выполнения этого запроса:
Здесь предложение FROM содержит два псевдонима для таблицы Department: t1 и t2. Первое условие в предложении WHERE определят столбцы соединения, а второе — удаляет ненужные дубликаты, обеспечивая сравнение каждого отдела с другими отделами.
Полусоединение
Полусоединение похоже на естественное соединение, но возвращает только набор всех строк из одной таблицы, для которой в другой таблице есть одно или несколько совпадений. Использование полусоединения показано в примере ниже:
Результат выполнения запроса:
Как можно видеть, список выбора SELECT в полусоединении содержит только столбцы из таблицы Employee. Это и есть характерной особенностью операции полусоединения. Эта операция обычно применяется в распределенной обработке запросов, чтобы свести к минимуму объем передаваемых данных. Компонент Database Engine использует операцию полусоединения для реализации функциональности, называющейся соединением типа "звезда".
Автор: Wagner Crivelini
Опубликовано: 09.07.2010
Версия текста: 1.1
Первое, что мы узнаем об SQL – это как писать выражения SELECT для выборки данных из таблицы. Такие выражения выглядят просто и очень похоже на обычный разговорный язык.
Но настоящие запросы зачастую гораздо сложнее, чем простые выражения SELECT.
Во-первых, нужные данные обычно разбиты на несколько разных таблиц. Это естественное следствие нормализации данных, которая является характерным свойством любой хорошо спланированной модели БД. SQL позволяет объединить эти данные.
В прошлом администраторы БД и разработчики помещали все нужные таблицы и/или представления в оператор FROM, а затем использовали оператор WHERE, чтобы определить, как должны комбинироваться записи из одной таблицы с записями из другой (чтобы сделать этот текст чуть-чуть более читаемым, я в дальнейшем буду писать просто «таблица», а не «таблица и/или представление»).
Однако, чтобы стандартизовать объединение данных, понадобилось довольно много времени. Это было сделано с помощью оператора JOIN (ANSI-SQL 92). К сожалению, некоторые детали использования оператора JOIN так и остаются неизвестными очень многим.
Прежде чем показать различный синтаксис JOIN, поддерживаемый T-SQL (в SQL Server 2008), я опишу несколько концепций, которые не следует забывать при любом соединении данных из двух или нескольких таблиц.
Начало: одна таблица, никакого JOIN
Если запрос обращается только к одному объекту, синтаксис будет очень простым, и никакое соединение не потребуется. Выражение будет старым добрым " SELECT fields FROM object " с другими необязательными операторами (то есть WHERE, GROUP BY, HAVING или ORDER BY).
Однако конечные пользователи не знают, что администраторы БД обычно прячут множество сложных соединений за одним красивым и простым в использовании представлением. Это делается по разным причинам, от безопасности данных до производительности БД. Например, администратор может дать конечному пользователю разрешение на доступ к одному представлению вместо нескольких рабочих таблиц, что, очевидно, повышает сохранность данных. А если говорить о производительности, можно создать представление, используя правильные параметры для соединения записей из разных таблиц, правильно использовать индексы и тем самым повысит производительность запроса.
Как бы то ни было, соединения в БД всегда есть, даже если конечный пользователь их и не видит.
Логика, стоящая за соединением таблиц
Много лет назад, когда я начинал работать с SQL, я узнал, что есть несколько типов соединения данных. Но мне потребовалось некоторое время, чтобы точно понять, что я делаю, соединяя таблицы. Возможно из-за того, что люди боятся математики, не часто можно услышать, что вся идея соединений таблиц – это теория множеств. Несмотря на заковыристое название, концепция так проста, что изучается в начальной школе.
Рисунок 1 очень похож на картинки из учебника для первого класса. Идея в том, чтобы найти в разных множествах соответствующие объекты. Это как раз то, чем занимается JOIN в SQL!
Рисунок 1. Комбинируем объекты из разных множеств.
Если вы поняли эту аналогию, все становится более осмысленным.
Представьте, что 2 множества на рисунке 1 – это таблицы, а цифры – это ключи, используемые для соединения таблиц. Таким образом, в каждом из множеств вместо целой записи мы видим только ключевые поля каждой таблицы. Результирующий набор комбинаций будет определяться типом используемого соединения, и это я как раз и собираюсь показать. Чтобы проиллюстрировать примеры, возьмем 2 таблицы, показанные ниже:
Таблица Table1
Таблица Table2
