Запросы со связанными подзапросами

ЛЕКЦИЯ № 10 3.3.2.3. Запросы со связанными подзапросами..1 3.3.2.4. Операторы сравнения с ANY, ALL и SOME 10 3.3.2.5. Предикат SINGULAR............................14 3.3.2.6. Предикат EXISTS...................................16 3.3.2.3. Запросы со связанными подзапросами Вложенный запрос может ссылаться на таблицу, указанную во внешнем (основном) запросе (независимо от его уровня вложенности). Такой вложенный запрос называется зависимым, соотнесенным, коррелированным или связанным изза того, что результат его выполнения зависит от значений, определенных в основном запросе. При этом вложенный запрос выполняется неоднократно, по разу для каждой строки таблицы основного (внешнего) запроса, а не раз, как в случае независимого вложенного запроса. Строка внешнего запроса, для которой внутренний запрос каждый раз будет выполнен, называется текущей строкойкандидатом. Процедура оценки, выполняемой при использовании связанного вложенного запроса, состоит из нескольких шагов. 1. Выбрать очередную строку из таблицы, именованной во внешнем запросе. Это будет текущая строка-кандидат. 2. Сохранить значения из этой строки-кандидата в псевдониме, который задан в секции FROM внешнего запроса. 3. Выполнить вложенный запрос. Везде, где псевдоним, данный для внешнего запроса, найден, использовать значение для текущей строки-кандидата. Использование значения из строки-кандидата внешнего запроса во вложенном запросе называется внешней ссылкой. 4. Если связанный подзапрос используется в секции WHERE или HAVING, то оценить условие поиска внешнего запроса на основе результатов вложенного запроса, выполняемого на шаге 3. Результат вложенного запроса определяет, выбирается ли строка-кандидат для вывода. Если связанный подзапрос используется в секции SELECT, то выводятся столбцы, указанные в списке возвращаемых элементов основного запроса, и результат выполнения вложенного запроса. 5. Повторить процедуру для следующей строки-кандидата основной (внешней) таблицы и т. д., пока все ее строки не будут проверены. Общая структура связанного подзапроса такая же, как и независимого подзапроса (используются те же самые конструкции, и не меняется порядок их следования), однако в секции WHERE или HAVING связанного подзапроса содержится ссылка на столбец таблицы внешнего запроса, и алгоритм выполнения связанного подзапроса совершенно другой. Так как вложенный запрос содержит ссылки на таблицу (таблицы) основного запроса, то вероятность неоднозначных ссылок на имена столбцов достаточно высока. Поэтому, если во вложенном запросе присутствует неполное имя столбца, 1 то сервер БД должен определить, относится ли оно к таблице, указанной в секции FROM самого вложенного запроса, или к таблице, указанной в секции FROM внешнего запроса, содержащего данный вложенный запрос. Возможные неоднозначности при определении столбца устраняются использованием полного имени столбца. Двусмысленность при определении таблицы, используемой для конкретного отбора строк, устраняется с помощью псевдонимов таблиц, указываемых во внешнем и внутреннем запросах. Связанные подзапросы в секции SELECT. Чаще всего в секции SELECT применяются вложенные связанные, а не независимые запросы. Запрос со связанным подзапросом, возвращающий ФИО абонентов и названия улиц, на которых они проживают, имеет вид: SELECT A.Fio, (SELECT S.StreetNM FROM Street S WHERE S.StreetCD = A.StreetCD) AS StreetNM FROM Abonent A; Результат выполнения запроса представлен на рис. 3.121. FIO STREETNM Аксенов С.А. ВОЙКОВ ПЕРЕУЛОК Мищенко Е.В. ВОЙКОВ ПЕРЕУЛОК Конюхов В.С. ВОЙКОВ ПЕРЕУЛОК Тулупова М.И. КУТУЗОВА УЛИЦА Свирина З.А. КУТУЗОВА УЛИЦА Стародубцев Е.В. ТАТАРСКАЯ УЛИЦА Шмаков С.В. ТАТАРСКАЯ УЛИЦА Маркова В.П. ТАТАРСКАЯ УЛИЦА Денисова Е.К. ТАТАРСКАЯ УЛИЦА Лукашина Р.М. МОСКОВСКОЕ ШОССЕ Шубина Т. П. МОСКОВСКОЕ ШОССЕ Тимошкина Н.Г. МОСКОВСКАЯ УЛИЦА Рис. 3.121. Результат выполнения связанного подзапроса в секции SELECT В соответствии с алгоритмом, описанным выше, данный запрос работает в такой последовательности. 1. Внешний запрос выбирает из таблицы Abonent строку с данными об абоненте, проживающем на улице с кодом, равным 3 (первая строка). 2. Сохраняет эту строку как текущую строку-кандидат под псевдонимом A. 3. Выполняет вложенный запрос, просматривающий всю таблицу Street, чтобы найти строку, где значение столбца S.StreetCD такое же, как значение A.StreetCD (3). Из найденной строки таблицы Street извлекается значение столбца StreetNM. 4. Для вывода выбираются значение столбца A.Fio из основного запроса (Аксенов С.А.) и найденное значение столбца S.StreetNM из вложенного запроса (ВОЙКОВ ПЕРЕУЛОК). 5. Повторяются пп.1–4, пока каждая строка таблицы Abonent не будет проверена. 2 Следует отметить, что эту же задачу можно решить с использованием неявного или явного соединения (INNER JOIN) таблиц Abonent и Street. Пусть необходимо вывести для каждого абонента номер его лицевого счета, ФИО и общее количество поданных им заявок (рис. 3.122). Для этого может использоваться запрос SELECT A.AccountCD, A.Fio, (SELECT COUNT (*) FROM Request R WHERE A.AccountCD = R.AccountCD) AS Request_Count FROM Abonent A; ACCOUNTCD FIO REQUEST_COUNT 005488 Аксенов С.А. 3 115705 Мищенко Е.В. 5 015527 Конюхов В.С. 1 443690 Тулупова М.И. 136159 Свирина З.А. 1 443069 Стародубцев Е.В. 2 136160 Шмаков С.В. 2 126112 Маркова В.П. 136169 Денисова Е.К. 2 080613 Лукашина Р.М. 1 080047 Шубина Т. П. 2 080270 Тимошкина Н.Г. 2 Рис. 3.122. Результат соотнесенного вложенного запроса Запрос работает таким образом. 1. Внешний запрос из таблицы Abonent выбирает строку c данными об абоненте, имеющем номер лицевого счета '005488' (первая строка). 2. Сохраняет эту строку как текущую строку-кандидат под псевдонимом A. 3. Выполняется вложенный запрос, просматривающий таблицу Request, чтобы найти все строки, где значение столбца R.AccountCD такое же, как значение A.AccountCD (005488). С помощью агрегатной функции COUNT подсчитывается общее количество таких строк (3). 4. Для вывода выбираются значения столбцов A.AccountCD и A.Fio из основного запроса ('005488', 'Аксенов С.А.') и найденное вложенным запросом количество связанных строк в таблице Request (3). 5. Повторяются пп.1–4, пока каждая строка таблицы Abonent не будет просмотрена. Если во внешнем запросе используется секция GROUP BY, то выражения, указанные в нем, можно использовать внутри подзапросов. С помощью следующего запроса можно получить общие суммы начислений и оплат по услуге с кодом 2 (Электроснабжение) по каждому абоненту, который подавал ремонтные заявки: SELECT R.AccountCD, 3 (SELECT Sum (NachislSum) FROM NachislSumma N WHERE N.AccountCD = R.AccountCD AND N.ServiceCD = 2) AS Nachisl, (SELECT Sum (PaySum) FROM PaySumma P WHERE P.AccountCD = R.AccountCD AND P.ServiceCD = 2) AS Pay FROM Request R GROUP BY R.AccountCD; Здесь в подзапросах вычисляются суммы всех начислений и оплат по услуге с кодом 2 для каждого абонента, отобранного внешним запросом из таблицы Request. Затем возвращенные подзапросами значения выводятся по каждому абоненту в результирующих столбцах Nachisl и Pay. Результат выполнения запроса представлен на рис. 3.123. ACCOUNTCD NACHISL PAY 005488 160,70 154,70 015527 NULL NULL 080047 160,00 160,00 080270 46,00 30,00 080613 56,00 58,50 115705 598,70 598,70 136159 NULL NULL 136160 76,00 76,00 136169 78,70 78,70 443069 118,50 118,50 Рис. 3.123. Результат соотнесенного вложенного запроса Следует отметить, что можно допустить ошибку, если попытаться получить аналогичные данные, используя запрос с соединением таблиц, например, такого вида: SELECT R.AccountCD, Sum (N.NachislSum) AS Nachisl, Sum (P.PaySum) AS Pay FROM Request R LEFT JOIN NachislSumma N ON N.AccountCD = R.AccountCD AND N.ServiceCD = 2 LEFT JOIN PaySumma P ON P.AccountCD = R.AccountCD AND P.ServiceCD = 2 GROUP BY R.AccountCD; Этот запрос выдаст некорректные данные, так как в результате соединения строки с начислениями и оплатами будут дублироваться. Корректные результаты будет выдавать запрос, в котором начисления и оплаты вычисляются отдельно во вложенных связанных подзапросах в секции SELECT (аналогичный запросу, представленному ранее). В запросе можно использовать независимые и связанные подзапросы. Если в предыдущем примере код услуги неизвестен, то его можно определить, используя независимый подзапрос, например: SELECT R.AccountCD, 4 (SELECT Sum (NachislSum) FROM NachislSumma N WHERE N.AccountCD = R.AccountCD AND N.ServiceCD = (SELECT ServiceCD FROM Services WHERE Servicenm = 'Электроснабжение')) AS Nachisl, (SELECT Sum (PaySum) FROM PaySumma P WHERE P.AccountCD = R.AccountCD AND P.ServiceCD = (SELECT ServiceCD FROM Services WHERE Servicenm = 'Электроснабжение')) AS Pay FROM Request R GROUP BY R.AccountCD; Если независимый подзапрос вынести в секцию WITH, то получится более короткий запрос: WITH Service AS (SELECT ServiceCD FROM Services WHERE Servicenm = 'Электроснабжение') SELECT R.AccountCD, (SELECT Sum (NachislSum) FROM NachislSumma N WHERE N.AccountCD = R.AccountCD AND N.ServiceCD = (SELECT ServiceCD FROM Service)) AS Nachisl, (SELECT Sum (PaySum) FROM PaySumma P WHERE P.AccountCD = R.AccountCD AND P.ServiceCD = (SELECT ServiceCD FROM Service)) AS Pay FROM Request R GROUP BY R.AccountCD; Следующий пример демонстрирует добавление соединений в запрос Firebird: SELECT A.Fio, (SELECT S.StreetNM FROM Street S WHERE S.StreetCD= A.StreetCD) ||', д.'||HouseNo||', кв.'||FlatNo AS Adress, R.IncomingDate FROM Abonent A, Request R WHERE R.AccountCD=A.AccountCD ORDER BY 1; В этом запросе со связанным подзапросом соединяются таблицы Abonent и Street для получения ФИО абонентов и их адреса, а также выполняется внешнее соединение с таблицей Request, чтобы возвратить даты подачи ими ремонтных заявок. Связанные подзапросы в секциях WHERE и HAVING. При использовании связанного вложенного запроса в условиях поиска секций WHERE и HAVING он может представлять собой <скалярный_подзапрос>, <подзапрос_столбца> или <табличный_подзапрос>, как и для независимых вложенных запросов. Поскольку запрос связанный, то внутренний запрос выполняется отдельно для каждой строки внешнего запроса (текущая строка-кандидат). Рассмотрим примеры, в которых используются <скалярный_подзапрос> и <подзапрос_столбца>. Подзапросы, представляющие собой <табличный_подзапрос>, будут рассмотрены позднее при изучении предикатов EXISTS и SINGULAR. Например, чтобы вывести все данные об абонентах, которые 17 декабря 2011 г. подали заявки на ремонт газового оборудования (рис. 3.124), можно использовать связанный вложенный запрос: 5 SELECT * FROM Abonent Out WHERE '17.12.2011' IN (SELECT IncomingDate FROM Request Inn WHERE Out.AccountCD = Inn.AccountCD); ACCOUNTCD STREETCD HOUSENO FLATNO FIO PHONE 005488 3 4 1 Аксенов 556893 С.А. 080270 6 35 6 Тимошкина 321002 Н.Г. Рис. 3.124. Результат связанного вложенного запроса в секции WHERE В этом примере Out и Inn – это соответственно псевдонимы таблиц Abonent и Request (могут задаваться произвольно). Так как значение в столбце AccountCD внешнего запроса меняется (при переборе строк), внутренний запрос должен выполняться отдельно для каждой строки внешнего запроса. В этом примере SQL осуществляет следующую процедуру. 1. Выбирает строку c данными об абоненте, имеющем номер лицевого счета '005488' (первая строка), из таблицы Abonent. 2. Сохраняет эту строку как текущую строку-кандидат под псевдонимом Out. 3. Выполняет вложенный запрос, просматривающий всю таблицу Request, чтобы найти строки, где значение столбца Inn.AccountCD – такое же, как значение Out.AccountCD (005488). Затем из каждой такой строки таблицы Request извлекается значение столбца IncomingDate. В результате вложенный запрос, представляющий собой <подзапрос_столбца>, формирует набор значений столбца IncomingDate для текущей строки-кандидата. 4. После получения набора всех значений столбца IncomingDate для AccountCD='005488' анализируется условие поиска основного запроса, чтобы проверить, имеется ли значение 17 декабря 2011 в наборе всех значений столбца IncomingDate. Если это так (а это так), то выбирается строка с номером лицевого счета '005488' для вывода ее из основного запроса. 5. Повторяются пп.1–4 (для второй строки с номером лицевого счета '015527' и т. д.), пока каждая строка таблицы Abonent не будет проверена. Ту же самую задачу в Firebird, например, можно решить, используя естественное соединение таблиц Abonent и Request: SELECT AccountCD, StreetCD, HouseNo, FlatNo, Fio, Phone FROM Abonent NATURAL JOIN Request Inn WHERE Inn.IncomingDate = '17.12.2011'; Результат выполнения будет совпадать с результатом, представленным на рис. 3.124. Однако следует обратить внимание на наличие существенных различий между соединением таблиц и вложенными соотнесенными запросами. Дело в том, что запросы с использованием соединения таблиц формируются СУБД как строки из декартова произведения таблиц, перечисленных в секции FROM. В случае же с вложенным соотнесенным запросом строки из произведения таблиц не вычисляются благодаря использованию механизма строки-кандидата. Вывод в связанном вложенном запросе формируется в секции SELECT внешнего запроса, в то время 6 как соединения могут выводить строки из обеих соединяемых таблиц (при указании символа * в секции SELECT). Но даже если столбцы для вывода при соединении таблиц указаны явно (см. предыдущий пример), то сначала все равно формируется декартово произведение. Каждый SQL-запрос можно оценить с точки зрения используемых ресурсов сервера БД. На практике большинство СУБД подзапросы выполняют более эффективно. Тем не менее, при проектировании комплекса программ с критичными требованиями по быстродействию разработчик должен проанализировать план выполнения SQL-запроса для конкретной СУБД. Тестирование в реальных условиях – единственный надежный способ решить, что лучше работает для конкретных потребностей. Рассмотрим пример сравнения значения, возвращаемого вложенным запросом, с константой. Вывести информацию об исполнителях, назначенных на выполнение 4-х и более ремонтных заявок (рис. 3.125), можно с помощью запроса EXECUTORCD FIO 1 Стародубцев Е.М. 3 Шубин В.Г. 4 Шлюков М.К. Рис. 3.125. Результат сравнения вложенного запроса с константой SELECT * FROM Executor E WHERE 4<= (SELECT COUNT(R.RequestCD) FROM Request R WHERE E.ExecutorCD = R.ExecutorCD); В данном примере связанный подзапрос в условии поиска представляет собой <скалярный_подзапрос>. Он возвращает одно-единственное значение (количество ремонтных заявок) для текущей строки-кандидата, выбранной из таблицы Executor. Если это значение больше или равно 4, то текущая строка-кандидат выбирается для вывода из основного запроса. Эта процедура повторяется, пока каждая строка таблицы Executor не будет проверена. В SQL имеется возможность использовать соотнесенный вложенный запрос, основанный на той же самой таблице, что и основной запрос. Это позволяет использовать соотнесенные вложенные запросы для извлечения сложных форм производной информации. Например, в Firebird вывести для каждого абонента размеры начислений, превышающие среднее значение всех его начислений, можно с помощью следующего запроса (в результирующий НД необходимо включить только первые 8 строк): SELECT FIRST 8 F.NachislSum, (SELECT AVG(D.NachislSum) FROM NachislSumma D WHERE F.AccountCD = D.AccountCD) AS Avg_d, A.AccountCD, A.Fio FROM Abonent A, NachislSumma F WHERE F.NachislSum > (SELECT AVG(S.NachislSum) 7 FROM NachislSumma S WHERE F.AccountCD = S.AccountCD) AND A.AccountCD = F.AccountCD ORDER BY 3; Результат выполнения запроса представлен на рис. 3.126. NACHISLSUM AVG_D ACCOUNTCD FIO 279,80 128,22 005488 Аксенов С.А. 266,70 128,22 005488 Аксенов С.А. 343,36 269,95 015527 Конюхов В.С. 580,10 269,95 015527 Конюхов В.С. 611,30 269,95 015527 Конюхов В.С. 80,00 75,49 080047 Шубина Т. П. 80,00 75,49 080047 Шубина Т. П. 271,60 75,49 080047 Шубина Т. П. Рис. 3.126. Результат использования одной и той же таблицы В этом примере производится одновременная оценка среднего значения для всех строк, удовлетворяющих условию поиска в секции WHERE вложенного связанного запроса, одной и той же таблицы со значениями строки-кандидата. Выбирается первая строка-кандидат из таблицы NachislSumma и сохраняется под псевдонимом F. Выполняется вложенный запрос, просматривающий ту же самую таблицу NachislSumma с самого начала, чтобы найти все строки, где значение столбца S.AccountCD – такое же, как значение F.AccountCD. Затем по всем таким строкам в таблице NachislSumma вложенный запрос (<скалярный_подзапрос>) подсчитывает среднее значение столбца NachislSum. Анализируется условие поиска основного запроса, чтобы проверить, превышает ли значение столбца NachislSum из текущей строки-кандидата среднее значение, вычисленное вложенным запросом. Если это так, то текущая строка-кандидат выбирается для вывода. Таким образом, производятся одновременно и вычисление среднего, и отбор строк, удовлетворяющих условию. 1. Примечание. Запрос, использующий агрегатную функцию в условии поиска основного запроса (данная функция является возвращаемым элементом вложенного запроса), нельзя сформулировать с помощью техники соединения таблиц. Рассмотрим использование соотнесенного вложенного запроса в условии поиска секции HAVING. Условие поиска секции HAVING в подзапросе оценивается для каждой группы из внешнего запроса, а не для каждой строки. Следовательно, вложенный запрос будет выполняться только раз для каждой группы, выведенной внешним запросом, а не для каждой строки (как это было при использовании в секции WHERE). Например, чтобы ACCOUNTCD SUM(N.NACHISLSUM) подсчитать общие суммы 136159 964,99 начислений за услуги для 443069 1106,24 абонентов, чьи ФИО Рис. 3.127. Результат использования начинаются с буквы C (рис. соотнесенного подзапроса 3.127), можно использовать в секции HAVING соотнесенный вложенный 8 запрос: SELECT N.AccountCD, SUM(N.NachislSum) FROM NachislSumma N GROUP BY N.AccountCD HAVING N.AccountCD = (SELECT A.AccountCD FROM Abonent A WHERE A.AccountCD = N.AccountCD AND A.Fio LIKE 'С%'); Последовательность выполнения: основной запрос группирует таблицу NachislSumma по полю AccountCD; затем для каждой группы выполняется связанный вложенный запрос, возвращая единственное значение столбца AccountCD таблицы Abonent (столбец AccountCD содержит уникальные значения). 3.3.2.4. Операторы сравнения с ANY, ALL и SOME Операции сравнения можно расширить до многократного сравнения с использованием кванторов ANY и ALL. В MS SQL Server может также использоваться квантор SOME, эквивалентный квантору ANY. Это расширение используется при сравнении значений определенного столбца со значениями, возвращаемыми вложенным запросом (<подзапрос_столбца>). Квантор существования ANY, указанный после знака любой из операций сравнения, означает, что будет возвращено TRUE, если хотя бы для одного значения из подзапроса результат сравнения истинен. Квантор всеобщности ALL требует, чтобы результат сравнения был истинен для всех значений, возвращаемых подзапросом. Оператор NOT не может использоваться с ANY и ALL. Рассмотрим использование квантора ANY с независимым подзапросом. Например, требуется вывести всю информацию об оплатах абонентами за услугу с кодом, равным 4, за период до 2013 г., размер которых превышает хотя бы одно значение оплат за эту же услугу за 2013 г. Соответствующий запрос будет выглядеть так: SELECT * FROM PaySumma WHERE PaySum > ANY (SELECT PaySum FROM PaySumma WHERE PayYear=2013 AND ServiceCD=4) AND PayYear<2013 AND ServiceCD=4; Результат выполнения запроса представлен на рис. 3.128. В MS SQL Server данный запрос может быть записан так: SELECT * FROM PaySumma WHERE PaySum > SOME (SELECT PaySum FROM PaySumma WHERE PayYear=2013 AND ServiceCD=4) AND PayYear<2013 AND ServiceCD=4; 9 PAYFA ACCO SERVIC PAYS PAYDATE PAYMONTH PAYYEAR CTCD UNTC ECD UM D 65 015527 4 580,10 08.08.2012 7 2012 67 080270 4 444,50 18.04.2011 3 2011 68 080270 4 450,00 14.07.2012 6 2012 70 115705 4 553,85 02.02.2012 1 2012 75 443069 4 466,69 03.06.2012 5 2012 77 443690 4 485,00 05.09.2011 8 2011 Рис. 3.128. Результат использования квантора ANY В этом примере вложенный запрос выполняется только раз, возвращая все значения столбца PaySum, для которых истинно условие PayYear=2013 и ServiceCD=4 (611.30, 460.00…). Затем значения, выбранные подзапросом, последовательно сравниваются со значением столбца PaySum для каждой строки из таблицы PaySumma основного запроса. При первом обнаруженном совпадении сравнение прекращается и соответствующая строка выводится. Условие «> ANY» равносильно утверждению «больше, чем минимальное из существующих», а условие «< ANY» – «меньше, чем максимальное из существующих». Становится очевидным, что эти условия можно записать иначе, используя агрегатные функции MIN и MAX. Предыдущий запрос можно переписать: SELECT * FROM PaySumma WHERE PaySum > (SELECT MIN (PaySum) FROM PaySumma WHERE PayYear=2013 AND ServiceCD=4) AND PayYear<2013 AND ServiceCD=4; Результат выполнения будет совпадать с результатом, представленным на рис. 3.128. Следует отметить, что использование сравнения «= ANY» эквивалентно использованию предиката IN. Рассмотрим использование квантора ALL с независимым подзапросом. Например, требуется вывести всю информацию о ремонтных заявках, дата регистрации которых ранее даты регистрации всех заявок с кодом неисправности газового оборудования, равным 7. Соответствующий запрос: SELECT * FROM Request WHERE IncomingDate < ALL (SELECT IncomingDate FROM Request WHERE FailureCD=7); Результат выполнения запроса представлен на рис. 3.129. 10 REQU ACCO EXEC FAIL INCOMINGDA EXECUTIONDATE ESTC UNTC UTOR UREC TE D D CD D 10 136159 3 12 01.04.2011 03.04.2011 13 005488 5 8 04.09.2010 05.12.2010 15 115705 4 5 20.09.2010 23.09.2010 Рис. 3.129. Результат использования квантора ALL EXECUTED False True True Если требуется вывести всю информацию о ремонтных заявках, дата выполнения которых позднее даты выполнения всех заявок с кодом неисправности, равным 2, то запрос будет выглядеть так: SELECT * FROM Request WHERE ExecutionDate > ALL (SELECT ExecutionDate FROM Request WHERE FailureCD=2); В процессе выполнения данного запроса подзапросом формируется набор значений столбца ExecutionDate, взятых из строк, где FailureCD=2. В результате условие поиска внешнего запроса будет выглядеть следующим образом: ExecutionDate > ALL (24.10.2012, 10.08.2011, 11.10.2011, 14.09.2011). Результат выполнения запроса представлен на рис. 3.130. REQU ACCO EXEC FAIL INCOM EXECUTIONDATE EXECUTED ESTC UNTC UTOR UREC INGDA D D CD D TE 11 136160 1 6 12.01.20 12.01.2013 True 13 14 005488 4 6 04.04.20 13.04.2013 True 13 18 115705 2 3 28.12.20 04.01.2013 True 12 Рис. 3.130. Результат использования квантора ALL В НД не включены строки, где столбец ExecutionDate имеет NULL, так как проверка условия «NULL > ALL(…)» всегда возвращает результат FALSE, а выводятся только те строки, для которых условие поиска истинно. Условие «> ALL» равносильно утверждению «больше, чем максимальное», а условие «< ALL» – «меньше, чем минимальное». Становится очевидным, что такие условия можно записать иначе, используя агрегатные функции MAX и MIN. Таким образом, предыдущий запрос можно переписать: SELECT * FROM Request WHERE ExecutionDate > (SELECT MAX (ExecutionDate) FROM Request WHERE FailureCD=2); Результат выполнения будет таким же, как и в предыдущем примере. 11 Следует отметить, что использование сравнения «<> ALL» эквивалентно использованию предиката NOT IN независимо от того, независимый или связанный подзапрос используется. Рассмотрим использование связанного подзапроса с квантором ALL. Пусть требуется вывести наименования неисправностей газового оборудования, все ремонтные заявки которых зарегистрированы после 1 мая 2011 г. (рис. 3.131). Соответствующий запрос: SELECT D.FailureNM FROM Disrepair D WHERE '01.05.2011' < ALL (SELECT R.IncomingDate FROM Request R WHERE D.FailureCD=R.FailureCD); Так как в этом примере используется связанный подзапрос, то он выполняется для каждой текущей строки из таблицы Disrepair (эта строка сохраняется во внешнем запросе под псевдонимом D). Вложенный запрос просматривает всю таблицу Request, чтобы найти строки, где значение столбца R.FailureCD такое же, как значение столбца D.FailureCD, и формирует набор значений столбца IncomingDate для текущей строки-кандидата. Затем анализируется условие поиска основного запроса, чтобы проверить, меньше ли значение '01-MAY-2013' всех значений столбца IncomingDate, полученных подзапросом. Если это так, то текущая строка-кандидат выбирается для вывода ее из основного запроса. FAILURENM Засорилась водогрейная колонка Не горит АГВ Неисправна печная горелка Плохое поступление газа на горелку плиты Течет из водогрейной колонки Туго поворачивается пробка крана плиты Рис. 3.131. Результат использования связанного подзапроса с ALL При отсутствующих данных следует иметь в виду различие реакции на них кванторов ANY и ALL. Когда правильный подзапрос не возвращает результатов, квантор ALL автоматически принимает значение TRUE, а квантор ANY – FALSE. Например, запрос SELECT * FROM Request WHERE ExecutionDate > ANY (SELECT ExecutionDate FROM Request WHERE FailureCD=4); не возвращает выходных данных (в учебной базе нет заявок с неисправностью с кодом 4), в то время как запрос SELECT * FROM Request WHERE ExecutionDate > ALL (SELECT ExecutionDate FROM Request WHERE FailureCD=4); 12 полностью воспроизводит таблицу Request. 3.3.2.5. Предикат SINGULAR В СУБД Firebird совместно с подзапросами можно использовать предикат SINGULAR, который проверяет, возвращает ли подзапрос в точности одно значение. Если возвращается NULL или более одного значения, то SINGULAR дает FALSE (NOT SINGULAR дает TRUE). Предикат SINGULAR похож на предикат с квантором ALL, за исключением того, что он проверяет наличие одного и только одного соответствующего значения в наборе. С предикатом SINGULAR могут использоваться как независимые, так и соотнесенные подзапросы, однако часто использование независимого подзапроса не имеет логического смысла. Следующим запросом выводятся все данные об услугах, если оплата в размере 40 руб. производилась только единожды: SELECT * FROM Services S WHERE SINGULAR (SELECT PayFactCD FROM PaySumma P WHERE PaySum=40); Такой запрос не имеет практического смысла, так как данные об услугах и о значениях оплат никак не связаны. Поэтому с предикатом SINGULAR в основном используют соотнесенные подзапросы. Например, следующий запрос отыскивает всех абонентов, которые имеют только одну ремонтную заявку (рис. 3.132): SELECT * FROM Abonent A WHERE SINGULAR (SELECT RequestCD FROM Request R WHERE A.AccountCD=R.AccountCD); ACCOUNTC D 015527 STREETC D 3 HOUSENO FLATNO 1 FIO PHONE 65 Конюхов 761699 В.С. 136159 7 39 1 Свирина NULL З.А. 080613 8 35 11 Лукашина 254417 Р.М. Рис. 3.132. Результат использования связанного подзапроса с предикатом SINGULAR Если использовать в предыдущем запросе предикат NOT SINGULAR, то будут выведены абоненты, у которых имеется более одной ремонтной заявки или вообще нет заявок (рис. 3.133): SELECT * FROM Abonent A WHERE NOT SINGULAR (SELECT RequestCD FROM Request R WHERE A.AccountCD=R.AccountCD); 13 ACCOUNTC D 005488 STREETC D 3 HOUSENO FLATNO 4 FIO PHONE 1 Аксенов 556893 С.А. 115705 3 1 82 Мищенко 769975 Е.В. 443690 7 5 1 Тулупова 214833 М.И. 443069 4 51 55 Стародубцев 683014 Е.В. 136160 4 9 15 Шмаков NULL С.В. 126112 4 7 11 Маркова 683301 В.П. 136169 4 7 13 Денисова 680305 Е.К. 080047 8 39 36 Шубина 257842 Т. П. 080270 6 35 6 Тимошкина 321002 Н.Г. Рис. 3.133. Результат использования подзапроса с NOT SINGULAR 3.3.2.6. Предикат EXISTS Условие EXISTS означает проверку существования. В SQL условие поиска с проверкой существования представляется выражением [NOT] EXISTS (<табличный_подзапрос>). Результат условия считается истинным только тогда, когда результат выполнения <табличный_подзапрос> является непустым множеством, т. е. когда существует какая-либо строка в таблице, указанной в секции FROM запроса, удовлетворяющая условию поиска секции WHERE вложенного запроса. Другими словами, EXISTS – это предикат, который возвращает значение, равное TRUE или FALSE, в зависимости от наличия вывода из вложенного запроса. Он может работать автономно в условии поиска или в комбинации с другими логическими выражениями, использующими логические операции AND, OR и NOT. Он берет вложенный запрос как аргумент и оценивает его:  как верный, если тот производит любой вывод;  как неверный, если тот не делает этого. Этим он отличается от других предикатов в условии поиска, где он не может быть неизвестным. Существует возможность с помощью следующего запроса решить, извлекать ли некоторые данные из таблицы Abonent, если хотя бы у одного из абонентов имеются непогашенные заявки на ремонт газового оборудования: SELECT AccountCD, Fio FROM Abonent WHERE EXISTS (SELECT * FROM Request WHERE Executed IS FALSE); 14 В этом примере вложенный запрос выбирает все данные о непогашенных ремонтных заявках. ACCOUNTCD FIO Предикат EXISTS в условии Аксенов С.А. поиска внешнего запроса 005488 «отмечает», что вложенным Мищенко Е.В. запросом был произведен 115705 некоторый вывод, и, так как Конюхов В.С. предикат EXISTS был одним в 015527 условии поиска, делает условие Тулупова М.И. поиска основного запроса верным. 443690 Поскольку в таблице заявок Свирина З.А. имеются (EXISTS) строки с 136159 Executed, принимающим значение Стародубцев Е.В. False, то в НД представлены все 443069 строки таблицы Abonent. Результат Шмаков С.В. выполнения запроса представлен на 136160 рис. 3.134. 126112 Маркова В.П. 136169 Денисова Е.К. 080613 Лукашина Р.М. 080047 Шубина Т. П. 080270 Тимошкина Н.Г. Рис. 3.134. Результат использования условия EXISTS В соотнесенном вложенном запросе предикат EXISTS оценивается отдельно для каждой строки таблицы, имя которой указано во внешнем запросе, т. е. алгоритм выполнения запроса с предикатом EXISTS и связанным подзапросом точно такой же, как и для всех запросов с соотнесенными подзапросами в условии поиска. Например, с помощью следующего запроса можно вывести коды неисправностей, которые возникали у газового оборудования нескольких абонентов: SELECT DISTINCT FailureCD FROM Request Out WHERE EXISTS (SELECT * FROM Request Inn WHERE Inn.FailureCD = Out.FailureCD AND Inn.AccountCD <> Out.AccountCD); Результат выполнения запроса представлен на рис. 3.135. Для каждой строкикандидата внешнего запроса внутренний 15 запрос находит строки, совпадающие со значением в столбце FailureCD и соответствующие разным абонентам (условие AND Inn.AccountCD <> Out.AccountCD). FAILURECD 1 2 6 7 8 12 Рис. 3.135. Результат использования условия EXISTS при соотнесенном вложенном запросе Если любые такие строки найдены внутренним запросом, то это означает, что имеются 2 разных абонента, газовое оборудование которых имело текущую неисправность (неисправность в текущей строке-кандидате из внешнего запроса). Предикат EXISTS возвращает TRUE для текущей строки (результат выполнения подзапроса является непустым множеством), и код неисправности из таблицы, указанной во внешнем запросе, будет выведен. Если DISTINCT не указывать, то каждая из этих неисправностей будет выбираться для каждого абонента, у которого она произошла (у некоторых несколько раз). Использование NOT EXISTS указывает на инверсию результатов запроса. Как предикат EXISTS можно использовать во всех случаях, когда необходимо определить, имеется ли вывод из вложенного запроса. Поэтому можно использовать предикат EXISTS и в соединении таблиц. С помощью следующего запроса можно вывести не только коды, но и названия неисправностей, которые возникали у газового оборудования нескольких абонентов: SELECT DISTINCT D.* FROM Disrepair D, Request Out WHERE EXISTS (SELECT * FROM Request Inn WHERE Inn.FailureCD = Out.FailureCD AND Inn.AccountCD <> Out.AccountCD) AND D.FailureCD = Out. FailureCD; В этом примере внешний запрос – это соединение таблицы Disrepair с таблицей Request. Результат выполнения запроса представлен на рис. 3.136. FAILURECD FAILURENM 1 Засорилась водогрейная колонка 2 Не горит АГВ 6 Плохое поступление газа на горелку плиты 7 Туго поворачивается пробка крана плиты 8 При закрытии краника горелка плиты не гаснет 12 Неизвестна Рис. 3.136. Результат использования условия EXISTS при соединении 16 Следующий запрос иллюстрирует принцип извлечения из одной таблицы значений, которых нет в другой таблице (разность таблиц), с помощью предиката EXISTS: SELECT S.StreetCD FROM Street S WHERE NOT EXISTS (SELECT StreetCD FROM Abonent A WHERE S.StreetCD =A.StreetCD); Он возвращает данные об улицах, на которых не проживают абоненты. Простая перестройка вышеприведенного запроса позволит извлечь улицы, присутствующие и в таблице Street, и в таблице Abonent (пересечение таблиц). С помощью предиката NOT EXISTS можно реализовать стандартную операцию реляционной алгебры – деление. Пусть требуется найти абонентов, которые оплачивали все услуги. Используя принцип построения запросов с отрицанием, можно преобразовать задание таким образом: отобрать тех абонентов из таблицы PaySumma (делимое), для которых не существует тех услуг из таблицы Services (делитель), для которых не существует оплат в таблице PaySumma для этого абонента и этой услуги. С использованием предиката проверки на существование это задание реализуется следующим запросом: SELECT DISTINCT P.AccountCD FROM PaySumma P WHERE NOT EXISTS (SELECT ServiceCD FROM Services S WHERE NOT EXISTS (SELECT ServiceCD FROM PaySumma P1 WHERE P1.AccountCD = P.AccountCD AND P1.ServiceCD = S.ServiceCD)); Результат выполнения запроса представлен на рис. 3.137. ACCOUNTCD 080270 136169 Рис. 3.137. Результат множественного использования условия NOT EXISTS Решение данной задачи, основанное на группировке по номеру лицевого счета с подсчетом уникальных кодов оплаченных им услуг и отборе только тех абонентов, у которых это количество равно общему числу услуг: SELECT AccountCD FROM PaySumma GROUP BY AccountCD HAVING COUNT(DISTINCT ServiceCD) = (SELECT COUNT(ServiceCD) FROM Services); . Для получения такого же результата можно отобрать абонентов, для которых разность между таблицами Services и PaySumma не содержит строк: SELECT DISTINCT P.AccountCD FROM PaySumma P WHERE ServiceCD = ALL (SELECT ServiceCD FROM Services S WHERE ServiceCD NOT IN (SELECT ServiceCD FROM Paysumma P1 WHERE P1.AccountCD=P.AccountCD AND P1.ServiceCD = S.ServiceCD)); . Примечание. Предикат EXISTS нельзя использовать в случае, если вложенный запрос возвращает значение агрегатной функции. 17