При включении нескольких источников данных в запрос Access используются соединения для ограничения записей, которые нужно просмотреть, в зависимости от того, как источники данных связаны друг с другом. Соединения также используются для объединения записей из обоих источников данных, чтобы каждая пара записей из источников становилась одной записью в результатах запроса.
В этой статье рассматриваются различные типы соединений и показано, как использовать их в запросе. По умолчанию соединение создается автоматически, если между двумя источниками данных, используемыми в запросе, уже существует связь. Соединение также создается, если есть поля, которые четко соответствуют друг другу. Вы можете удалить автоматически созданное соединение. В этой статье содержатся основные сведения о связях таблиц, в том числе о том, как их создать.
Примечание: Запросы можно объединять так же, как и таблицы, а также объединять и то, и другое.
В этой статье
Обзор
База данных — это коллекция таблиц данных, которые имеют логические связи друг с другом. Связи используются для соединения таблиц по общим полям. Таблица может быть частью любого количества связей, но каждая связь всегда имеет ровно две таблицы. В запросе связь представлена соединением.
При добавлении таблиц в запрос Access создает соединения, основанные на связях, определенных между таблицами. Вы можете вручную создавать соединения в запросах, даже если они не представляют связи, которые уже определены. Если в качестве источников данных для запроса используются другие запросы (вместо таблиц или в дополнение к ним), можно создать соединения между исходными запросами, а также между этими запросами и любыми таблицами, которые используются в качестве источников данных.
Соединения ведут себя аналогично критериям запроса, так как они устанавливают правила, согласно которым данные должны соответствовать для включения в операции запроса. В отличие от условий, соединения также указывают, что каждая пара строк, удовлетворяющая условиям соединения, будет объединена в наборе записей для формирования одной строки.
Существует четыре основных типа соединений: внутренние соединения, внешние соединения, перекрестные соединения и неравные соединения. В этой статье рассматриваются все типы соединения, которые можно использовать, причины использования каждого типа и способы создания соединений.
Соединения — это запросы связей с таблицами. Это указывает на то, как данные в двух источниках можно комбинировать на основе общих значений данных. Ниже показана иллюстрация соединения в конструкторе запроса со свойствами соединения, открытыми в диалоговом окне:
Эта линия между таблицами представляет соединение. Дважды щелкните соединение, чтобы открыть диалоговое окно Свойства соединения (на рисунке) и просмотреть или изменить соединение.
Соединения иногда являются направленными. В этой области диалогового окна показано, какая таблица находится в соединении и какие поля используются для соединения таблиц.
Эта область определяет тип соединения: вариант 1 — внутреннее соединение, 2 — левое внешнее соединение, а 3 — правое внешнее соединение.
Можно использовать поля из обеих таблиц, а данные, относящиеся к определенной задаче, отображаются из каждой из них. Во внутреннем соединении другие данные не включаются. Во внешнем соединении несвязанные записи из одной таблицы также включаются в результаты запроса.
Типы соединений
Существует четыре основных типа соединений: внутренние соединения, внешние соединения, перекрестные соединения и неравные соединения. Перекрестные соединения и неравные соединения являются расширенными типами соединений и редко используются, но вы должны знать о них, чтобы иметь полное представление о работе соединений.
Внутренние соединения: только связанные данные из обеих таблиц
Внутреннее соединение — это соединение, в котором Access включает данные из таблицы только при наличии соответствующих данных в связанной таблице, и наоборот. Большую часть времени вы будете использовать внутренние соединения. Если вы создаете соединение и не указываете его тип, Access предполагает, что требуется внутреннее соединение. Внутренние соединения полезны, так как они позволяют объединять данные из двух источников на основе общих значений, поэтому данные отображаются только при наличии полной картины.
Внешние соединения: все связанные данные объединены правильно, а также все остальные записи из одной таблицы
Внешнее соединение похоже на внутреннее соединение, но добавляет оставшиеся строки из одной из таблиц. Внешние соединения являются направленными: левое внешнее соединение включает все записи из левой таблицы (первой таблицы в соединении), а правое внешнее соединение включает все записи из правой таблицы — второй таблицы в соединении.
Полные внешние соединения: все данные, объединенные, где это возможно
В некоторых системах внешнее соединение может включать все строки из обеих таблиц, а строки объединяются, если они соответствуют. Это называется полным внешним соединением, и Access не поддерживает их явным образом. Однако для достижения того же эффекта можно использовать перекрестное соединение и критерии.
Перекрестные соединения: все данные, объединенные всеми возможными способами
В большинстве случаев перекрестное соединение является побочным эффектом добавления двух таблиц в запрос, а затем забвения их соединения. Access интерпретирует это как означает, что вы хотите видеть каждую запись из одной таблицы в сочетании с каждой записью из другой таблицы — каждой возможной комбинацией записей. Так как данные не могут быть объединены, такое соединение редко дает полезные результаты. Но есть несколько случаев, когда перекрестное соединение — это именно то, что вам нужно.
Неравные соединения: как обычное соединение, но для объединения строк используется другое сравнение.
Неравные соединения используют оператор, отличный от знака равенства (=), чтобы сравнить значения и определить, следует ли объединять данные и как. Неравные соединения явно не поддерживаются, но для достижения того же эффекта можно использовать перекрестное соединение и критерии.
Отображение строк, в которых существует общее значение в обеих объединенных таблицах
Если вы хотите отобразить только те строки, которые имеют совпадающие значения в объединяемом поле, используйте внутреннее соединение. Access автоматически создает внутренние соединения.
Внутреннее объединение — это самый распространенный тип объединения. Они сообщают запросу, что строки из одной из объединенных таблиц соответствуют строкам в другой таблице на основе данных в объединенных полях. При выполнении запроса с внутренним соединением в операции запроса будут включены только те строки, в которых существует общее значение в обеих объединенных таблицах.
Разделы справки использовать внутреннее соединение?
В большинстве случаев вам не нужно ничего делать, чтобы использовать внутреннее соединение. Если таблицы, добавляемые в запрос, уже имеют связи, Access автоматически создает внутреннее соединение между каждой парой связанных таблиц при добавлении таблиц. Если применяется целостность данных, Access также отображает "1" над строкой соединения, чтобы показать, какая таблица находится на одной стороне отношение "один-ко-многим" и символ бесконечности (∞), чтобы показать, какая таблица находится на стороне "многие".
Даже если вы еще не создали связи, Access автоматически создает внутренние соединения, если вы добавляете две таблицы в запрос и каждая из этих таблиц имеет поле с тем же или совместимым типом данных, а одно из полей соединения является первичный ключ. Символы "один" и "многие" в этом случае не отображаются, так как целостность ссылок не применяется.
Если вы добавляете запросы в запрос и не создали связи между этими запросами, Access не будет автоматически создавать внутренние соединения между этими запросами или между запросами и таблицами. Как правило, их следует создавать самостоятельно. Внутреннее соединение создается путем перетаскивания поля из одного источника данных в поле в другом источнике данных. Access добавит линию между двумя полями, чтобы показать, что они соединены.
Синтаксис SQL для внутреннего соединения
Внутренние соединения указываются в SQL в предложении FROM, как показано ниже:
FROM таблица1 INNER JOIN таблица2 ON таблица1.field1 compare table2.поле2
Операция INNER JOIN состоит из следующих элементов:
|
Часть |
Описание |
|
таблица1, таблица2 |
Имена таблиц, содержащих объединяемые записи. |
|
поле1, поле2 |
Имена объединенных полей. Если они не являются числовыми, поля должны иметь один и тот же тип данных и содержать данные одного типа, но они не должны иметь одно и то же имя. |
|
compare |
Любой оператор сравнения: (=, <, >, <=, >= или <>) |
Дополнительные сведения о синтаксисе внутреннего соединения см. в разделе ОПЕРАЦИЯ INNER JOIN.
Отображение всех строк из одной таблицы и соответствующих строк из другой таблицы
Внешние соединения сообщают запросу, что, хотя некоторые строки с обеих сторон соединения точно соответствуют, запрос должен включать все строки из одной таблицы, а также строки из другой таблицы, которые имеют общее значение с обеих сторон соединения.
Внешние соединения могут быть левыми внешними соединениями или правыми внешними соединениями. В левом внешнем соединении запрос включает все строки из первой таблицы в предложении ИНСТРУКЦИи SQL FROM и только те строки из другой таблицы, где поле соединения содержит значения, общие для обеих таблиц. В правом внешнем соединении запрос включает все строки из второй таблицы в предложении SQL STATEMENT FROM и только те строки из другой таблицы, где поле соединения содержит значения, общие для обеих таблиц.
Примечание: Вы можете легко определить, какая таблица является левой или правой таблицей в данном соединении, дважды щелкнув соединение, а затем найдите в диалоговом окне Свойства соединения . Вы также можете переключиться в представление SQL, а затем изучить предложение FROM.
Так как некоторые строки на одной стороне внешнего соединения не будут иметь соответствующих строк из другой таблицы, некоторые поля, возвращенные в результатах запроса из этой другой таблицы, будут пустыми, если строки не соответствуют.
Разделы справки использовать внешнее соединение?
Внешнее соединение создается путем изменения существующего внутреннего соединения. Если внутреннее соединение не существует, создайте его, а затем измените его на внешнее.
Изменение внутреннего соединения на внешнее соединение
-
В представлении конструктора запроса дважды щелкните соединение, которое нужно изменить.
Откроется диалоговое окно Параметры соединения.
-
В диалоговом окне Свойства соединения обратите внимание на варианты, перечисленные рядом с вариантами 2 и 3.
-
Выберите нужный вариант и нажмите кнопку ОК.
-
Access отображает соединение и стрелку, указывающую из источника данных, где все строки будут включены в источник данных, где будут включены только те строки, которые удовлетворяют условию соединения.
Неоднозначные внешние соединения
При создании запроса, содержащего LEFT JOIN и INNER JOIN, Access может не определить, какую операцию соединения выполнить первым. Так как результаты отличаются в зависимости от того, выполняется ли левое или внутреннее соединение первым, Access отображает сообщение об ошибке:
Чтобы исправить эту ошибку, необходимо изменить запрос, чтобы было ясно, какое соединение следует выполнить первым.
Синтаксис SQL для внешнего соединения
Внешние соединения указываются в SQL в предложении FROM, как показано ниже:
FROM table1 [ LEFT | RIGHT ] JOIN table2ON table1.field1 compare table2.field2
Операции LEFT JOIN и RIGHT JOIN состоят из следующих элементов:
|
Часть |
Описание |
|
таблица1, таблица2 |
Имена таблиц, содержащих объединяемые записи. |
|
поле1, поле2 |
Имена объединенных полей. Поля должны быть одинаковыми тип данных и содержать одни и те же данные, но они не должны иметь одно и то же имя. |
|
compare |
Любой оператор сравнения: (=, <, >, <=, >= или <>) |
Дополнительные сведения о синтаксисе внешнего соединения см. в разделе LEFT JOIN, RIGHT JOIN Operations.
Отображение всех строк из обеих таблиц и их объединение там, где существует общее значение
Если вы хотите отобразить все строки из двух таблиц и объединить их на основе общих значений, используйте полное внешнее соединение. Access явно не поддерживает полные внешние соединения, но вы можете добиться того же эффекта с помощью запроса на объединение. В следующей процедуре объясняется, как это сделать, но если вам нужны дополнительные сведения о запросах на объединение, см. раздел См. также .
Чтобы использовать запрос объединения для выполнения полного внешнего соединения, выполните следующие действия:
-
Создайте запрос с левым внешним соединением в поле, которое необходимо использовать для полного внешнего соединения.
-
На вкладке Главная в группе Режимы выберите команду Режим, а затем — Режим SQL.
-
Нажмите клавиши CTRL+C, чтобы скопировать код SQL.
-
Удалите точку с запятой в конце предложения FROM и нажмите клавишу ВВОД.
-
Введите слово UNION и нажмите клавишу ВВОД.
Примечание: Не используйте ключевое слово ALL при использовании запроса объединения для выполнения полного внешнего соединения.
-
Нажмите клавиши CTRL+V, чтобы вставить код SQL, скопированный на шаге 3.
-
В вставленном коде измените значение LEFT JOIN на RIGHT JOIN.
-
Удалите точку с запятой в конце второго предложения FROM и нажмите клавишу ВВОД.
-
Добавьте предложение WHERE, указывающее, что значение поля соединения равно NULL в первой таблице, указанной в предложении FROM (левая таблица).
Например, если предложение FROM имеет следующий тип:
FROM Products RIGHT JOIN [Order Details] ON Products.ID = [Order Details].[Product ID]
Добавьте следующее предложение WHERE:
WHERE Products.ID IS NULL
-
Введите точку с запятой (;) в конце предложения WHERE, чтобы указать конец запроса на объединение.
-
На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить.
Перекрестные соединения
Перекрестные соединения отличаются от внутренних и внешних соединений тем, что они явно не представлены в Access. В перекрестном соединении каждая строка из одной таблицы объединяется с каждой строкой из другой таблицы, что приводит к так называемому перекрестному продукту или декартовому продукту. Каждый раз, когда вы выполняете запрос с таблицами, которые не соединены явным образом, результатом является перекрестный продукт. Перекрестные соединения обычно непреднамеренные, но бывают случаи, когда они могут быть полезны.
Зачем использовать перекрестное соединение?
Если вы хотите изучить все возможные сочетания строк между двумя таблицами или запросами, используйте перекрестное соединение. Например, предположим, что у вашей компании был впечатляющий год, и вы рассматриваете возможность предоставления скидки своим клиентам. Вы можете создать запрос, который суммирует покупки каждого клиента, создать небольшую таблицу с несколькими возможными процентами скидки и объединить их в другой запрос, выполняющий перекрестное соединение. В конечном итоге вы дается запрос, который отображает набор гипотетических скидок для каждого клиента.
Разделы справки использовать перекрестное соединение?
Перекрестное соединение создается при каждом включении таблиц или запросов в запрос и не создает хотя бы одно явное соединение для каждой таблицы или запроса. Access объединяет каждую строку из каждой таблицы или запроса, которые не соединены явным образом с любой другой таблицей или запросом с каждой другой строкой результатов. Рассмотрим сценарий скидок из предыдущего абзаца. Предположим, что у вас есть 91 клиент и вы хотите рассмотреть пять возможных процентных скидок. При перекрестной соединении создается 455 строк (произведение 91 и 5).
Как вы могли себе представить, непреднамеренные перекрестные соединения могут создавать огромное количество строк в результатах запроса. Более того, эти результаты, как правило, бессмысленны, так как если вы фактически не планируете объединять каждую строку с каждой другой строкой, большинство объединенных строк, которые отображаются в результатах, не имеют смысла. Наконец, выполнение запросов, использующих непреднамеренные перекрестные соединения, может занять очень много времени.
1. Обведенные поля должны быть соединены друг с другом.
1. Обратите внимание на очень большое количество записей.
1. Обратите внимание, что количество записей значительно меньше.
Объединение таблиц на основе неравенства значений полей
Соединения не должны основываться на эквивалентности объединенных полей. Соединение может быть основано на любом операторе сравнения, например больше (>), меньше (<), или не равно (<>). Соединения, не основанные на эквивалентности, называются неравными соединениями.
Если вы хотите объединить строки двух источников данных на основе значений полей, которые не равны, используйте неравное соединение. Как правило, неравные соединения основаны на операторах сравнения больше (>), меньше (<), больше или равно (>=), или меньше или равно (<=). Неравные соединения, основанные на операторе не равно (<>), могут возвращать почти столько строк, сколько перекрестных соединений, и результаты могут быть трудно интерпретировать.
Разделы справки использовать неравное соединение?
Неравные соединения не поддерживаются в режиме конструктора. Если вы хотите использовать их, это необходимо сделать с помощью представления SQL. Однако вы можете создать соединение в режиме конструктора, переключиться в представление SQL, найти оператор сравнения equals (=) и изменить его на нужный оператор. После этого вы сможете снова открыть запрос в режиме конструктора, только если сначала измените оператор сравнения обратно на равно (=) в представлении SQL.
Удаление соединения
Если Access автоматически создает нежелательное соединение или соединение по ошибке (например, соединение между двумя полями с разнородными типами данных), его можно удалить.
-
В сетке конструктора запросов щелкните соединение, которое нужно удалить.
-
Нажмите клавишу DELETE.
ИЛИ
-
В сетке конструктора запроса щелкните правой кнопкой мыши соединение, которое нужно удалить, и выберите команду Удалить.
