Выполнение соединений с помощью 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 и обратная связь.
Join — инструмент объединения данных из нескольких связанных таблиц
Реляционная модель данных подразумевает отдельное хранение и возможность независимой обработки данных для каждой сущности.
Вместе с тем, часто возникает потребность собрать данные из нескольких связанных таблиц.
Как правило, сущности (таблицы) связаны друг с другом внешними связями по принципу (primary key — foreign key).
Связи могут быть типа «1 к 1» или «1 ко многим» (с вариантами «1 к 0 или 1», «1 к 0 или более», «1 к 2» и пр).
Связь «многие-ко-многим» в реляционной модели обеспечивается с помощью дополнительной таблицы связей (другие названия: Link-таблица, Bridge-таблица, xref-таблица).
В зависимости от характера связей между таблицами, операция соединения может быть:
- внутренним соединением (INNER JOIN). При этом:
- если для описания связи между наборами данных используются корреляционные подзапросы, то такой INNER JOIN называют CROSS APPLY
- если условие соединения отсутствует, то такой INNER JOIN называют “декартовым произведением” (CROSS JOIN, CARTESIAN PRODUCT)
- если для описания связи между наборами данных используются корреляционные подзапросы, то такой OUTER JOIN называют OUTER APPLY
Новичку порой трудно разобраться, когда и какой тип джоинов нужно использовать и чем одни из них лучше других.
В этой статье мы попробуем решить разными способами несколько практических задач, отмечая плюсы и минусы каждого решения.
Пример логической модели данных:
Обратите внимание на характер данных в наших таблицах.
У некоторых спортсменов нет категории, клуба или тренера (возможно, они неизвестны).
Встречаются спортсмены, которых ведёт несколько тренеров.Про одни сущности есть чуть больше информации чем про другие.
Знакомство с данными.
use tempdb go --содержимое таблиц: select * from dbo.SwimmingClub select * from dbo.Swimmer select * from dbo.Category (3 rows affected) (7 rows affected) (3 rows affected)
Пример #1. Найти всех спортсменов из клуба Янтарь, имеющих II спортивный разряд.
Используя старую нотацию:
use tempdb go select s.SwimmerID, s.FirstName, s.LastName, s.YearOfBirth, s.Gender, sc.[Name] Club, sc.City, c.[Name] Category from dbo.SwimmingClub sc, dbo.Swimmer s, dbo.Category c where sc.[Name] like N'%Янтарь%' and sc.SwimmingClubID = s.SwimmingClubID and s.CategoryID = c.CategoryID and c.[Name] = N'II' (1 row affected)
В этой форме записи WHERE-часть перегружена условиями. Часть из них относятся к логической модели данных (у. 2 и 3), часть – к логике текущей задачи (у. 1 и 4).
В сложных запросах WHERE-часть может стать просто огромной!
Используя новую нотацию:
use tempdb go select s.SwimmerID, s.FirstName, s.LastName, s.YearOfBirth, s.Gender, sc.[Name] Club, sc.City, c.[Name] Category from dbo.SwimmingClub sc inner join dbo.Swimmer s on s.SwimmingClubID = sc.SwimmingClubID inner join dbo.Category c on s.CategoryID = c.CategoryID where sc.[Name] like N'%Янтарь%' and c.[Name] = N'II' (1 row affected)
Преимуществом этой формы записи является “разгрузка” WHERE-части от условий, определенных логической моделью данных, при этом четко видно какое из условий используется для реализации связи между какими таблицами.
В сложных запросах новая форма записи позволяет добиться существенно лучшей читаемости кода (а, как увидим позже, еще и лучшей готовности кода к рефакторингу).
Используя CROSS JOIN:
use tempdb go select s.SwimmerID, s.FirstName, s.LastName, s.YearOfBirth, s.Gender, sc.[Name] Club, sc.City, c.[Name] Category from dbo.SwimmingClub sc cross join dbo.Swimmer s cross join dbo.Category c where sc.[Name] like N'%Янтарь%' and sc.SwimmingClubID = s.SwimmingClubID and s.CategoryID = c.CategoryID and c.[Name] = N'II' (1 row affected)
Обратите внимание на то, как запрос с CROSS JOIN похож на вариант решения в старой нотации!
На самом деле, ничего удивительного, т. к. логически они выражают один и тот же подход к решению задачи: “все источники данных — в FROM, все условия — в WHERE. При отсутствии условий каждая таблица декартово перемножается с каждой”.Вообще, вариантов решения задачи много. Но, как можно догадаться, не все они оптимальны;).
Например, порой начинающие программисты для решения первой задачи создают что-то вроде этого:
use tempdb go declare @ClubId int, @ClubCity nvarchar(30), @ClubName nvarchar(100), @CategoryId int set @ClubId = ( select SwimmingClubId from dbo.SwimmingClub where [Name] like N'%Янтарь%' ) set @ClubCity = ( select City from dbo.SwimmingClub where [Name] like N'%Янтарь%' ) set @ClubName = ( select [Name] from dbo.SwimmingClub where [Name] like N'%Янтарь%' ) set @CategoryId = ( select CategoryId from dbo.Category where [Name] = N'II' ) select SwimmerID, FirstName, LastName, YearOfBirth, Gender, @ClubName Club, @ClubCity City, N'II' Category from dbo.Swimmer where SwimmingClubID = @ClubId and CategoryId = @CategoryId (1 row affected)
Идея за этим кодом такова.
Чтобы найти всех спортсменов из клуба Янтарь, имеющих II спортивный разряд, нужно:
найти код клуба Янтарь (значение внешнего ключа SwimmingClubId)
2. dbo.Category
найти код категории, указывающей на II разряд (значение внешнего ключа CategoryId)
3. dbo.Swimmer
зная эти два значения, найти соответствующих пловцов
Приведенный выше алгоритм “напрашивается” сам собой. Именно поэтому разработчики, далекие от SQL, пишут такие решения.
Недостатком этого подхода являются:
- длинный код (объявление переменных + 5 инструкций SELECT)
- худшая производительность (пять селектов явно медленнее одного)
- неизолированность от конкурирующих транзакций (пока мы рассчитываем значения переменных, данные в любой из таблиц могут быть изменены кем-то другим)
- если есть несколько клубов со словом “Янтарь” в названии, любой из трех первых селектов упадет.
Пример #2. Вывести спортсменов из клуба Янтарь с теми же атрибутами что и выше, но без требования иметь II спортивный разряд.
Используя старую нотацию:
use tempdb go --это код с багом! в случае если у спортсмена нет разряда, запись о нем не выводится select s.SwimmerID, s.FirstName, s.LastName, s.YearOfBirth, s.Gender, sc.[Name] Club, sc.City, c.[Name] Category from dbo.SwimmingClub sc, dbo.Swimmer s, dbo.Category c where sc.[Name] like N'%Янтарь%' and sc.SwimmingClubID = s.SwimmingClubID and s.CategoryID = c.CategoryID --подправленный код (один из вариантов) select s.SwimmerID, s.FirstName, s.LastName, s.YearOfBirth, s.Gender, sc.[Name] Club, sc.City, (select c.[Name] from dbo.Category c where c.CategoryID = s.CategoryID) Category from dbo.SwimmingClub sc, dbo.Swimmer s where sc.[Name] like N'%Янтарь%' and sc.SwimmingClubID = s.SwimmingClubID (2 rows affected) (3 rows affected)
Интуитивно напрашивающееся решение адаптировать старый код под новые требования удалением соответствующего условия не работает!
В старой нотации нужно существенно менять сам код при незначительных изменениях в требованиях.
Используя новую нотацию:
use tempdb go select s.SwimmerID, s.FirstName, s.LastName, s.YearOfBirth, s.Gender, sc.[Name] Club, sc.City, c.[Name] Category from dbo.SwimmingClub sc inner join dbo.Swimmer s on s.SwimmingClubID = sc.SwimmingClubID left join dbo.Category c on s.CategoryID = c.CategoryID where sc.[Name] like N'%Янтарь%' (3 rows affected)
В отличии от старой, после удаления уже ненужного требования, в новой нотации нужно лишь поменять слово inner на left.
Вариант решения задачи с outer apply:
use tempdb go select s.SwimmerID, s.FirstName, s.LastName, s.YearOfBirth, s.Gender, sc.[Name] Club, sc.City, c.[Name] Category from dbo.SwimmingClub sc inner join dbo.Swimmer s on s.SwimmingClubID = sc.SwimmingClubID outer apply (select [Name] from dbo.Category c where c.CategoryID = s.CategoryId) c where sc.[Name] like N'%Янтарь%' (3 rows affected)
Несмотря на запись запроса, похожую на вариант с LEFT JOIN, этот способ не оптимален из-за имеющегося корреляционного подзапроса.
Последний приводит к тому, что выполнение идет по принципу row—by—row вместо set—based. Логически, мы выполняем корреляционный подзапрос с внешним параметром s.CategoryId столько раз, сколько строчек в таблице dbo.Swimmer.Корреляционные запросы существенно ухудшают производительность!
Вариант решения задачи с пользовательской скалярной функцией:
use tempdb go create or alter function dbo.fn_GetCategoryName(@CategoryID int) returns nvarchar as begin return (select [Name] from dbo.Category where CategoryId = @CategoryID) end go select s.SwimmerID, s.FirstName, s.LastName, s.YearOfBirth, s.Gender, sc.[Name] Club, sc.City, dbo.fn_GetCategoryName(s.CategoryId) Category from dbo.SwimmingClub sc inner join dbo.Swimmer s on s.SwimmingClubID = sc.SwimmingClubID where sc.[Name] like N'%Янтарь%' (3 rows affected)
Использование пользовательской функции, на первый взгляд, помогает упростить решение задачи и, опять же, масса разработчиков идет этим путем, следуя привычке разбивать сложный функционал на отдельные блоки.
Вместе с тем, это решение в условиях баз данных, скорее всего, еще хуже предыдущего:
- корреляционный подзапрос никуда не делся, он лишь мигрировал в тело функции
- в ходе выполнения запроса функция будет вызвана множество раз, что негативно влияет на производительность (из-за особенностей работы СУБД и интерпретатора языка SQL)
- для решения одной конкретной задачи понадобилось создать дополнительный постоянный объект БД – пользовательскую скалярную функцию.
Если пользоваться таким подходом постоянно, то скоро БД будет завалена множеством непонятных программных объектов!
Вывод.
В рассмотренных примерах победителем стал запрос, созданный в новой нотации без использования корреляционных подзапросов. Он максимально читабельный, легко меняется в случае изменения требований, производительный и не требует создания дополнительных объектов в БД.
Автор материала – Тимофей Гавриленко, преподаватель Тренинг центра ISsoft.
Образование: окончил с отличием математический факультет Гомельского Государственного Университета им. Франциска Скорины.
Microsoft Certified Professional (70-464, 70-465).
Работа: c 2011 года работает в компании ISsoft (ETL/BI Developer, Release Manager, Data Analyst/Architect, SQL Training Manager), на протяжении 10 лет до этого выступал как Sysadmin, DBA, Software Engineer.
В свободное время ведет бесплатные образовательные курсы для детей и взрослых, желающих повысить свой уровень компьютерной грамотности и переквалифицироваться в IT-специалиста.
Developing.ru
Не понятен термин "соединить".
Если имелось в виду объединение (Join), то Вы не указали,
по каким полям связаны таблицы между собой.Игорь Акопян Сообщения: 1419 Зарегистрирован: 13 окт 2004, 17:11 Откуда: СПБ Контактная информация:
полагаю что поля для связи надо таки добавить во вновь созданные таблицы. Без них нельзя ничего соединить
Таб2 это некая расширенная инфа для заказа и там один-к-одному наверное связь (просто ORDNO добавить), по Таб1 добавить наверное RefOrdNo будет много-к-одномуDryg Сообщения: 2 Зарегистрирован: 21 окт 2008, 23:09
короче!! в реале эти таблицы выглядят как 2! программа Optima WIN 6 там есть дизайнер отчетов!! программа работает под Вайлберт! и она делит их на три!! причем 1 и 3 таблица перется из одной поднозванием ORDERS а 2 это ADRESES !! в прогрпмме есть такая фигня типа создание SQL запроса!! мне нужно как то написать SQL запрос чтобы прога понимала что я их объеденяю только на этот отчет!! структура реальной базы не должна меняться!! DialogForm.qryOrdDate, ReportData.Table1 и ReportData.Table2 это как бы псевдоним в программе!! в реальной они выглядят как 2 всего.
Naeel Maqsudov Сообщения: 2551 Зарегистрирован: 20 фев 2004, 19:17 Откуда: Moscow, Russia Контактная информация:
Использование в запросе нескольких источников записей
т.е. соединяются только те строки таблиц, у которых в указанных полях находятся равные значения (эквисоединение). Естественно, могут быть использованы любые условия, хотя эквисоединение используется чаще всего, поскольку эта операция воссоздает некую сущность, декомпозированную на две других в результате процедуры нормализации.
Если разные таблицы имеют столбцы с одинаковыми именами, то для однозначности требуется использовать точечную нотацию:
В тех случаях, когда это не вызывает неоднозначности, использование данной нотации не является обязательным.
model maker 1232 A 1260 E Иногда в предложении FROM требуется указать одну и ту же таблицу несколько раз. В этом случае обязательным является переименование.
model_1 model_2 1232 1233 1232 1260 Обратите внимание, что в этом случае в других предложениях оператора SELECT уже нельзя использовать квалификатор Product, поскольку таблица Product уже не используется. Вместо него используется псевдоним prod. Кроме того, ссылаться теперь можно только на те поля таблицы Product, которые перечислены в подзапросе.
Явные операции соединения
В предложении FROM может быть указана явная операция соединения двух и более таблиц. Среди ряда операций соединения, описанных в стандарте языка SQL, многими серверами баз данных поддерживается лишь операция соединения по предикату. Синтаксис соединения по предикату имеет вид:
Соединение может быть либо внутренним (INNER), либо одним из внешних (OUTER). Служебные слова INNER и OUTER можно опускать, поскольку внешнее соединение однозначно определяется его типом - LEFT (левое), RIGHT (правое) или FULL (полное), а просто JOIN будет означать внутреннее соединение.
Предикат определяет условие соединения строк из разных таблиц. При этом INNER JOIN означает, что в результирующий набор попадут только те соединения строк двух таблиц, для которых значение предиката равно TRUE. Как правило, предикат определяет эквисоединение по внешнему и первичному ключам соединяемых таблиц, хотя это не обязательно.
maker model_1 model_2 price A 1232 1232 600.0 A 1232 1232 400.0 A 1232 1232 350.0 A 1232 1232 350.0 A 1233 1233 600.0 A 1233 1233 950.0 A 1233 1233 980.0 B 1121 1121 850.0 B 1121 1121 850.0 B 1121 1121 850.0 E 1260 1260 350.0 Внешнее соединение LEFT JOIN означает, что помимо строк, для которых выполняется условие предиката, в результирующий набор попадут все остальные строки из первой таблицы (левой). При этом отсутствующие значения полей из правой таблицы будут заполнены NULL-значениями.
maker model_1 model_2 price A 1232 1232 600.0 A 1232 1232 400.0 A 1232 1232 350.0 A 1232 1232 350.0 A 1233 1233 600.0 A 1233 1233 950.0 A 1233 1233 980.0 B 1121 1121 850.0 B 1121 1121 850.0 B 1121 1121 850.0 E 2111 NULL NULL E 2112 NULL NULL E 1260 1260 350.0 Поскольку моделей 2111 и 2112 из таблицы Product нет в таблице PC, в полях из таблицы PC содержится NULL.
Соединение RIGHT JOIN обратно соединению LEFT JOIN, т.е. в результирующий набор попадут все строки из второй таблицы, которые будут соединяться только с теми строками из первой таблицы, для которых выполняется условие соединения. В нашем случае левое соединениеProduct LEFT JOIN PC ON PC.model = Product.model
будет эквивалентно правому соединению
PC RIGHT JOIN Product ON PC.model = Product.model
a b c d 1 2 NULL NULL 2 1 2 4 NULL NULL 3 3 Заметим, что это соединение симметрично, т.е. "A FULL JOIN B" эквивалентно "B FULL JOIN A". Обратите также внимание на обозначение A.*, что означает "все поля таблицы А".