При включении нескольких источников данных в запрос Access с помощью соединений можно ограничить количество записей, которые необходимо просмотреть, в зависимости от того, как источники данных связаны между собой. Соединения также используются для объединения записей из обоих источников данных, чтобы каждая пара записей из источников стала одной записью в результатах запроса.
В этой статье рассматриваются различные типы соединений и показано, как их использовать в запросе. По умолчанию соединение создается автоматически, если связь между двумя источниками данных, используемыми в запросе, уже существует. Объединение также создается при наличии четко соответствующих друг другу полей. Вы можете удалить автоматически созданное соединение. В этой статье приводятся основные сведения о связях между таблицами, в том числе о способах их создания.
Примечание
Запросы можно соединять так же, как таблицы, а также соединять их.
В этом разделе...
- Обзор
- Типы соединений
- Отображение строк, в которых в обеих объединенных таблицах есть одно и то же значение
- Отображение всех строк из одной таблицы и соответствующих строк из другой таблицы
- Отображение всех строк обеих таблиц и объединение их в случае наличия общего значения
- Перекрестные соединения
- Объединение таблиц на основе неравенства значений полей
- Удаление присоединения
Обзор
База данных — это набор таблиц данных, которые логически связаны друг с другом. Связи используются для соединения таблиц по общим полям. Одна из таблиц может содержать любое количество связей, но каждое отношение всегда состоит только из двух таблиц. В запросе связь представлена соединением.
При добавлении таблиц в запрос Access создаются соединения на основе связей, определенных между таблицами. Соединения в запросах можно создавать вручную, даже если они не представляют связи, которые уже определены. Если в качестве источников данных для запроса используются другие запросы (вместо таблиц или в дополнение к ним), можно создать связи между исходными запросами, а также между этими запросами и любыми таблицами, используемыми в качестве источников данных.
Соединения ведут себя аналогично условиям запроса, поскольку они устанавливают правила, которым должны соответствовать данные для включения в операции запроса. В отличие от условий, соединения также указывают, что каждая пара строк, удовлетворяющих условиям соединения, будет объединена в наборе записей в одну строку.
Существует четыре основных типа соединений: внутренние соединения, внешние соединения, поперечные соединения и неравные соединения. В этой статье рассматриваются все доступные типы соединений, смысл использования каждого из них и способы их создания.
Соединения относятся к запросам то же, что отношения к таблицам: это способ объединения данных в двух источниках на основе значений данных, которые у них общие. Ниже показано соединение в конструкторе запросов со свойствами соединения, открытыми в диалоговом окне.
Эта линия между таблицами представляет соединение. Дважды щелкните соединение, чтобы открыть диалоговое окно "Свойства соединения " (изображено) и просмотреть или изменить соединение.
Соединения иногда бывают направленными. В этой области диалогового окна показано, какая таблица будет использована для соединения и какие поля используются для соединения таблиц.
Эта область определяет тип соединения: вариант 1 — внутреннее соединение, 2 — левое внешнее соединение, 3 — правое внешнее соединение.
Можно использовать поля из обеих таблиц, и из каждой из них отображаются данные, относящиеся к конкретной задаче. При внутреннем соединении никакие другие данные не включаются. При внешнем соединении несвязанные записи из одной таблицы также включаются в результаты запроса.
Типы соединений
Существует четыре основных типа соединений: внутренние соединения, внешние соединения, поперечные соединения и неравные соединения. Перекрестные и неравные соединения — это сложные типы соединений, которые редко используются, но их нужно знать, чтобы иметь полное представление о том, как они работают.
Внутренние связи: объединены только связанные данные из обеих таблиц
Внутреннее соединение включает данные из таблицы только в том случае, если в связанной таблице есть соответствующие данные, и наоборот. Чаще всего используются внутренние соединения. Если вы создаете соединение и не указываете тип соединения, Access предполагает, что вам требуется внутреннее соединение. Внутренние соединения полезны, так как они позволяют объединять данные из двух источников на основе общих значений, поэтому вы видите данные только тогда, когда есть полная картина.
Внешние соединения: все связанные данные объединены правильно, а также все оставшиеся записи из одной таблицы
Внешнее соединение похоже на внутреннее, но при этом добавляются оставшиеся строки из одной из таблиц. Внешние соединения являются направленными: левое внешнее соединение включает все записи из левой таблицы (первой таблицы в объединении), а правое внешнее соединение включает все записи из правой таблицы (второй таблицы в объединении).
Полные внешние соединения: все данные, объединенные там, где это возможно
В некоторых системах внешнее соединение может включать все строки обеих таблиц с объединением строк, если они совпадают. Это называется полным внешним соединением, и Access не поддерживает их явно. Однако для достижения того же результата можно использовать перекрестное соединение и условия.
Перекрестные соединения: все данные, объединенные всеми возможными способами
В большинстве случаев перекрестное объединение является побочным эффектом, когда вы добавляете в запрос две таблицы и забываете их соединить. Это означает, что необходимо видеть все записи из одной таблицы в сочетании с записями в другой таблице, т. е. все возможные сочетания записей. Так как данные невозможно объединить, такого рода объединение редко дает полезные результаты. Но есть несколько случаев, когда перекрестное соединение — это то, что вам нужно.
Неравные соединения: аналогично обычному соединению, но с использованием другого сравнения для объединения строк
В случае неравного соединения используется оператор, отличный от знака равенства (=), для сравнения значений и определения способа их объединения. Неравные соединения явно не поддерживаются, но вы можете использовать перекрестное соединение и критерии для достижения того же эффекта.
Отображение строк, в которых в обеих объединенных таблицах есть одно и то же значение
Если нужно отобразить только те строки, которые содержат совпадающие значения в связанном поле, используйте внутреннее соединение. Access автоматически создает внутренние соединения.
Внутреннее объединение — это самый распространенный тип объединения. Они сообщают запросу, что строки из одной из связанных таблиц соответствуют строкам другой таблицы на основе данных в связанных полях. При выполнении запроса с внутренним соединением в операции запроса включаются только те строки, для которых в обеих связанных таблицах есть общее значение.
Разделы справки используются для внутреннего соединения?
В большинстве случаев для использования внутреннего соединения не нужно ничего делать. Если таблицы, добавляемые в запрос, уже имеют связи, Access автоматически создает внутреннее соединение между каждой парой связанных таблиц. Если обеспечение целостности данных включено, над линией соединения отображается значок "1", указывающий тип таблицы, находящейся на стороне "один" отношения "один-ко-многим", и символ бесконечности (∞-символ, указывающий, какая таблица находится на стороне "многие".
Даже если связи еще не созданы, Access автоматически создает внутренние соединения, если добавить в запрос две таблицы, каждая из которых содержит поле с одним и тем же или совместимым типом данных, одно из которых является первичным ключом. Символы "один" и "многие" в этом случае не отображаются, так как целостность данных не обеспечивается.
Если вы добавляете запросы в запрос и не создаете связи между ними, Access не создает автоматически внутренних связей между этими запросами или между запросами и таблицами. Как правило, вы должны создавать их самостоятельно. Внутреннее соединение создается путем перетаскивания поля из одного источника данных в поле другого источника данных. Access добавит линию между двумя полями, чтобы показать, что они соединены.
Синтаксис SQL для внутреннего соединения
Внутренние соединения задаются в SQL в предложении FROM, как показано ниже:
ИЗ ТАБЛИЦЫ1 ВНУТРЕННЕЕ СОЕДИНЕНИЕ таблицы2 НА ТАБЛИЦЕ1. поле1 Сравните таблицу2. поле2
Операция INNER JOIN состоит из следующих элементов:
| Часть | Описание |
|---|---|
| таблица1, таблица2 | Имена таблиц, содержащих объединяемые записи. |
| поле1, поле2 | Имена объединенных полей. Если они не являются числовыми, поля должны иметь один и тот же тип данных и содержать одинаковые данные, но не обязательно должны иметь одинаковые имена. |
| compare | Любой оператор реляционного сравнения: "=", "<," ">," "<=", ">=," или "<>." |
Дополнительные сведения о синтаксисе INNER JOIN см. в статье ОПЕРАЦИЯ INNER JOIN.
Отображение всех строк из одной таблицы и соответствующих строк из другой таблицы
Внешние соединения сообщают запросу, что, хотя некоторые строки с обеих сторон соединения точно совпадают, запрос должен включать все строки из одной таблицы, а также те строки из другой таблицы, которые имеют общие значения на обеих сторонах соединения.
Внешние соединения могут быть левыми или правыми. При левом внешнем соединении запрос включает все строки из первой таблицы в предложении FROM инструкции SQL и только те строки из другой таблицы, в которых поле связывания содержит значения, общие для обеих таблиц. При правом внешнем соединении запрос включает все строки из второй таблицы в предложении FROM инструкции SQL и только те строки из другой таблицы, в которых поле связывания содержит значения, общие для обеих таблиц.
Примечание
Вы можете легко определить, какая таблица является левой или правой в данном соединении, дважды щелкнув соединение и просмотрев диалоговое окно "Параметры объединения ". Вы также можете переключиться в режим SQL, а затем изучить предложение FROM.
Поскольку некоторые строки с одной стороны внешнего соединения не будут содержать соответствующих строк из другой таблицы, некоторые поля, возвращаемые в результатах запроса из другой таблицы, будут пустыми, если строки не соответствуют друг другу.
Разделы справки Использование внешнего соединения?
Внешнее соединение создается путем изменения существующего внутреннего соединения. Если внутреннего соединения нет, вы можете создать его, а затем изменить на внешнее.
Изменение внутреннего соединения на внешнее
- В конструкторе запросов дважды щелкните соединение, которое нужно изменить.
Откроется диалоговое окно Параметры соединения. - В диалоговом окне "Параметры объединения " обратите внимание на команды, перечисленные рядом с вариантами 2 и 3.
- Выберите нужный вариант и нажмите кнопку ОК.
- В Access отображается соединение со стрелкой, указывающей от источника данных, где будут включены все строки, к источнику данных, где будут включены только те строки, которые удовлетворяют условию соединения.
Неоднозначные внешние соединения
Если вы создаете запрос, содержащий LEFT JOIN и INNER JOIN, Access не сможет определить, какую операцию соединения следует выполнить первой. Так как результаты различаются в зависимости от того, какое соединение выполняется первым, как левое, так и внутреннее, в Access отображается сообщение об ошибке:
Чтобы исправить эту ошибку, необходимо изменить запрос таким образом, чтобы было понятно, какое соединение выполнить первым.
Синтаксис SQL для внешнего соединения
Внешние соединения задаются в SQL в предложении FROM, как показано ниже:
ИЗ ТАБЛИЦЫ1 [ СЛЕВА | СПРАВА ] СОЕДИНЕНИЕ ТАБЛИЦЫ2
ON таблица1.поле1 сравнить таблицу2.поле2
Операции LEFT JOIN и RIGHT JOIN состоят из следующих элементов:
| Часть | Описание |
|---|---|
| таблица1, таблица2 | Имена таблиц, содержащих объединяемые записи. |
| поле1, поле2 | Имена объединенных полей. Поля должны иметь одинаковый тип данных и содержать одинаковые данные, но не обязательно должны иметь одинаковые имена. |
| compare | Любой оператор реляционного сравнения: "=", "<," ">," "<=", ">=," или "<>." |
Дополнительные сведения о синтаксисе внешнего соединения см. в статье ОПЕРАЦИИ ЛЕВОГО СОЕДИНЕНИЯ, ПРАВОГО СОЕДИНЕНИЯ.
Отображение всех строк обеих таблиц и объединение их в случае наличия общего значения
Если требуется отобразить все строки из двух таблиц и объединить их на основе общих значений, используется полное внешнее соединение. В 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. Обратите внимание, что количество записей намного меньше.
Объединение таблиц на основе неравенства значений полей
Соединения не обязательно должны основываться на эквивалентности связанных полей. Соединение может быть основано на любом операторе сравнения, например больше (),> меньше (<), или не равно (<>). Соединения, не основанные на эквивалентности, называются неравными соединениями.
Если требуется объединить строки двух источников данных на основе неравных значений полей, используется неравное соединение. Как правило, неравные связи основаны на операторах сравнения «больше (>»), меньше (<), больше или равно (=), меньше или равно (><=). Неравные соединения, основанные на операторе does not equal (<>), могут возвращать почти столько же строк, сколько и перекрестных соединений, и результаты может быть трудно интерпретировать.
Разделы справки используют неравное соединение?
Неравные соединения не поддерживаются в Конструкторе. Если вы хотите использовать их, вы должны сделать это в представлении SQL. Однако можно создать соединение в Конструкторе, переключиться в режим SQL, найти оператор сравнения "равно" (=) и заменить его нужным оператором. После этого запрос можно будет снова открыть в Конструкторе только в том случае, если сначала измените оператор сравнения на "равно" (=) в режиме SQL.
Удаление присоединения
Если приложение Access автоматически создает ненужное соединение или если оно было создано по ошибке (например, объединение двух полей с разными типами данных), его можно удалить.
- В бланке запроса выберите соединение, которое вы хотите удалить.
- Нажмите клавишу DELETE.
ИЛИ
- На бланке запроса щелкните правой кнопкой мыши соединение, которое требуется удалить, и выберите команду Удалить.
Добавление записей в таблицу с помощью запроса на добавлениеОбъединение результатов нескольких запросов на выборку с помощью запроса на объединениеСоздание запроса на основе нескольких таблицСоздание, изменение и удаление отношения