Многотабличные и вложенные запросы

ЛЕКЦИЯ № 8 3.3. Многотабличные и вложенные запросы..................1 3.3.1. Соединения таблиц............................................2 3.3.1.1. Неявное соединение таблиц...................2 3.3.1.2. Явное соединение таблиц.......................6 3.3.1.3. Реализация стандартных операций реляционной алгебры.. 13 3.3.1.4. Соединение таблицы со своей копией. 17 3.3. МНОГОТАБЛИЧНЫЕ И ВЛОЖЕННЫЕ ЗАПРОСЫ СУБД поддерживают 3 варианта запросов к множеству таблиц с помощью одного SQL-запроса на выборку: соединения, подзапросы и объединения [29, 52]. Эти 3 метода поиска данных во множестве таблиц существенно отличаются друг от друга и, как правило, решают разные виды поисковых задач. Соединение является одной из наиболее востребованных операций в запросе SELECT. Поскольку большинство БД являются нормализованными, часто возникает необходимость получения данных из нескольких таблиц. Соединения дают возможность формировать результирующий набор из столбцов одной или более таблиц, которые хранят связанные данные. Множество столбцов, выбранных из каждой таблицы, называются потоками. Процесс соединения объединяет выбранные столбцы в единый выходной набор данных. Стандарты SQL поддерживают 2 варианта синтаксиса соединения: неявное и явное. Неявное соединение соответствует более старому SQL-стандарту. Таблицы, участвующие в соединении, задаются списком с разделяющими запятыми в секции FROM запроса SELECT. Условия для связи таблиц задаются среди условий поиска секции WHERE. Не существует специального синтаксиса для указания, какие условия используются для поиска, а какие – для соединения. В стандарте SQL:92 введен более универсальный синтаксис явного соединения, которое осуществляется с помощью конструкции JOIN…ON. Структура конструкции JOIN…ON дает возможность отличать условие соединения от условий поиска. Следует отметить, что неявное соединение может быть всегда заменено эквивалентным явным, обратное же возможно не всегда. Подзапросом является запрос, заключенный в круглые скобки и вложенный в секцию SELECT, FROM, WHERE или HAVING основного (внешнего) запроса SELECT или других запросов, использующих эти секции. Правила построения подзапросов изменяются в соответствии с целью запроса. Оба метода – и соединения, и подзапросы – используют слияние потоков данных из строк различных таблиц. В связи с этим при некоторых условиях их роли могут совпадать. Тем не менее, рациональнее использовать разные 1 методы для решения разных задач, а именно: для сравнения значений агрегатных функций с другими значениями – подзапросы, для выборки информации из нескольких таблиц – соединения [53]. В этом заключается основное различие рассматриваемых видов запросов ко множеству таблиц. Кроме того, соединения позволяют формировать набор данных из столбцов таблицы или таблиц, хранящих связанные данные, в то время, как в подзапросах результаты внешнего и внутреннего запросов могут быть и не связаны. Запросы объединения, реализуемые с помощью оператора UNION, дают возможность выбрать строки из различных наборов данных в объединенный набор данных, причем подмножества не обязательно должны быть связаны друг с другом – просто они должны соответствовать друг другу структурно. Соединения, подзапросы и объединения не являются взаимоисключающими. Соединения и объединения могут включать подзапросы, и некоторые подзапросы могут содержать соединения. 3.3.1. Соединения таблиц 3.3.1.1. Неявное соединение таблиц Как уже отмечалось, различные виды соединений используются для получения составных наборов данных, содержащих столбцы из нескольких таблиц, которые хранят связанные данные. Формат секций FROM и WHERE при неявном соединении таблиц имеет вид: FROM <таблица1> [[AS] псевдоним1], <таблица2> [[AS] псевдоним2]… [WHERE <условие_соединения> [AND <условие_поиска>]… ], где <условие_соединения>::= <таблица1>.столбец <операция_сравнения> <таблица2>.столбец, <операция_сравнения> ::= { = | < | > | {<= | !> } | { >= | !< } | { <> | != }}; В Oracle операции сравнения !> и !< не используются. Особенности синтаксиса неявных соединений.  Использование более одной таблицы в секции FROM (список таблиц с разделяющими запятыми).  Среди остальных условий поиска секции WHERE применяется операция сравнения для создания выражения, которое определяет связанные столбцы, используемые для соединения указанных таблиц (<условие_соединения>). При этом соединение на основе точного равенства между двумя столбцами называется соединением по равенству (эквивалентности). Алгоритм выполнения запросов на неявное соединение таблиц состоит из нескольких этапов [53]. 1. Вычисляется декартово произведение таблиц, входящих в соединение, т. е. для каждой строки одной из таблиц берутся все возможные сочетания строк из других таблиц. 2 2. Производится отбор строк из полученной таблицы согласно условиям в секции WHERE. 3. Осуществляется проекция (вывод) по столбцам, указанным в списке возвращаемых элементов. Среди запросов на соединение таблиц наиболее распространены запросы к таблицам, которые связаны с помощью отношения родитель-потомок. Чтобы использовать в запросе отношение родитель-потомок, необходимо задать <условие_соединения>, в котором первичный ключ родительской таблицы сравнивается с внешним ключом таблицы-потомка (обычно имена этих столбцов совпадают в связанных таблицах). Несмотря на то, что в языке определения данных присутствует возможность декларативного задания первичных и внешних ключей таблиц, связь, о которой идет речь, должна всегда явно указываться в секции WHERE запроса SELECT. Например, необходимо вывести для всех абонентов названия улиц, на которых они проживают (рис. 3.82). Для этого надо каждую строку из таблицы Abonent связать по полю внешнего ключа (столбец StreetCD в таблице Abonent) с таблицей улиц (столбец StreetCD в таблице Street). Следующий запрос позволяет получить требуемый результат: SELECT Abonent.Fio, Street.StreetCD, Street.StreetNM FROM Abonent, Street WHERE Abonent.StreetCD = Street.StreetCD; Данный запрос, как и большинство приведенных в учебнике запросов с неявным и явным соединением, корректен как в Firebird, так и в Oracle и MS SQL Server. При этом в MS SQL Server результаты многих многотабличных запросов будут отличаться порядком следования строк от результатов, приведенных для Firebird. FIO Аксенов С.А. Мищенко Е.В. Конюхов В.С. Тулупова М.И. Свирина З.А. Тимошкина Н.Г. Лукашина Р.М. Шубина Т. П. Стародубцев Е.В. Шмаков С.В. Маркова В.П. Денисова Е.К. STREETCD 3 3 3 7 7 6 8 8 4 4 4 4 STREETNM ВОЙКОВ ПЕРЕУЛОК ВОЙКОВ ПЕРЕУЛОК ВОЙКОВ ПЕРЕУЛОК КУТУЗОВА УЛИЦА КУТУЗОВА УЛИЦА МОСКОВСКАЯ УЛИЦА МОСКОВСКОЕ ШОССЕ МОСКОВСКОЕ ШОССЕ ТАТАРСКАЯ УЛИЦА ТАТАРСКАЯ УЛИЦА ТАТАРСКАЯ УЛИЦА ТАТАРСКАЯ УЛИЦА Рис. 3.82. Результат выполнения многотабличного запроса 3 Следует обратить внимание на то, что к столбцам таблиц обращение производится на основе полного имени столбца (указание таблицы, к которой он относится), чтобы исключить неоднозначности, возникающие в случае присутствия в разных таблицах столбцов с одинаковыми именами (например, столбцов StreetCD в таблицах Abonent и Street). Чтобы ускорить ввод запросов и сделать их более понятными, в списке таблиц можно определить псевдонимы таблиц (сокращенные имена). Например, предыдущий запрос можно записать более кратко: SELECT A.Fio, S.StreetCD, S.StreetNM FROM Abonent A, Street S WHERE A.StreetCD = S.StreetCD; В многотабличном запросе можно комбинировать условие соединения, в котором задаются связанные столбцы, с условиями поиска. Например, для вывода ФИО абонентов, которым за 2013 г. начислены суммы более 350 (рис. 3.83), можно использовать запрос: SELECT A.Fio, N.NachislSum FROM Abonent A, NachislSumma N WHERE A.AccountCD = N.AccountCD AND NachislYear = 2013 AND NachislSum > 350; FIO NACHISLSUM Термин «соединение» Конюхов В.С. 611,30 применяется к любому запросу, который Стародубцев Е.В. 444,45 объединяет данные Денисова Е.К. 528,44 нескольких таблиц БД Тимошкина Н.Г. 454,60 путем сравнения значений Рис. 3.83. Результат выполнения в парах столбцов этих запроса таблиц. Самыми многотабличного с комбинированным условием распространенными являются соединения по поиска равенству. Кроме этого, имеется возможность соединять таблицы с помощью других операций сравнения. Например, чтобы вывести все комбинации ФИО абонентов и исполнителей ремонтных заявок так, чтобы ФИО абонентов были больше при сравнении, можно использовать следующий запрос с соединением таблиц по неравенству: SELECT A.Fio, E.Fio FROM Abonent A, Executor E WHERE A.Fio > E.Fio; Как следует из данного примера, соединения таблиц по условиям, отличающимся от равенства, во многом искусственны. Поэтому в подавляющем большинстве случаев таблицы соединяются по равенству, а другие операции сравнения используются для дополнительного отбора строк в условии поиска. SQL позволяет соединять 3 и более таблицы, используя ту же самую методику, что и при соединении данных из двух таблиц. Например, чтобы в 4 предыдущем запросе вместе со значениями начислений вывести и значения оплат за тот же период и за ту же услугу (рис. 3.84), можно использовать соединение трех таблиц: SELECT A.Fio, N.NachislSum, P.PaySum FROM Abonent A, NachislSumma N, PaySumma P WHERE A.AccountCD = N. AccountCD AND A.AccountCD = P.AccountCD AND N.ServiceCD = P.ServiceCD AND N.NachislMonth =P.PayMonth AND N.NachislYear = P.PayYear AND NachislYear = 2013 AND NachislSum > 350; FIO Конюхов В.С. Стародубцев Е.В. Денисова Е.К. Тимошкина Н.Г. NACHISLSUM 611,30 444,45 528,44 454,60 PAYSUM 611,30 444,45 528,44 460,00 Рис. 3.84. Результат выполнения запроса на соединение трех таблиц Следует отметить, что результат выполнения запроса на неявное соединение более двух таблиц не зависит от порядка перечисления этих таблиц в секции FROM и от порядка указания условий соединения в секции WHERE. В таблицах соединяются только те строки, для которых выполняется <условие_соединения>, и независимо от порядка соединения результат будет одинаковый. Можно соединять данные из трех и более таблиц, связанных более чем одним отношением родитель-потомок. Например, для того чтобы определить исполнителей, которым назначены ремонтные заявки абонентов, необходимо соединить таблицы Abonent, Request и Executor. Для этого надо каждую строку из таблицы Request связать по полю внешнего ключа AccountCD со справочником абонентов (столбец AccountCD в таблице Abonent), а по полю внешнего ключа ExecutorCD – со справочником исполнителей (столбец ExecutorCD в таблице Executor). Для этого можно использовать такой запрос: SELECT DISTINCT A.Fio AS Fio_Abonent, E.Fio AS Fio_Executor FROM Abonent A, Executor E, Request R WHERE R.AccountCD = A.AccountCD AND R.ExecutorCD = E.ExecutorCD; Результат выполнения запроса представлен на рис. 3.85. FIO_ABONENT Аксенов С.А. Аксенов С.А. Аксенов С.А. FIO_EXECUTOR Стародубцев Е.М. Школьников С.М. Шлюков М.К. 5 Денисова Е.К. Денисова Е.К. Конюхов В.С. Лукашина Р.М. Мищенко Е.В. Мищенко Е.В. Мищенко Е.В. Мищенко Е.В. Свирина З.А. Стародубцев Е.В. Стародубцев Е.В. Тимошкина Н.Г. Шмаков С.В. Шубина Т. П. Булгаков Т.И. Школьников С.М. Стародубцев Е.М. Стародубцев Е.М. Булгаков Т.И. Стародубцев Е.М. Шлюков М.К. Шубин В.Г. Шубин В.Г. Стародубцев Е.М. Рис. 3.85. Результат на соединение трех таблиц выполнения Школьников С.М. Шлюков М.К. Стародубцев Е.М. Шубин В.Г. запроса Таким образом, при выполнении операции неявного соединения данные из двух таблиц комбинируются с образованием пар связанных строк, в которых значения сопоставляемых столбцов являются одинаковыми. Если одно из значений в сопоставляемом столбце одной таблицы не совпадает ни с одним из значений в сопоставляемом столбце другой таблицы, то соответствующая строка удаляется из результирующей таблицы. 3.3.1.2. Явное соединение таблиц Другим способом связывания таблиц является явное соединение, осуществляемое с помощью JOIN. Формат секции FROM при таком соединении таблиц имеет вид: FROM {<таблица1> [псевдоним1] <тип_соединения1> <таблица2> [псевдоним2] [ { ON <условие_соединения1> | USING (<список_столбцов>) }] } [<тип_соединения2> <таблица3> [псевдоним3] [ { ON <условие_соединения2> | USING (<список_столбцов>) }]]…, где <тип_соединения>::= {CROSS JOIN | [NATURAL] [{INNER | {LEFT | RIGHT | FULL} [OUTER]}] JOIN }. Итак, существуют различные типы явного соединения таблиц. 1. Перекрестное соединение CROSS JOIN используется без конструкции ON <условие_соединения>. CROSS JOIN эквивалентно декартовому произведению таблиц. То есть конструкция … FROM A CROSS JOIN B полностью эквивалентна конструкции … FROM A, B. Перекрестное (полное) соединение группирует строки таблиц по правилу «каждая с каждой». Первая строка первой таблицы соединяется с первой 6 строкой второй таблицы, потом первая строка первой таблицы соединяется со второй строкой второй таблицы и так до тех пор, пока в первой таблице не закончатся строки. 2. Уточненные соединения, которые предполагают явное задание условия соединения после ON или имен столбцов, по которым производится соединение, после USING. Для визуального представления различных типов соединения часто используются диаграммы Эйлера-Венна [54]. Диаграмма Венна представляет собой схематичное изображение всех возможных пересечений нескольких множеств и полезна для понимания, как происходит выборка данных в таблицах с использованием соединений и условий. Среди уточненных соединений выделяют:  INNER JOIN – «внутреннее» соединение (рис. 3.86). В таблицах соединяются только те строки, для которых выполняется <условие_соединения>: все значения связанных столбцов одной таблицы попарно находятся в заданном оператором сравнения отношении с соответствующими значениями связанных столбцов другой таблицы. Остальные строки из соединения исключаются. Этот вид соединения используется чаще всего. Рис. 3.86. Диаграмма Венна для «внутреннего» соединения  OUTER JOIN – «внешнее» соединение. Данное ключевое слово является необязательным и имеет смысл только в комбинации с ключевым словом определения типа внешнего соединения. Внешние соединения бывают трех типов: LEFT [OUTER] JOIN – «левое (внешнее)» соединение (рис. 3.87); выполняется сравнение значений связанных столбцов и в результат запроса включаются все строки левой таблицы и только те строки правой таблицы, для которых выполняется <условие_соединения> (для строк из левой таблицы, для которых не найдено соответствия в правой таблице, в столбцы, извлекаемые из правой таблицы, заносится NULL); Рис. 3.87. Диаграмма Венна для «левого (внешнего)» соединения 7 RIGHT [OUTER] JOIN – «правое (внешнее)» соединение (рис. 3.88); является зеркальным отображением левого внешнего соединения, в нем тоже сначала выполняется сравнение значений связанных столбцов, но в результат запроса включаются все строки правой таблицы и только те строки левой таблицы, для которых выполняется <условие_соединения> (для строк из правой таблицы, для которых не найдено соответствия в левой таблице, в столбцы, извлекаемые из левой таблицы, заносится NULL); Рис. 3.88. Диаграмма Венна для «правого (внешнего)» соединения FULL [OUTER] JOIN – «полное (внешнее)» соединение – это комбинация «левого» и «правого» соединения таблиц (рис. 3.89); позволяет возвращать все строки как из правой, так и из левой таблицы, включая те, которые не удовлетворяют условиям соединения (строки, у которых нет пары по связанным столбцам, возвращаются со значениями NULL в выходных столбцах «противоположной» таблицы). Рис. 3.89. Диаграмма Венна для «полного (внешнего)» соединения Если не указан тип соединения в JOIN, то он по умолчанию принимается за INNER. 3. Естественное соединение (NATURAL JOIN). Стандарт SQL определяет это соединение как результат соединения таблиц по всем одноименным столбцам. Соединяются те строки, в которых все значения одноименных столбцов одной таблицы попарно совпадают с соответствующими значениями одноименных столбцов другой таблицы. Остальные строки из соединения исключаются. Если одноименных столбцов нет, выполняется перекрестное соединение CROSS JOIN. Естественное соединение не требует задания каких-либо условий (используется без ON <условие_соединения>). Может применяться при внутреннем и внешнем соединении таблиц. В Oracle формат секции FROM существенных отличий от вышеприведенного не имеет. 8 В MS SQL Server формат секции FROM при явном соединении таблиц несколько отличается: FROM {<таблица1> [псевдоним1] <тип_соединения1> <таблица2> [псевдоним2] [ ON <условие_соединения1> ] } [<тип_соединения2> <таблица3> [псевдоним3] [ ON <условие_соединения2> ]]…, где <тип_соединения>::= { CROSS JOIN | { CROSS | OUTER } APPLY | { [ { INNER | {LEFT | RIGHT | FULL} [OUTER] } ] [<подсказка соединения>] JOIN } }. Из приведенного синтаксиса видны особенности реализации явных соединений в MS SQL Server [37]: - для задания условия соединения используется только ключевое слово ON, USING не поддерживается; - не поддерживается естественное соединение NATURAL JOIN; - может использоваться оператор APPLY с ключевыми словами CROSS и OUTER, который позволяет соединять таблицу с возвращающей табличное значение функцией (table valued function - TVF) по принципу перекрестного либо внешнего соединения; - может быть задана подсказка соединения, которая указывает оптимизатору запросов на выбор определенной стратегии соединения таблиц. В уточненных явных соединениях <условие_соединения>, указываемое после ON, аналогично рассмотренному выше условию соединения в секции WHERE. При соединении данных из нескольких таблиц конструкции JOIN и WHERE в большинстве случаев взаимозаменяемы. Однако зачастую конструкция JOIN является более удобной для понимания. Например, конструкция JOIN…ON дает возможность отличать условие соединения, указываемое после ON, от условия поиска, указываемого в секции WHERE. Например, необходимо вывести для всех абонентов названия и коды улиц, на которых они проживают. Решение этого примера на основе конструкции WHERE было приведено на рис. 3.82. Реализация запроса посредством явного внутреннего соединения таблиц, позволяющего выбрать только строки, которые есть и в таблице Street и в таблице Abonent, имеет вид: SELECT A.Fio, S.StreetCD, S.StreetNM FROM Abonent A INNER JOIN Street S ON A.StreetCD = S.StreetCD; Если в этом же примере использовать правое внешнее соединение, то получится список всех улиц и проживающих на них абонентов, если таковые имеются. Запрос выглядит так: SELECT A.Fio, S.StreetCD, S.StreetNM FROM Abonent A RIGHT JOIN Street S ON A.StreetCD = S.StreetCD; Результат выполнения запроса представлен на рис. 3.90. FIO Аксенов С.А. Мищенко Е.В. STREETCD STREETNM 3 ВОЙКОВ ПЕРЕУЛОК 3 ВОЙКОВ ПЕРЕУЛОК 9 Конюхов В.С. Тулупова М.И. Свирина З.А. Тимошкина Н.Г. Лукашина Р.М. Шубина Т. П. Стародубцев Е.В. Шмаков С.В. Маркова В.П. Денисова Е.К. NULL NULL NULL 3 7 7 6 8 8 4 4 4 4 5 1 2 ВОЙКОВ ПЕРЕУЛОК КУТУЗОВА УЛИЦА КУТУЗОВА УЛИЦА МОСКОВСКАЯ УЛИЦА МОСКОВСКОЕ ШОССЕ МОСКОВСКОЕ ШОССЕ ТАТАРСКАЯ УЛИЦА ТАТАРСКАЯ УЛИЦА ТАТАРСКАЯ УЛИЦА ТАТАРСКАЯ УЛИЦА ГАГАРИНА УЛИЦА ЦИОЛКОВСКОГО УЛИЦА НОВАЯ УЛИЦА Рис. 3.90. Результат правого внешнего соединения Для каждого абонента выводится название улицы, на которой он проживает, а если на какой-либо улице не проживают абоненты, то в столбце Fio строки для данной улицы выводится NULL. Если в данном примере использовать левое внешнее соединение, то результат будет совпадать с результатом внутреннего соединения (см. рис. 3.82), так как в учебной БД нет абонентов, для которых не указана улица проживания. Если же использовать полное внешнее соединение, то для данного примера результат будет совпадать с результатом правого внешнего соединения (см. рис. 3.90). Нужно отметить, что необязательно в качестве имен соединяемых таблиц использовать только явные имена таблиц или только псевдонимы таблиц. Допускается использовать их сочетание. Следующий запрос (использующий по умолчанию «внутреннее» соединение таблиц) вернет правильный результат: SELECT Abonent.Fio, S.StreetCD, S.StreetNM FROM Abonent JOIN Street S ON Abonent.StreetCD = S.StreetCD; Рассмотрим примеры запросов, в которых используются явное соединение таблиц и условие поиска в секции WHERE. Например, если предыдущий запрос с явным «правым» соединением дополнить условием и выполнить запрос SELECT A.Fio, S.StreetCD, S.StreetNM FROM Abonent A RIGHT JOIN Street S ON A.StreetCD = S.StreetCD WHERE A.StreetCD IS NULL;, то будет выведена информация только об улицах с кодами 1, 2 и 5, на которых не проживают абоненты. 10 Для выбора ФИО абонентов, которые производили оплату за услугу с кодом 2 позднее 1 октября 2011 г. (рис. 3.91), запрос с явным «внутренним» соединением может быть построен так: SELECT A.Fio, P.PaySum FROM Abonent A JOIN PaySumma P ON A.AccountCD = P.AccountCD WHERE ServiceCD=2 AND PayDate > '01.10.2011'; FIO Аксенов С.А. Аксенов С.А. Мищенко Е.В. Стародубцев Е.В. Шмаков С.В. Денисова Е.К. Шубина Т. П. Шубина Т. П. Тимошкина Н.Г. PAYSUM 58,70 56,00 250,00 80,00 56,00 58,70 80,00 80,00 30,00 Рис. 3.91. Результат наложения дополнительных условий поиска для соединяемых таблиц В Firebird существует возможность более простого, по сравнению с конструкцией ON, задания условия соединения – соединение по именам столбцов. Если таблицы соединяются по одноименным столбцам, то можно использовать конструкцию USING (<список_столбцов>). В <списке_столбцов> указываются имена всех столбцов, по значениям в которых требуется соединить таблицы. При создании соединения по именам столбцов нужно помнить:  что все столбцы, указанные в списке столбцов, должны существовать в соединяемых таблицах;  по всем указанным столбцам автоматически создается такое соединение, как если бы было указано <таблица1>.столбец = <таблица2>.столбец в секции WHERE при неявном соединении или после ON при явном соединении. Например, рассмотренный ранее запрос, выводящий для всех абонентов названия и коды улиц, можно реализовать с использованием конструкции USING: SELECT A.Fio, S.StreetCD, S.StreetNM FROM Abonent A INNER JOIN Street S USING (StreetCD); Этот же запрос можно реализовать с помощью естественного соединения: SELECT A.Fio, S.StreetCD, S.StreetNM FROM Abonent A NATURAL JOIN Street S; С помощью оператора JOIN можно соединять 3 и более таблицы. Порядок соединений может уточняться круглыми скобками, так как результат нескольких внешних соединений зависит от порядка их выполнения. 11 Например, вывести 10 первых строк c адресом и ФИО абонентов, проживающих на улицах, наименования которых начинаются с букв Г или М, указав для каждого абонента значения начислений, в Firebird можно с помощью запроса: SELECT FIRST 10 S.StreetNM,('д.'||A.HouseNo) AS House, ('кв.'||A.FlatNo) AS Flat, A.Fio, N.NachislSum, (N.NachislMonth || ' месяц ' || N.NachislYear || ' г.') AS Period FROM (Abonent A RIGHT JOIN Street S ON A.StreetCD = S.StreetCD) FULL JOIN NachislSumma N ON A.AccountCD = N.AccountCD WHERE (S.StreetNM LIKE 'М%') OR (S.StreetNM LIKE 'Г%') ORDER BY N.NachislYear ASC, N.NachislMonth ASC; Результат выполнения запроса представлен на рис. 3.92. STREETNM ГАГАРИНА УЛИЦА МОСКОВСКОЕ ШОССЕ МОСКОВСКОЕ ШОССЕ МОСКОВСКАЯ УЛИЦА МОСКОВСКАЯ УЛИЦА МОСКОВСКАЯ УЛИЦА МОСКОВСКОЕ ШОССЕ МОСКОВСКОЕ ШОССЕ МОСКОВСКОЕ ШОССЕ МОСКОВСКОЕ ШОССЕ HO FL FIO USE AT NUL NU NULL L LL д.35 кв.1 Лукашин 1 а Р.М. д.35 кв.1 Лукашин 1 а Р.М. д.35 кв.6 Тимошки на Н.Г. д.35 кв.6 Тимошки на Н.Г. д.35 кв.6 Тимошки на Н.Г. д.35 кв.1 Лукашин 1 а Р.М. д.35 кв.1 Лукашин 1 а Р.М. д.39 кв.3 Шубина 6 Т. П. д.39 кв.3 Шубина 6 Т. П. NACHIS PERIOD LSUM NULL NULL 22,86 12,60 58,10 444,43 60,10 56,00 254,40 32,56 80,00 4 месяц 2010 г. 8 месяц 2010 г. 12 месяц 2010 г. 3 месяц 2011 г. 5 месяц 2011 г. 6 месяц 2011 г. 7 месяц 2011 г. 9 месяц 2011 г. 10 месяц 2011 г. Рис. 3.92. Результат соединения трех таблиц В НД включена и ГАГАРИНА УЛИЦА, на которой не проживают абоненты, так как сначала выполнялось правое внешнее соединение таблиц Abonent и Street, а затем полное внешнее соединение полученной таблицы 12 с таблицей NachislSumma. Итоговый результат отсортирован по возрастанию года начислений, а внутри года – по возрастанию месяца. 3.3.1.3. Реализация стандартных операций реляционной алгебры Селекция (горизонтальное подмножество). Создается из тех строк таблицы, которые удовлетворяют заданным условиям: SELECT * FROM Abonent WHERE Phone IS NOT NULL; Проекция (вертикальное подмножество). Создается из указанных столбцов таблицы с последующим исключением избыточных дубликатов строк: SELECT DISTINCT StreetCD FROM Abonent; Декартово произведение. Для получения декартова произведения таблиц в секции FROM необходимо указать перечень перемножаемых таблиц, а в секции SELECT – все их столбцы. Перемножим таблицы Abonent (12 строк) и Street (8 строк) и получим результирующую таблицу (96 строк): SELECT Abonent.*, Street.* FROM Abonent, Street; Фрагмент результата выполнения запроса представлен на рис. 3.93. Такой же результат может быть получен, если использовать запрос на явное перекрестное соединение: SELECT Abonent.*, Street.* FROM Abonent CROSS JOIN Street; Таким образом, декартово произведение двух таблиц – это набор всевозможных комбинаций строк из двух таблиц. ACCO STR HOU FL FIO UNTC EET SEN AT D CD O NO 005488 3 4 1 Аксенов С.А. 115705 3 1 82 Мищенко Е.В. 015527 3 1 65 Конюхов В.С. … … … … … 005488 3 4 1 Аксенов С.А. 115705 3 1 82 Мищенко Е.В. 015527 3 1 65 Конюхов В.С. … … … … … 080047 8 39 36 Шубина Т. П. PH STRE STREETNM ON ETC E D1 5568 3 ВОЙКОВ 93 ПЕРЕУЛОК 7699 3 ВОЙКОВ 75 ПЕРЕУЛОК 7616 3 ВОЙКОВ 99 ПЕРЕУЛОК … … … 5568 7 КУТУЗОВА 93 УЛИЦА 7699 7 КУТУЗОВА 75 УЛИЦА 7616 7 КУТУЗОВА 99 УЛИЦА … … … 2578 2 НОВАЯ 42 УЛИЦА 13 080270 6 35 6 Тимошкин 3210 2 а Н.Г. 02 НОВАЯ УЛИЦА Рис. 3.93. Декартово произведение таблиц Abonent и Street Экви-соединение. Для получения экви-соединения таблиц необходимо для декартова произведения таблиц установить имеющее смысл соответствие на основе равенства между столбцами соединяемых таблиц. Например, запрос на неявное экви-соединение таблиц Abonent и Street будет выглядеть таким образом: SELECT Abonent.*, Street.* FROM Abonent, Street WHERE Abonent.StreetCD = Street.StreetCD; Фрагмент результата выполнения запроса представлен на рис. 3.94. ACCO STRE HOUS FLA FIO PHO STREE STREETNM UNTC ETCD ENO TNO NE TCD1 D 005488 3 4 1 Аксенов 55689 3 ВОЙКОВ С.А. 3 ПЕРЕУЛОК 115705 3 1 82 Мищенко 76997 3 ВОЙКОВ Е.В. 5 ПЕРЕУЛОК 015527 3 1 65 Конюхов 76169 3 ВОЙКОВ В.С. 9 ПЕРЕУЛОК 443690 7 5 1 Тулупова М 21483 7 КУТУЗОВА .И. 3 УЛИЦА 136159 7 39 1 Свирина NULL 7 КУТУЗОВА З.А. УЛИЦА … … … … … … … … Рис. 3.94. Экви-соединение таблиц Abonent и Street Такой же результат может быть получен, если использовать запрос на явное соединение: SELECT Abonent.*, Street.* FROM Abonent JOIN Street ON Abonent.StreetCD = Street.StreetCD; Естественное соединение. Для получения естественного соединения таблиц необходимо в экви-соединении таблиц исключить дубликаты повторяющихся столбцов (входящих в условие соединения). Для предыдущего примера естественное соединение таблиц Abonent и Street по столбцу StreetCD выглядит так: SELECT AccountCD, Street.StreetCD, StreetNM, HouseNo, FlatNo, Fio, Phone FROM Abonent, Street WHERE Abonent.StreetCD = Street.StreetCD; Фрагмент результата выполнения запроса представлен на рис. 3.95. 14 ACCO UNTC D 005488 115705 015527 443690 136159 … STR STREETN HOU FLA FIO EET M SEN TN CD O O 3 ВОЙКОВ 4 1 Аксенов ПЕРЕУЛО С.А. К 3 ВОЙКОВ 1 82 Мищенко ПЕРЕУЛО Е.В. К 3 ВОЙКОВ 1 65 Конюхов ПЕРЕУЛО В.С. К 7 КУТУЗОВ 5 1 Тулупова А УЛИЦА М.И. 7 КУТУЗОВ 39 1 Свирина А УЛИЦА З.А. … … … … … PHONE 556893 769975 761699 214833 NULL … Рис. 3.95. Естественное соединение таблиц Abonent и Street Аналогичный результат может быть получен, если использовать следующий запрос на явное естественное соединение, например в Firebird: SELECT AccountCD, StreetCD, StreetNM, HouseNo, FlatNo, Fio, Phone FROM Abonent NATURAL JOIN Street; Композиция. Для создания композиции таблиц нужно исключить из вывода все столбцы, по которым проводилось соединение таблиц: SELECT AccountCD, StreetNM, HouseNo, FlatNo, Fio, Phone FROM Abonent, Street WHERE Abonent.StreetCD = Street.StreetCD; Фрагмент результата выполнения запроса представлен на рис. 3.96. ACCO STREETNM HOUS FLA UNTCD ENO TNO 005488 ВОЙКОВ 4 1 ПЕРЕУЛОК 115705 ВОЙКОВ 1 82 ПЕРЕУЛОК 015527 ВОЙКОВ 1 65 ПЕРЕУЛОК 443690 КУТУЗОВА 5 1 УЛИЦА 136159 КУТУЗОВА 39 1 УЛИЦА 080270 МОСКОВСКА 35 6 Я УЛИЦА … … … … FIO Аксенов С.А. Мищенко Е.В. Конюхов В.С. Тулупова М.И. Свирина З.А. Тимошкин а Н.Г. … PHONE 556893 769975 761699 214833 NULL 321002 … Рис. 3.96. Композиция таблиц Abonent и Street 15 Тета-соединение. Тета-соединение предназначено для тех случаев, когда необходимо соединить две таблицы на основе некоторых условий, отличных от равенства. Получить тета-соединение таблиц Abonent и Street можно таким образом: SELECT Abonent.*, Street.* FROM Abonent, Street WHERE Abonent.StreetCD < Street.StreetCD; Фрагмент результата выполнения запроса представлен на рис. 3.97. ACC OUN TCD 00548 8 11570 5 01552 7 44306 9 13616 12611 2 13616 9 08027 … 01552 7 44306 9 13616 12611 2 13616 9 STR HOU FL FIO EET SEN AT CD O NO 3 4 1 Аксенов С.А. 3 1 82 Мищенко Е.В. 3 1 65 Конюхов В.С. 4 51 55 Стародубц ев Е.В. 4 9 15 Шмаков С.В. 4 7 11 Маркова В.П. 4 7 13 Денисова Е.К. 6 35 6 Тимошкин а Н.Г. … … … … 3 1 65 Конюхов В.С. 4 51 55 Стародубц ев Е.В. 4 9 15 Шмаков С.В. 4 7 11 Маркова В.П. 4 7 13 Денисова Е.К. PH ON E 556 893 769 975 761 699 683 014 NU LL 683 301 680 305 321 002 … 761 699 683 014 NU LL 683 301 680 305 STRE STREETNM ETC D1 7 КУТУЗОВА УЛИЦА 7 КУТУЗОВА УЛИЦА 7 КУТУЗОВА УЛИЦА 7 КУТУЗОВА УЛИЦА 7 КУТУЗОВА УЛИЦА 7 КУТУЗОВА УЛИЦА 7 КУТУЗОВА УЛИЦА 7 КУТУЗОВА УЛИЦА … … 5 ГАГАРИНА УЛИЦА 5 ГАГАРИНА УЛИЦА 5 ГАГАРИНА УЛИЦА 5 ГАГАРИНА УЛИЦА 5 ГАГАРИНА УЛИЦА Рис. 3.97. Тета-соединение таблиц Abonent и Street Разность таблиц. Это соединение позволяет в одной из двух таблиц, имеющих общие ключи, найти строки, которых нет в другой таблице. Например, для выяснения улиц, представленных в таблице Street, отсутствующих в таблице Abonent, можно использовать запрос SELECT S.* 16 FROM Street S LEFT JOIN Abonent A ON S.StreetCD= A.StreetCD WHERE A.StreetCD IS NULL ORDER BY 1; Пересечение таблиц. Позволяет из двух таблиц, имеющих общие ключи, найти значения, которые имеются в обеих таблицах. Например, для выяснения улиц, представленных в таблице Street, имеющихся в таблице Abonent, можно использовать такой запрос: SELECT DISTINCT S.* FROM Street S LEFT JOIN Abonent A ON S.StreetCD= A.StreetCD WHERE A.StreetCD IS NOT NULL ORDER BY 1; Этот запрос решает ту же задачу, что и аналогичные запросы, приведенные в пп. 3.3.1.1, 3.3.1.2 соответственно с неявным и явным внутренним соединением. Деление таблиц. Позволяет в одной из двух таблиц, имеющих общие ключи, найти значения, для которых имеются все соответствия в другой таблице. Пример рассматривается в разд. 3.3.2.6. 3.3.1.4. Соединение таблицы со своей копией В некоторых, довольно часто встречающихся на практике случаях необходимо выбрать данные из таблицы, основываясь на результатах дополнительных выборок из этой же таблицы. Такие выборки называются коррелированными. Для коррелированных выборок многотабличные запросы используют отношения, существующие внутри одной из таблиц (самосоединение) [52]. Чтобы обратиться к одной и той же таблице внутри одного запроса, используются псевдонимы таблиц, определяемые непосредственно после имени таблицы в секции FROM запроса SELECT. Например, чтобы найти все пары абонентов, проживающих на одной и той же улице, можно использовать следующее неявное рефлексивное самосоединение таблицы Abonent: SELECT F.Fio, S.Fio FROM Abonent F, Abonent S WHERE F.StreetCD = S.StreetCD AND F.Fio < S.Fio; В приведенном примере для таблицы Abonent определены 2 псевдонима: F (First) и S (Second). Эти псевдонимы будут существовать, пока выполняется запрос. Запрос ведет себя так, как будто в операции соединения участвуют две таблицы, называемые F и S. Обе они в действительности являются таблицей Abonent, но алиасы позволяют рассматривать ее как две независимые таблицы. Получив две копии таблицы Abonent для работы, SQL выполняет операцию соединения, как для двух разных таблиц: выбирает очередную строку из одного алиаса и соединяет ее с каждой строкой другого алиаса. Дополнительное условие поиска F.Fio < S.Fio предназначено для удаления из НД повторяющихся строк, появляющихся в результате того, что запрос выбирает все комбинации строк с одинаковым кодом улицы (рис. 17 3.98). Такой же результат может быть получен, если использовать следующий запрос на явное внутреннее рефлексивное самосоединение: SELECT F.Fio, S.Fio FROM Abonent F JOIN Abonent S ON F.StreetCD = S.StreetCD WHERE F.Fio < S.Fio; FIO Аксенов С.А. Аксенов С.А. Конюхов В.С. Свирина З.А. Стародубцев Е.В. Маркова В.П. Маркова В.П. Денисова Е.К. Денисова Е.К. Денисова Е.К. Лукашина Р.М. FIO1 Мищенко Е.В. Конюхов В.С. Мищенко Е.В. Тулупова М.И. Шмаков С.В. Стародубцев Е.В. Шмаков С.В. Стародубцев Е.В. Шмаков С.В. Маркова В.П. Шубина Т. П. Рис. 3.98. Результат соединения таблицы со своей копией Примером неявного соединения таблицы со своей копией и другой таблицей может быть запрос, выводящий все пары абонентов, имеющих ремонтные заявки с одной и той же неисправностью газового оборудования: SELECT FailureNM, A.AccountCD, B.AccountCD FROM Request A, Request B, Disrepair D WHERE A.FailureCD = B.FailureCD AND D.FailureCD = A.FailureCD AND A.AccountCD < B.AccountCD; Предположим, что в таблицу Abonent добавлен внешний рекурсивный ключ Head_Account (это можно сделать с помощью запроса ALTER TABLE, использование которого будет рассмотрено позднее). В этом столбце для каждого абонента указан номер лицевого счета управляющего по дому, в котором проживает абонент. Если абонент сам является управляющим, то в столбце Head_Account указывается NULL. Допустим, в таблице Abonent имеются данные, представленные на рис. 3.99. ACCOUNT HEAD_ STRE HOU FLA FIO CD ACCOU ETCD SEN TNO NT O 005488 NULL 3 4 1 Аксенов С.А. 015527 115705 3 1 65 Конюхов В.С. 080047 NULL 8 39 36 Шубина Т. П. PHONE 556893 761699 257842 18 080270 NULL 6 35 6 080613 NULL 8 35 11 115705 NULL 3 1 82 126112 136169 4 7 11 136159 NULL 7 39 1 136160 NULL 4 9 15 136169 NULL 4 7 13 443069 NULL 4 51 55 443690 NULL 7 5 1 Тимошкин а Н.Г. Лукашина Р.М. Мищенко Е.В. Маркова В.П. Свирина З.А. Шмаков С.В. Денисова Е.К. Стародубц ев Е.В. Тулупова М.И. 321002 254417 769975 683301 NULL NULL 680305 683014 214833 Рис. 3.99. Данные таблицы Abonent с рекурсивным внешним ключом Head_Account С помощью внутреннего рекурсивного самосоединения можно создать запрос, в результате выполнения которого выводится список абонентов с указанием ФИО управляющих их домов: SELECT A.Fio "Абонент", B.Fio "Управляющий" FROM Abonent A INNER JOIN Abonent B ON A.Head_Account = B.AccountCD; Этот запрос включает таблицу Abonent и ее копию: из таблицы Abonent (под псевдонимом A) извлекаются ФИО абонентов, а из ее копии (под псевдонимом B) – ФИО управляющих домов. Хотя в таблице Abonent 12 абонентов, но запрос возвращает только 2. Остальные абоненты являются управляющими домов. Чтобы включить всех абонентов в результирующий набор, необходимо использовать внешнее рекурсивное самосоединение: SELECT A.Fio "Абонент", B.Fio "Управляющий" FROM Abonent A LEFT OUTER JOIN Abonent B ON A.Head_Account=B.AccountCD; Для формирования списка всех абонентов и управляющих их домов, если таковые имеются, запрос использует левостороннее внешнее соединение. 19