Выполнение соединений с помощью Access SQL

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

INNER JOIN

Inner JOIN, также известный как равное соединение, является наиболее часто используемым типом соединения. Это соединение используется для получения строк из двух или более таблиц путем сопоставления значения поля, которое является общим для таблиц. Поля, к которых вы присоединяете, должны иметь аналогичные типы данных, и вы не можете присоединиться к типам данных MEMO или OLEOBJECT.

Чтобы создать инструкцию INNER JOIN , используйте ключевые слова INNER JOIN в предложении FROM инструкции SELECT .

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

SELECT [Last Name], InvoiceDate, Amount 
   FROM tblCustomers INNER JOIN tblInvoices 
   ON tblCustomers.CustomerID=tblInvoices.CustomerID 
   ORDER BY InvoiceDate 

Имейте в виду, что имена таблиц делятся по ключевым словам INNER JOIN и что реляционное сравнение выполняется после ключевое слово ON. Для реляционных сравнений можно также использовать <операторы , >=<, >=или<>, а также ключевое слово BETWEEN. Кроме того, обратите внимание, что поля идентификаторов из обеих таблиц используются только при реляционном сравнении; они не входят в окончательный результирующий набор.

Чтобы дополнительно определить оператор SELECT , можно использовать предложение WHERE после сравнения соединения в предложении ON .

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

SELECT [Last Name], InvoiceDate, Amount 
   FROM tblCustomers INNER JOIN tblInvoices 
   ON tblCustomers.CustomerID=tblInvoices.CustomerID 
   WHERE tblInvoices.InvoiceDate > #01/01/1998# 
   ORDER BY InvoiceDate 

Если необходимо объединить несколько таблиц, можно вложить предложения INNER JOIN . В следующем примере используется предыдущая инструкция SELECT для создания результирующих наборов, но также включается город и штат каждого клиента путем добавления INNER JOIN для таблицы tblShipping.

SELECT [Last Name], InvoiceDate, Amount, City, State 
   FROM (tblCustomers INNER JOIN tblInvoices 
   ON tblCustomers.CustomerID=tblInvoices.CustomerID) 
      INNER JOIN tblShipping 
      ON tblCustomers.CustomerID=tblShipping.CustomerID 
   ORDER BY InvoiceDate 

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

SELECT tblCustomers.[Last Name], 
   tblCustomers.[First Name] 
   FROM tblCustomers INNER JOIN tblCustomers AS A 
   ON tblCustomers.[Last Name]=A.[Last Name] 
   WHERE tblCustomers.[First Name]<>A.[First Name] 
   ORDER BY tblCustomers.[Last Name] 

ВНЕШНИЕ JOIN

OUTER JOIN используется для получения записей из нескольких таблиц при сохранении записей из одной из таблиц, даже если в другой таблице нет соответствующей записи. Ядро СУБД Access поддерживает два типа OUTER JOIN : LEFT OUTER JOIN и RIGHT OUTER JOIN.

Представьте себе две таблицы, которые находятся рядом друг с другом: таблицу слева и таблицу справа. Left OUTER JOIN выбирает все строки в правой таблице, которые соответствуют условиям реляционного сравнения, а также все строки из левой таблицы, даже если в правой таблице не существует совпадений. RIGHT OUTER JOIN — это просто обратная часть левого внешнего соединения; вместо этого сохраняются все строки в правой таблице.

В качестве примера предположим, что вы хотите определить общую сумму, выставленную каждому клиенту, но если у клиента нет счетов, вы хотите отобразить ее, отображая слово "NONE".

SELECT [Last Name] & ', ' &  [First Name] AS Name, 
   IIF(Sum(Amount) IS NULL,'NONE',Sum(Amount)) AS Total 
   FROM tblCustomers LEFT OUTER JOIN tblInvoices 
   ON tblCustomers.CustomerID=tblInvoices.CustomerID 
   GROUP BY [Last Name] & ', ' &  [First Name] 

В предыдущей инструкции SQL произошло несколько действий. Во-первых, используется оператор объединения строк "&". Этот оператор позволяет объединить два или несколько полей вместе в виде одной строки. Второй оператор является непосредственным оператором if (IIf), который проверяет, имеет ли итог значение NULL. Если это так, оператор возвращает слово NONE. Если итог не равен NULL, возвращается значение . Последнее — предложение OUTER JOIN . При использовании ФУНКЦИИ LEFT OUTER JOIN строки в левой таблице сохраняются, чтобы вы могли видеть всех клиентов, даже тех, у кого нет счетов.

OUTER JOIN могут быть вложены внутри INNER JOIN в соединении с несколькими таблицами, но INNER JOIN не могут быть вложены внутри OUTER JOIN.

Декартовой продукт

Термин, который часто встречается при обсуждении присоединения является декартовой продукт. Декартовой продукт определяется как "все возможные сочетания всех строк во всех таблицах". Например, если бы вы объединили две таблицы без каких-либо квалификаций или типа соединения, вы получите декартовой продукт.

SELECT * 
   FROM tblCustomers, tblInvoices 

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

Оператор UNION

Хотя оператор UNION , также известный как запрос на объединение, технически не является соединением, он включается здесь, так как он включает в себя объединение данных из нескольких источников данных в один результирующий набор, который похож на некоторые типы соединений. Оператор UNION используется для объединения данных из таблиц, инструкций SELECT или запросов, оставляя при этом все повторяющиеся строки. Оба источника данных должны иметь одинаковое количество полей, но они не должны иметь одинаковый тип данных. Предположим, что у вас есть таблица Employees, которая имеет ту же структуру, что и таблица Customers, и вы хотите создать список имен и адресов электронной почты путем объединения обеих таблиц.

SELECT [Last Name], [First Name], Email 
   FROM tblCustomers 
UNION 
SELECT [Last Name], [First Name], Email 
   FROM tblEmployees 

Чтобы получить все поля из обеих таблиц, можно использовать ключевое слово TABLE, как показано ниже.

TABLE tblCustomers 
UNION 
TABLE tblEmployees 

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

SELECT [Last Name], [First Name], Email 
   FROM tblCustomers 
UNION ALL 
SELECT [Last Name], [First Name], Email 
   FROM tblEmployees 

Инструкция TRANSFORM

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

Инструкция TRANSFORM используется для вычисления суммы, среднего, счетчика или другого типа совокупного итога по записям. Затем она отображает информацию в формате сетки или электронной таблицы с данными, сгруппированных по вертикали (строки) и горизонтально (столбцы). Ниже приведена общая форма инструкции TRANSFORM .

   TRANSFORM aggregating function 
   SELECT statement 
   PIVOT column heading field 

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

TRANSFORM 
IIF(Sum([Amount]) IS NULL,'NONE',Sum([Amount])) 
   AS Total 
SELECT [Last Name] & ', ' & [First Name] AS Name 
      FROM tblCustomers LEFT JOIN tblInvoices 
      ON tblCustomers.CustomerID=tblInvoices.CustomerID 
      GROUP BY [Last Name] & ', ' & [First Name] 
PIVOT Format(InvoiceDate, 'yyyy') 
   IN ('1996','1997','1998','1999','2000') 

Имейте в виду, что агрегатная функция — это функция Sum, вертикальные заголовки находятся в предложении GROUP BY инструкции SELECT, а горизонтальные заголовки определяются полем, указанным после ключевое слово PIVOT.

Поддержка и обратная связь

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.