Запросы с вложенными запросами

ЛЕКЦИЯ № 9 3.3.2. Запросы с вложенными запросами...................1 3.3.2.1. Виды вложенных запросов.....................1 3.3.2.2. Запросы с независимыми подзапросами4 3.3.2. Запросы с вложенными запросами Часто невозможно решить поставленную задачу путем использования одного запроса. Это особенно актуально, когда при использовании условия поиска в секции WHERE значение, с которым нужно сравнивать, заранее не определено и должно быть вычислено в момент выполнения запроса. В таком случае приходят на помощь законченные запросы SELECT, внедренные в тело другого запроса SELECT. 3.3.2.1. Виды вложенных запросов Вложенный запрос – это запрос, заключенный в круглые скобки и вложенный в секцию SELECT, FROM, WHERE, HAVING или WITH основного (внешнего) запроса SELECT или других запросов, использующих эти секции. Вложенный (внутренний, подзапрос) запрос представляет собой также запрос SELECT, а кодирование его секций подчиняется тем же правилам, что и основного запроса SELECT. Вложенный запрос в своих секциях может содержать другой вложенный запрос и т. д. Внешний запрос SELECT использует результаты выполнения внутреннего запроса для определения содержания окончательного результата всей операции. Подзапрос – это инструмент создания временной таблицы, содержимое которой извлекается и обрабатывается внешним запросом. Условно подзапросы подразделяют на 3 типа, каждый из которых является сужением предыдущего: 1) <табличный_подзапрос> – запрос SELECT, возвращает набор строк и столбцов; 2) <подзапрос_столбца> – запрос SELECT, возвращает значения только одного столбца, но, возможно, в нескольких строках; 3) <скалярный_подзапрос> – запрос SELECT, возвращает значение одного столбца в одной строке. При использовании вложенных запросов в секции SELECT синтаксис возвращаемых элементов имеет вид: <возвращаемый_элемент> :: = { [<таблица>].* | [<таблица>.]столбец |константа | переменная | <выражение> | (<скалярный_подзапрос>) } . При использовании вложенных запросов в секции FROM его синтаксис имеет следующий вид: FROM <производная_таблица1> [,<производная_таблица2> ]…, где <производная_таблица> ::= (<табличный_подзапрос>) [[AS] псевдоним] [(<список_столбцов>)] . 1 В Oracle конструкция (<список_столбцов>) для вложенных запросов не используется (для всех столбцов производной таблицы должно быть указано имя или введен псевдоним), необязательно слово [AS] не указывается перед псевдонимом производной таблицы. В MS SQL Server конструкция [AS] псевдоним является обязательной, т.е. производная таблица обязательно должна иметь псевдоним. При использовании вложенных запросов в секциях WHERE и HAVING изменяется синтаксис некоторых условий поиска. Простое сравнение при использовании вложенного запроса реализуется конструкцией: <значение> <операция_сравнения> { <значение1> | (<скалярный_подзапрос>) | {ANY| ALL} (<подзапрос_столбца>)}. В MS SQL Server синтаксис простого сравнения в условии поиска: <значение> <операция_сравнения> { <значение1> | (<скалярный_подзапрос>) | {ANY| SOME| ALL} (<подзапрос_столбца>)}. Проверка на членство во множестве реализуется конструкцией <значение> [NOT] IN ({<значение1> [ , <значение2> …] | <подзапрос_столбца>} ). В Oracle проверка на членство во множестве может быть реализована в виде (<столбец1>, <столбец2>, [<столбец3>,] […]) IN (<табличный_подзапрос>). Также при использовании вложенных запросов есть возможность делать проверку на существование с помощью предиката EXISTS. Условие поиска с проверкой существования представляется в виде: [NOT] EXISTS (<табличный_подзапрос>). Проверка с помощью предиката EXISTS поддерживается всеми тремя рассматриваемыми в учебнике СУБД. Firebird, помимо проверки на существование, позволяет выполнять проверку на возврат подзапросом единственного значения с помощью предиката SINGULAR. Условие поиска с проверкой на единственное возвращаемое значение представляется в таком виде: SINGULAR (<табличный_подзапрос>). SQL допускает вынесение определений подзапросов из тела основного запроса в секцию WITH. Использование подзапросов в секции WITH имеет вид: WITH [RECURSIVE] имя_производной_таблицы1 [(<список_столбцов>)] AS (<табличный_подзапрос> ) [, имя_производной_таблицы2 [(<список_столбцов>)] AS (<табличный_подзапрос>)]... . Ключевое слово RECURSIVE может использоваться только в Firebird. В любых подзапросах могут использоваться ключевые слова FIRST, SKIP, ROWS. Использование секции ORDER BY в подзапросах запрещено, однако 2 в некоторых случаях в СУБД могут использоваться другие средства для упорядочивания результатов подзапросов. Так, в Oracle использование подзапросов в секции WITH имеет вид: WITH имя_производной_таблицы1 [(<список_столбцов>)] AS (<табличный_подзапрос> ) [SEARCH [DEPTH FIRST] BY <имя_столбца> [ASC|DESC] SET <псевдоним_столбца>] [, имя_производной_таблицы2 [(<список_столбцов>)] AS (<табличный_подзапрос>)] [SEARCH [DEPTH FIRST] BY <имя_столбца> [ASC|DESC] SET <псевдоним_столбца>] ... , где конструкция SEARCH используется для придания порядка строкам результата в вынесенном запросе [55]. Существуют независимые и связанные (зависимые, соотнесенные, коррелированные) вложенные запросы. В секциях SELECT, WHERE и HAVING могут использоваться и независимые, и связанные вложенные запросы, а в секции FROM только независимые вложенные запросы. В секцию WHERE (или HAVING) как независимые, так и связанные вложенные запросы включаются с помощью предикатов IN, EXISTS или одной из операций сравнения (=, <>, !=, <, >, <=, !<, >=, !>). Следует отметить, что выражения, содержащие подзапрос в секциях WHERE или HAVING, используются наиболее часто. Независимым вложенным запросом называется такой, результат которого не зависит от внешнего запроса. Данные из таблиц, указанных в секциях FROM внешнего запроса и подзапроса, извлекаются независимо друг от друга, вследствие чего необязательно вводить псевдонимы для этих таблиц или указывать полные имена столбцов. Независимые вложенные запросы обрабатываются системой «снизу вверх». Первым обрабатывается вложенный запрос самого нижнего уровня. Множество значений, полученное в результате его выполнения, используется при реализации запроса более высокого уровня и т. д. Связанным вложенным запросом называется такой, результат которого зависит от результата внешнего запроса. Подзапрос является связанным, когда в нем (в секциях WHERE, HAVING) указан столбец таблицы внешнего запроса. Такое обращение к столбцам внешнего запроса называется внешней ссылкой. Если быть точнее, внешняя ссылка – это имя столбца одной из таблиц, указанных в секции FROM внешнего запроса, но не входящего ни в одну из таблиц секции FROM подзапроса. В связанных подзапросах следует указывать полные имена столбцов, причем если во внешнем и вложенном запросах используется одна и та же таблица, то для столбцов должны быть заданы псевдонимы. Запросы со связанными вложенными запросами обрабатываются в обратном порядке («сверху вниз»), т. е. сначала выбирается первая строка рабочей таблицы, сформированная основным запросом. Затем из нее выбираются значения тех столбцов, которые используются в подзапросе (подзапросах). Если эти значения удовлетворяют условиям вложенного запроса, то выбранная строка включается в результат. 3 После этого во внешнем запросе выбирается вторая строка и т. д., пока в результат не будут включены все строки, удовлетворяющие подзапросу (последовательности подзапросов). Правила использования подзапросов. • Подзапрос должен быть заключен в скобки. • Подзапрос должен находиться справа от оператора сравнения. • Подзапросы не могут содержать секцию ORDER BY. В запросе SELECT может быть только одна секция ORDER BY. Если секция ORDER BY используется, она должна быть последней в главном запросе SELECT. • В подзапросах используются операторы сравнения двух типов: однострочные и многострочные. 3.3.2.2. Запросы с независимыми подзапросами Независимые подзапросы в секции SELECT При использовании независимого подзапроса возвращенный им результат вставляется во все строки, формируемые внешним запросом. В секции SELECT может использоваться только <скалярный_подзапрос>, т. е. подзапрос, который является выражением над столбцом, возвращающим только одно значение. Использование подзапроса таким образом позволяет «выдернуть» отдельное значение из некоторой другой таблицы или запроса, чтобы включить его в вывод для своего запроса [56]. Например, необходимо по каждому абоненту вывести среднее значение его оплат, а также среднее значение начислений по всем абонентам (рис. 3.100). Запрос может выглядеть следующим образом: SELECT AccountCD, AVG (PaySum) AS AVG_Pay, (SELECT AVG (NachislSum) FROM NachislSumma ) AS AVG_All_Nachisl FROM PaySumma GROUP BY AccountCD; ACCOUNTCD AVG_PAY AVG_ALL_NACHISL 005488 126,61 166,02 015527 271,06 166,02 080047 75,49 166,02 080270 230,07 166,02 080613 108,68 166,02 115705 144,64 166,02 126112 164,00 166,02 136159 193,22 166,02 136160 91,66 166,02 136169 173,05 166,02 443069 184,37 166,02 443690 204,86 166,02 Рис. 3.100. Результат выполнения вложенного запроса На рис. 3.100 представлен результат, полученный в Firebird. При выполнении запроса в Oracle или MS SQL Server будет получен результат с большей точностью числовых значений (рис. 3.101). 4 ACCOUNTCD AVG_PAY AVG_ALL_NACHISL 005488 126.616666 166.022179 015527 271.066666 166.022179 080047 75.497142 166.022179 080270 230.075000 166.022179 080613 108.688750 166.022179 115705 144.644444 166.022179 126112 164.000000 166.022179 136159 193.222000 166.022179 136160 91.666666 166.022179 136169 173.058750 166.022179 443069 184.373333 166.022179 443690 204.867500 166.022179 Рис. 3.101. Результат выполнения вложенного запроса в MS SQL Server Как следует из этого примера, связь между значением, возвращаемым независимым вложенным запросом (среднее значение всех начисленных сумм по всем абонентам), и значениями внешнего запроса фактически отсутствует. Рассмотрим более практичный пример использования независимых подзапросов в секции SELECT. Пусть необходимо вывести год и число абонентов, которым не производились за него начисления. Запрос будет таким: SELECT NachislYear AS "Год", (SELECT COUNT(AccountCD) FROM Abonent) - COUNT(DISTINCT AccountCD) AS "Число абонентов" FROM NachislSumma GROUP BY NachislYear ORDER BY NachislYear; Независимые подзапросы в секции SELECT используются достаточно редко. Область применения связанных вложенных запросов в секции SELECT намного шире и будет рассмотрена далее. Независимые подзапросы в секции FROM. В соответствии со стандартом SQL:2003 в секции FROM могут быть определены не только базовые таблицы, а и производные таблицы, возвращаемые вложенным запросом (<табличный_подзапрос>). Производные таблицы могут быть вложенными друг в друга и могут быть включены в соединение (неявное или явное) как обычные таблицы или представления. Следует учесть:  для определения производных таблиц можно использовать только независимые подзапросы;  каждый столбец в производной таблице должен иметь имя. Если в качестве возвращаемого элемента в производной таблице используется, например, константа или выражение, то для такого столбца должен быть 5 введен псевдоним или, если это поддерживает используемая СУБД, должны указываться имена столбцов с помощью конструкции: [[AS] псевдоним] (<список_столбцов>);  если используется конструкция [AS] псевдоним (<список_столбцов>), то количество столбцов в скобках должно быть таким же, как и количество столбцов в секции SELECT подзапроса. Рассмотрим простейшее определение производной таблицы в Firebird с помощью запроса: SELECT * FROM (SELECT AccountCD, Fio, Phone FROM Abonent) AS A (Id, Full_Name, Tel);, где A – псевдоним производной таблицы, а Id, Full_Name, Tel – список ее столбцов. Данный пример иллюстрирует правила использования подзапроса в секции FROM, но применять его нецелесообразно, так как выборку тех же значений можно получить с помощью такого обычного запроса SELECT AccountCD, Fio, Phone FROM Abonent; В условии поиска секции WHERE нельзя использовать псевдонимы возвращаемых элементов. Вместе с тем использование подзапроса, назначающего псевдонимы, дает возможность обращаться к возвращаемым столбцам по псевдонимам во внешнем запросе. Например, выполнив в Firebird запрос SELECT * FROM (SELECT Fio AS Full_Name FROM Abonent) WHERE Full_Name LIKE 'С%';, получим результат, совпадающий с результатом, представленным на рис. 3.12, однако возвращаемый столбец имеет имя Full_Name. В MS SQL Server данный запрос не выполнится, для успешного выполнения необходимо задать псевдоним для подзапроса: SELECT * FROM (SELECT Fio AS Full_Name FROM Abonent) A WHERE A.Full_Name LIKE 'С%'; В Oracle использование независимого подзапроса в секции FROM может быть полезным для получения из результатов запроса строки (строк) с определенным порядковым номером. Например, для получения пятой строки из выборки по таблице Abonent можно использовать запрос такого вида: SELECT * FROM (SELECT A.*, ROWNUM RNum FROM ABONENT A WHERE ROWNUM <= 5) WHERE RNum >= 5; В данном примере сначала подзапросом с помощью псевдостолбца ROWNUM извлекаются первые 5 строк из таблицы Abonent с выводом их порядковых номеров в столбец RNum, затем основным запросом отбирается необходимая строка по значению в столбце RNum. Рассмотрим варианты, когда использование подзапроса в секции FROM может оказаться более полезным. В секции FROM могут быть определены две и более производные таблицы. Например, требуется вывести среднее количество ремонтных заявок, приходящихся на одного абонента. Для этого нужно определить общее количество ремонтных заявок, общее число абонентов и поделить 6 полученное количество заявок на число абонентов (рис. 3.102). Запрос может выглядеть таким образом: SELECT (CAST (R.Req_Count AS NUMERIC(5,2)) / A.Ab_Count) AS Req_On_Ab FROM (SELECT COUNT (*) FROM Abonent) AS A (Ab_Count), (SELECT COUNT (*) FROM Request) AS R (Req_Count); Результат выполнения запроса представлен рис. 3.102. на REQ_ON_AB 1,75 Рис. 3.102. Результат выполнения вложенного запроса в секции FROM Приведенный запрос корректен для Firebird и MS SQL Server, в Oracle запрос будет выглядеть так: SELECT (CAST (R.Req_Count AS NUMERIC(5,2)) / A.Ab_Count) AS Req_On_Ab FROM (SELECT COUNT (*) Ab_Count FROM Abonent) A, (SELECT COUNT (*) Req_Count FROM Request) R; В настоящем примере используется функция CAST для преобразования вычисленного целого значения (Req_Count) в десятичный формат NUMERIC(5,2). Если не сделать такое преобразование, то при делении целого числа на целое (R.Req_Count/A.Ab_Count) произойдет округление результата до целого в меньшую сторону (будет выведен результат Req_On_Ab=1). Следует учесть, что предыдущий запрос выдаст ошибку деления на ноль, если таблица Abonent будет пустой. Чтобы исключить такую ошибку, можно несколько изменить предыдущий запрос. Например, для Firebird или MS SQL Server: SELECT IIF(A.Ab_Count > 0, (CAST (R.Req_Count AS NUMERIC(5,2))/ A.Ab_Count), 'Нет ни одного абонента') AS Req_On_Ab FROM (SELECT COUNT (*) FROM Abonent) AS A (Ab_Count), (SELECT COUNT (*) FROM Request) AS R (Req_Count); В данном случае деление производится, только если количество абонентов больше нуля, в противном случае выводится 'Нет ни одного абонента'. Рассмотрим более сложный пример вложенного запроса, когда производная таблица в секции FROM получается путем соединения двух таблиц. Пусть требуется вывести информацию о том, сколько абонентов подали одинаковое количество ремонтных заявок, и число этих заявок (рис. 3.103). INFO 3 абонентов подали 1 заявки 5 абонентов подали 2 заявки 1 абонентов подали 3 заявки 1 абонентов подали 5 заявки Рис. 3.103. Результат выполнения 7 запроса с подзапросом в секции FROM с соединением Запрос на вывод требуемой информации в Firebird: SELECT (Count (*)||' абонентов подали '||Req_Count ||' заявки') AS Info FROM (SELECT A.AccountCD, Count (*) FROM Abonent A JOIN Request R ON A.AccountCD = R.AccountCD GROUP BY AccountCD) AS Ar (Abonent_ID, Req_Count) GROUP BY Ar.Req_Count; Аналогичный запрос в Oracle: SELECT (Count (*)||' абонентов подали '||Ar.Req_Count||' заявки') AS Info FROM (SELECT A1.AccountCD, Count (*) Req_Count FROM Abonent A1 JOIN Request R ON A1.AccountCD = R.AccountCD GROUP BY A1.AccountCD) Ar GROUP BY Ar.Req_Count; Такой же запрос в MS SQL Server: SELECT (Cast(Count (*) AS varchar) +' абонентов подали ' + CAST(Req_Count AS varchar) + ' заявки') AS Info FROM (SELECT A.AccountCD, Count (*) FROM Abonent A JOIN Request R ON A.AccountCD = R.AccountCD GROUP BY A.AccountCD) AS Ar (Abonent_ID, Req_Count) GROUP BY Ar.Req_Count; В этом запросе сначала формируется производная таблица Ar, которая содержит информацию о номере лицевого счета каждого абонента (AccountCD) и количестве поданных им заявок на ремонт (Req_Count). Вид производной таблицы Ar представлен на рис. 3.104. Затем внешний запрос группирует строки из этой ACCOUNTCD REQ_COUNT 005488 3 015527 1 080047 2 080270 2 080613 1 115705 5 136159 1 136160 2 136169 2 443069 2 Рис. 3.104. Вид производной 8 таблицы по количеству таблицы поданных заявок, подсчитывает количество строк в каждой группе и выводит данные в 1 столбец. Таким образом, получено, что одну ремонтную заявку подали 3 абонента, две заявки – 5 абонентов и т. д. (см. рис. 3.103). В Firebird допускается определение производной таблицы без использования конструкции [[AS] псевдоним] (<список_столбцов>). Предыдущий пример может быть записан таким образом: SELECT (Count (*)||' абонентов подали '||Req_Count ||' заявки') AS Info FROM (SELECT A.AccountCD, Count (*) AS Req_Count FROM Abonent A JOIN Request R ON A.AccountCD = R.AccountCD GROUP BY AccountCD) GROUP BY Req_Count; Результат выполнения запроса будет совпадать с результатом, представленным на рис. 3.103. Приведем пример запроса в Firebird, в котором осуществляется явное соединение с производной таблицей, сформированной подзапросом: SELECT Executor.Fio, Stat.Total FROM Executor INNER JOIN (SELECT COUNT(RequestCD) AS Total, ExecutorCD FROM Request GROUP BY ExecutorCD) AS Stat USING(ExecutorCD) WHERE Stat.Total > 3; Результатом этого запроса является список ФИО исполнителей с количеством принятых к исполнению заявок, большим 3 (рис. 3.105). FIO TOTAL Стародубцев Е.М. 7 Шубин В.Г. 4 Шлюков М.К. 4 Рис. 3.105. Результат запроса с явным соединением с подзапросом Подзапросы могут быть вложенными друг в друга. Так, следующий запрос Firebird рассчитывает среднее значение оплат, произведенных в каждом году, и находит разницу между этим средним и общей суммой оплат в каждом году (рис. 3.106): SELECT EXTRACT(Year FROM PayDate) AS "Год", AllAvg - SUM(PaySum) AS "Разность" FROM (SELECT AVG(YearSum) FROM (SELECT SUM(PaySum) FROM PaySumma 9 GROUP BY EXTRACT(Year FROM PayDate)) AS Yp(YearSum)) AS Aa(AllAvg), PaySumma GROUP BY 1, AllAvg ORDER BY 1; Год Разность 201 2457,03 201 368,11 1 201 -1355,7 2 201 -1469,46 3 Рис. 3.106. Результат запроса с вложенными подзапросами в FROM Аналогичный запрос в Oracle будет выглядеть так: SELECT EXTRACT(Year FROM P.PayDate) AS "Год", Aa.AllAvg - SUM(P.PaySum) AS "Разность" FROM (SELECT AVG(YearSum) AllAvg FROM (SELECT SUM(PaySum) YearSum FROM PaySumma GROUP BY EXTRACT(Year FROM PayDate)) Yp) Aa, PAYSUMMA P GROUP BY EXTRACT(Year FROM P.PayDate), Aa.AllAvg ORDER BY EXTRACT(Year FROM P.PayDate); В MS SQL Server запрос должен быть записан следующим образом: SELECT Year(PayDate) AS "Год", AllAvg - SUM(PaySum) AS "Разность" FROM (SELECT AVG(YearSum) FROM (SELECT SUM(PaySum) FROM PaySumma GROUP BY Year(PayDate)) AS Yp(YearSum)) AS Aa(AllAvg), PaySumma GROUP BY Year(PayDate), AllAvg ORDER BY Year(PayDate); Независимые подзапросы в секциях WHERE и HAVING. Наиболее часто вложенные запросы используются в условиях поиска секций WHERE и HAVING для усложненной фильтрации данных. В зависимости от того, в каком условии поиска используется подзапрос, он может представлять собой <скалярный_подзапрос>, <подзапрос_столбца> или <табличный_подзапрос>. При простом сравнении используется <скалярный_подзапрос> либо <подзапрос_столбца>, если перед ним указан квантор ANY или ALL (или SOME в MS SQL Server). Также <подзапрос_столбца> используется при 10 проверке на членство во множестве. В условиях поиска с предикатом EXISTS или с предикатом SINGULAR используется <табличный_подзапрос>. Использование подзапросов с кванторами ANY, ALL, SOME и предикатами EXISTS, SINGULAR будет рассмотрено позднее после изучения независимых и связанных подзапросов. Рассмотрим использование независимых подзапросов в условиях поиска секций WHERE и HAVING. Предположим, что известно ФИО абонента Шмаков С.В., но неизвестно значение его номера лицевого счета. Необходимо извлечь из таблицы NachislSumma все данные о начислениях абоненту с ФИО Шмаков С.В. Ниже приведен независимый вложенный запрос, извлекающий требуемый результат из таблицы NachislSumma: SELECT * FROM NachislSumma WHERE AccountCD = (SELECT AccountCD FROM Abonent WHERE Fio = 'Шмаков С.В.') ORDER BY NachislFactCD; Результат выполнения запроса представлен на рис. 3.107. NACHISLFACT ACCO SERVIC NACHI NACHIS NACHISLYEAR CD UNTC ECD SLSU LMONT D M H 1 136160 2 56,00 1 2013 6 136160 1 18,30 1 2012 13 136160 2 20,00 5 2011 50 136160 1 18,30 12 2010 61 136160 3 238,80 3 2010 62 136160 3 237,38 3 2011 Рис. 3.107. Результат выполнения вложенного запроса в секции WHERE В данном примере подзапрос в условии поиска представляет собой <скалярный_подзапрос>. Он выполняется первым и возвращает единственное значение столбца AccountCD = 136160. Оно помещается в условие поиска основного (внешнего) запроса так, что условие поиска будет выглядеть следующим образом: WHERE AccountCD = 136160. 1. Примечание. В секции SELECT вложенный запрос должен выбрать одно и только одно значение, а тип данных этого значения должен совпадать с типом того значения, с которым он будет сравниваться в основном запросе. Запрос предыдущего примера вернет во всех столбцах НД NULL, если в таблице Abonent не будет абонента с ФИО Шмаков С.В. Вложенные запросы, которые не производят никакого вывода (нулевой вывод), вынуждают рассматривать результат не как верный, не как неверный, а как неизвестный. Однако неизвестный результат имеет тот же самый эффект, что и неверный: никакие строки не выбираются основным запросом. 11 Запрос предыдущего примера не выполнится, если в таблице Abonent будет более одного абонента с ФИО Шмаков С.В., так как вложенный запрос вернет более одного значения. Следующий запрос, который должен найти абонентов, имеющих погашенные заявки на ремонт газового оборудования, не может быть выполнен из-за ошибки «multiple rows in singleton select» (многочисленные строки в единичном запросе SELECT): SELECT * FROM Abonent WHERE AccountCD = (SELECT AccountCD FROM Request WHERE Executed GROUP BY AccountCD); Это происходит потому, что вложенный запрос возвращает более одного значения. Если в БД будет одно значение или его вообще не будет, то запрос выполнится, а если несколько, то возникнет ошибка. Для обработки множества значений, возвращаемых вложенным запросом, следует использовать специальный предикат IN. Тогда приведенный выше запрос может быть правильно реализован так: SELECT * FROM Abonent WHERE AccountCD IN (SELECT AccountCD FROM Request WHERE Executed GROUP BY AccountCD); В данном примере подзапрос в условии поиска представляет собой <подзапрос_столбца>, возвращающий различные значения столбца AccountCD (005488, 080047, 080270, 080613 и т. д.), где Executed принимает значение True. Затем выполняется внешний запрос, выводящий те строки из таблицы Abonent, для которых верно условие поиска «AccountCD IN (005488, 080047, 080270, 080613 и т. д.)». Таким образом, если используется предикат IN, то вложенный запрос выполняется только раз и формирует множество значений, используемых основным запросом. В любой ситуации, где используется реляционная операция сравнения (=), разрешается использовать IN. В отличие от запроса со знаком равенства запрос с предикатом IN не потерпит неудачу, если больше чем одно значение выбрано вложенным запросом. Результат выполнения запроса представлен на рис. 3.108. ACCOO STREE HOUS FLAT FIO PHONE UNTD TCD ENO NO 005488 3 4 1 Аксенов С.А. 556893 115705 3 1 82 Мищенко Е.В. 769975 443069 4 51 55 Стародубцев Е.В. 683014 136160 4 9 15 Шмаков С.В. NULL 136169 4 7 13 Денисова Е.К. 680305 080613 8 35 11 Лукашина Р.М. 254417 080047 8 39 36 Шубина Т. П. 257842 080270 6 35 6 Тимошкина Н.Г. 321002 Рис. 3.108. Результат выполнения вложенного запроса с предикатом IN 12 С помощью предыдущего запроса получены данные об абонентах, которые имеют погашенные ремонтные заявки, но этот запрос не дает информации об абонентах, все заявки которых погашены. Чтобы получить данные об абонентах, все заявки которых погашены, предыдущий запрос можно модифицировать: SELECT * FROM Abonent WHERE AccountCD IN (SELECT AccountCD FROM Request WHERE Executed GROUP BY AccountCD) AND AccountCD NOT IN (SELECT AccountCD FROM Request WHERE NOT Executed GROUP BY AccountCD); Результат выполнения этого запроса представлен на рис. 3.109. Абонентов с номерами лицевых счетов '115705' и '080270' нет в результирующей таблице, так как у этих абонентов имеются непогашенные заявки. Пример запроса демонстрирует принцип построения запросов с отрицанием, заключающийся в том, что настоящее отрицание требует двух проходов: чтобы найти, «кто не», сначала надо найти, «кто да», и затем избавиться от них. ACCOUNTC D 005488 STREETC D 3 HOUSEN O 4 FLATN O 1 FIO PHON E 556893 Аксенов С.А. 443069 4 51 55 Стародубце 683014 в Е.В. 136160 4 9 15 Шмаков NULL С.В. 136169 4 7 13 Денисова 680305 Е.К. 080613 8 35 11 Лукашина 254417 Р.М. 080047 8 39 36 Шубина 257842 Т. П. Рис. 3.109. Результат выполнения запроса с двумя вложенными запросами Следующий запрос иллюстрирует принцип извлечения из одной таблицы значений, которых нет в другой таблице (разность таблиц), с помощью подзапроса: SELECT S.StreetCD FROM Street S WHERE StreetCD NOT IN (SELECT StreetCD FROM Abonent); Он возвращает данные об улицах, на которых не проживают абоненты (рис. 3.110). 13 STREETCD 5 1 2 Рис. 3.110. Результат вычисления разности таблиц Следует отметить, что СУБД Oracle для реализации разности таблиц поддерживает специальный оператор MINUS (рассмотрен далее в п. 3.3.3). Во вложенном запросе возможно использование той же таблицы, что и в основном запросе. Например, если требуется вывести все данные об абоненте с ФИО Аксенов С.А. и обо всех других абонентах, которые проживают с ним на одной улице, то запрос может иметь следующий вид: SELECT * FROM Abonent WHERE StreetCD = (SELECT StreetCD FROM Abonent WHERE Fio='Аксенов С.А.'); Результат выполнения запроса представлен на рис. 3.111. ACCOUN STREE HOUSE FLAT FIO PHONE TCD TCD NO NO 005488 3 4 1 Аксенов С.А. 556893 115705 3 1 82 Мищенко Е.В. 769975 015527 3 1 65 Конюхов В.С. 761699 Рис. 3.111. Результат использования одной и той же таблицы В данном примере подзапрос выполняется отдельно от внешнего запроса, так как является независимым. Сначала будет выполнен вложенный запрос, который выберет значение StreetCD из таблицы Abonent для абонента с ФИО Аксенов С.А. Затем основной запрос выберет из той же таблицы Abonent строки со значением столбца StreetCD, равным значению, выбранному вложенным запросом. При построении запросов, в том числе запросов с подзапросами, следует помнить, что NULL никогда не бывает равным или не равным ни одному значению, даже самому себе. Например, следующий запрос вернет информацию только о выполненных заявках (строки, где ExecutionDate равно NULL, не попадут в результат): SELECT * FROM Request WHERE ExecutionDate IN (SELECT ExecutionDate FROM Request); Более того, при наличии неопределенных значений можно получить совершенно непредсказуемые результаты. Например, при попытке определить исполнителей ремонтных заявок, не назначенных ни на одну заявку, путем нахождения разности между таблицами Executor и Request запросом SELECT ExecutorCD FROM Executor WHERE ExecutorCD NOT IN (SELECT ExecutorCD FROM Request); 14 будет получен пустой результат. Пустой результат является следствием того, что в таблице Request есть строка, содержащая NULL в столбце ExecutorCD. Для предотвращения подобных ошибок необходимо выполнить дополнительную проверку: SELECT ExecutorCD FROM Executor WHERE ExecutorCD NOT IN (SELECT ExecutorCD FROM Request WHERE ExecutorCD IS NOT NULL); 2. Примечание. Результатом выполнения двух последних запросов к учебной БД будет пустой результат из-за отсутствия в таблице Executor исполнителя, не назначенного на исполнение ни одной заявки. Однако часто требуется проводить вычисления над данными столбца, который может содержать неопределенные значения. Например, требуется найти в таблице Abonent всех абонентов, первая цифра номера телефона которых меньше первой цифры номера телефона абонента с ФИО Cтародубцев Е.В. В НД должны быть включены и абоненты, у которых нет телефона. Для решения этой задачи в следующем запросе с независимым подзапросом в секции WHERE используется функция COALESCE для преобразования NULL в действительное значение, которое может использоваться в обычных вычислениях и сравнениях: SELECT Fio, Phone FROM Abonent WHERE COALESCE(LEFT(Phone,1),0) < (SELECT LEFT(Phone,1) FROM Abonent WHERE Fio='Стародубцев Е.В.'); Результат выполнения запроса представлен на рис. 3.112. FIO PHONE Аксенов С.А. 556893 Тулупова М.И. 214833 Свирина З.А. Шмаков С.В. Лукашина Р.М. 254417 Шубина Т.П. 257842 Тимошкина Н.Г. 321002 Рис. 3.112. Результат обработки столбца с NULL значениями В Oracle аналогичный запрос: SELECT Fio, Phone FROM Abonent WHERE COALESCE(TO_NUMBER(SUBSTR(Phone,1)),0) < (SELECT TO_NUMBER(SUBSTR(Phone,1)) FROM Abonent WHERE Fio='Стародубцев Е.В.'); Во вложенном запросе можно использовать агрегатные функции. Допустим, необходимо вывести номера лицевых счетов абонентов и 15 значения их начислений за 2013 год, превышающие среднее значение начислений по всем абонентам за этот год. Запрос будет иметь следующий вид: SELECT AccountCD, NachislSum, NachislMonth, NachislYear, (SELECT AVG(NachislSum) FROM NachislSumma GROUP BY NachislYear HAVING NachislYear=2013) AS Avg_All FROM NachislSumma WHERE NachislSum > (SELECT AVG(NachislSum) FROM NachislSumma GROUP BY NachislYear HAVING NachislYear=2013) AND NachislYear=2013 ORDER BY 1; В этом примере вложенный запрос выполняется только раз, возвращая среднее значение столбца NachislSum за 2013 г. Затем это значение последовательно сравнивается с каждой строкой, выбираемой из таблицы NachislSumma. Результат выполнения запроса представлен на рис. 3.113. ACCOUNTC NACHISLSU NACHISLMON NACHISLYE AVG_AL D M TH AR L 005488 266,70 2 2013 196,91 015527 343,36 11 2013 196,91 015527 611,30 10 2013 196,91 080047 271,60 2 2013 196,91 080270 278,25 11 2013 196,91 080270 454,60 4 2013 196,91 080613 258,80 2 2013 196,91 080613 239,33 5 2013 196,91 136169 528,44 10 2013 196,91 443069 444,45 10 2013 196,91 443090 290,33 3 2013 196,91 Рис. 3.113. Результат использования агрегатной функции в подзапросе Рассмотрим еще несколько примеров использования агрегатных функций в подзапросе секции WHERE. Для вывода информации о погашенных ремонтных заявках с наиболее поздней датой поступления (рис. 3.114) в Firebird можно использовать запрос SELECT * FROM Request WHERE IncomingDate = (SELECT MAX (IncomingDate) FROM Request WHERE Executed); REQUE ACCOU EXECUT FAILU INCOMIN EXECUTI EXECUTED STCD NTCD ORCD RECD GDATE ONDATE 14 005488 4 6 04.04.2013 13.04.2013 True 16 Рис. 3.114. Результат использования агрегатной функции Для вывода ФИО абонентов с информацией об оплате с наибольшим значением можно использовать такой запрос (результат на рис. 3.115): SELECT Abonent.Fio, PaySumma.* FROM PaySumma, Abonent WHERE PaySum = (SELECT MAX(PaySum) FROM PaySumma) AND Abonent.AccountCD = PaySumma.AccountCD; FIO Конюхов В.С. PAYFA ACCOU SERVI PAYS PAYDA PAYMO PAYYEAR CTCD NTCD CECD UM TE NTH 66 015527 4 611,30 03.11.20 10 2013 13 Рис. 3.115. Результат выполнения запроса В секции WHERE, как и в секции FROM, подзапросы могут быть вложенными друг в друга. Cледующий запрос подсчитывает общую сумму значений оплат и выводит название услуги с максимальной суммой (рис. 3.116): SELECT ServiceName, TotalSum FROM (SELECT S.ServiceNM, SUM(P.PAYSUM) FROM PaySumma P INNER JOIN Services S ON P. ServiceCD = S.ServiceCD GROUP BY S.ServiceNM) AS TNS(ServiceName, TotalSum) WHERE TNS. TotalSum = (SELECT MAX(TS.TotalSum) FROM (SELECT SUM(PaySum) FROM PaySumma GROUP BY ServiceCD) AS TS(TotalSum)); SERVICENAME TOTALSUM Водоснабжение 6229,21 Рис. 3.116. Результат запроса с вложенными подзапросами в WHERE Приведенный запрос корректен для Firebird и MS SQL Server, в Oracle аналогичный запрос будет выглядеть так: SELECT ServiceName, TotalSum FROM (SELECT S.SERVICENM AS ServiceName, SUM(P.PAYSUM) AS TotalSum FROM PaySumma P INNER JOIN Services S ON P. ServiceCD = S.ServiceCD GROUP BY S.ServiceNM) TNS WHERE TNS. TotalSum = (SELECT MAX(TS.TotalSum) FROM (SELECT SUM(PaySum) AS TotalSum FROM PaySumma GROUP BY ServiceCD) TS); Независимые запросы в секции HAVING. Они могут использовать свои собственные агрегатные функции (если эти функции не возвращают 17 многочисленных значений). Также в подзапросе, включенном в условие поиска секции HAVING внешнего запроса, могут использоваться свои собственные секции GROUP BY и HAVING. Следует помнить, что аргументы, указанные в HAVING, должны присутствовать в качестве аргументов и в GROUP BY. Например, для подсчета числа абонентов с максимальным значением оплаты за 2012 г. (рис. 3.117) можно использовать запрос SELECT COUNT(DISTINCT AccountCD), PaySum FROM PaySumma GROUP BY PaySum HAVING PaySum = (SELECT MAX(PaySum) FROM PaySumma WHERE PayYear = 2012); COUNT PAYSUM 1 580,10 Рис. 3.117. Результат вложенного запроса в секции HAVING Независимые многостолбцовые подзапросы в Oracle. СУБД Oracle предоставляет дополнительные возможности по реализации условий поиска на с подзапросами. До сих пор рассматривались такие запросы, где в секциях WHERE или HAVING сравнивалось значение только одного столбца с результатом, возвращаемым однострочным или многострочным подзапросом. Если требуется сравнение значений двух или более столбцов, необходимо сложное условие с логическими операторами. Многостолбцовые подзапросы позволяют объединять дублируемые условия секции WHERE или HAVING в единое условие поиска. Синтаксис запроса с многостолбцовым подзапросом в Oracle имеет вид: SELECT <столбец1>, <столбец2>, [<столбец3>,] […] FROM {<таблица>|<табличный_подзапрос>} WHERE (<столбец1>, <столбец2>, [<столбец3>,] […]) IN (SELECT <столбец1>, <столбец2>, [<столбец3>,] […] FROM {<таблица>|<табличный_подзапрос>} WHERE <условие_поиска>). Например, чтобы вывести информацию о начислениях абонентов за те же месяцы и годы, что и начисления для абонента с лицевым счетом 015527, в Oracle можно использовать запрос: SELECT * FROM NachislSumma WHERE (NachislMonth,NACHISLYEAR) IN (SELECT DISTINCT NachislMonth,NACHISLYEAR FROM NachislSumma WHERE AccountCD='015527') AND AccountCD <> '015527'; Результат представлен на рис. 3.118. 18 NACHISLFA ACCOUN SERVIC NACHISL NACHISLM NACHISL CTCD TCD ECD SUM ONTH YEAR 64 136169 3 346,18 7 2012 70 080270 4 454,6 4 2013 3 005488 2 56 4 2013 44 136169 1 28,32 2 2012 28 080270 1 57,1 2 2012 55 080270 3 278,25 11 2013 37 115705 1 37,15 11 2013 77 443069 4 444,45 10 2013 75 136169 4 528,44 10 2013 25 115705 1 37,15 10 2013 Рис. 3.118. Результат вложенного многостолбцового запроса в Oracle В Firebird и MS SQL Server не реализованы многостолбцовые подзапросы, вследствие чего запрос на вывод аналогичной информации будет более сложным. Простое разделение подзапроса на две составляющих в виде SELECT * FROM NachislSumma WHERE NachislMonth IN (SELECT DISTINCT NachislMonth FROM NachislSumma WHERE AccountCD='015527') AND NACHISLYEAR IN (SELECT DISTINCT NACHISLYEAR FROM NachislSumma WHERE AccountCD='015527') AND AccountCD <> '015527'; выдаст неверный результат (рис. 3.119). 19 NACHISLFA ACCOUN SERVIC NACHISL NACHISLM NACHISL CTCD TCD ECD SUM ONTH YEAR 3 005488 2 56 4 2013 7 080047 2 80 10 2012 25 115705 1 37,15 10 2013 28 080270 1 57,1 2 2012 36 080613 1 12,6 4 2012 37 115705 1 37,15 11 2013 44 136169 1 28,32 2 2012 45 080047 1 22,2 7 2013 48 136159 1 8,3 10 2012 52 005488 3 266,7 2 2013 54 080047 3 271,6 2 2013 55 080270 3 278,25 11 2013 57 080613 3 258,8 2 2013 59 126112 3 179,9 4 2012 64 136169 3 346,18 7 2012 70 080270 4 454,6 4 2013 75 136169 4 528,44 10 2013 77 443069 4 444,45 10 2013 Рис. 3.119. Результат некорректного преобразования многостолбцового запроса в Firebird Корректный запрос для реализации примера в Firebird: SELECT N1.* FROM NachislSumma N1 INNER JOIN (SELECT DISTINCT NachislMonth, NachislYear FROM NachislSumma WHERE AccountCD='015527') N2(NachislMonth, NachislYear) ON (N2.NachislMonth = N1.NachislMonth AND N2.NachislYear = N1.NachislYear) WHERE N1.AccountCD <> '015527'; В приведенном запросе осуществляется соединение с производной таблицей, сформированной подзапросом, по равенству в столбцах NachislMonth и NachislYear. В подзапросе и основном запросе используется одна и та же таблица. Результат выполнения запроса аналогичен результату, приведенному для Oracle на рис. 3.118 (разница только в порядке следования строк). Независимые подзапросы в секции WITH. Производные таблицы, возвращаемые табличным подзапросом, могут быть определены в секции WITH, которая записывается перед основным запросом SELECT. Такая техника вынесения определений подзапросов из тела основного запроса получила название Subquery Factoring ("факторизация", "разложение на подзапросы") [55]. Производные таблицы получили название обобщенные 20 табличные выражения (Common Table Expression, CTE) и являются наиболее сложной и мощной вариацией производных таблиц. Секция WITH может использоваться в двух целях [39]:  для придания запросу более понятной формулировки: описания подзапросов, которые многократно используются в основном запросе, с тем чтобы к ним (подзапросам) можно было обращаться в запросе по имени;  для записи рекурсивных запросов (recursive subquery factoring). Таким образом, подзапросы в секции WITH могут быть нерекурсивными и рекурсивными. Простое и рекурсивное разложения на подзапросы с помощью секции WITH не противоречат друг другу и могут использоваться совместно. Синтаксис использования секции WITH: WITH [RECURSIVE] имя_производной_таблицы1 [(<список_столбцов1>)] AS (<табличный_подзапрос1> ) [, имя_производной_таблицы2 [(<список_столбцов2>)] AS (<табличный_подзапрос2>)]... 3. Примечание. В секции WITH может быть определено несколько подзапросов. Сами секции WITH не могут быть вложенными. Ключевое слово RECURSIVE может использоваться только в Firebird для реализации рекурсивных запросов. В качестве табличного подзапроса может использоваться любой запрос SELECT, причем при использовании рекурсии <табличный_подзапрос> обязательно содержит в себе объединение результатов нескольких запросов. Примеры использования рекурсивных подзапросов в секции WITH будут приведены после изучения объединений результатов нескольких запросов. Рассмотрим примеры простого разложения на подзапросы в WITH. Получить результат, представленный на рис. 3.113, и избежать повторения подзапроса в секциях SELECT и WHERE можно, определив его только раз в секции WITH, а затем использовав в самом запросе: WITH Fact_Avg AS (SELECT AVG(NachislSum) AS Avg_Nach FROM NachislSumma GROUP BY NachislYear HAVING NachislYear = 2013) SELECT Ns.AccountCD, Ns.NachislSum, Ns.NachislMonth, Ns.NachislYear, (SELECT Avg_Nach FROM Fact_Avg) AS Avg_All FROM NachislSumma Ns WHERE Ns.NachislSum > (SELECT Avg_Nach FROM Fact_Avg) AND Ns.NachislYear = 2013 ORDER BY 1; Допустим, ACCO необходимо вывести UNTC по каждому абоненту D FIO TOTAL_20 TOTAL_2013 12 21 номер его лицевого 005488 Аксенов 280,00 316,00 счета, ФИО и общие С.А. суммы оплат за 2012 115705 Мищенко 553,85 111,45 г. и 2013 г. Е.В. (рис. 3.120). 015527 Конюхов 630,10 996,30 В.С. 443690 Тулупова 20,00 314,47 М.И. 136159 Свирина 428,30 188,43 З.А. 443069 Стародубце 504,97 444,45 в Е.В. 136160 Шмаков 18,00 56,00 С.В. 126112 Маркова 615,40 NULL В.П. 136169 Денисова 723,69 553,76 Е.К. 080613 Лукашина 23,60 498,15 Р.М. 080047 Шубина 99,56 316,36 Т. П. 080270 Тимошкина 510,00 738,00 Н.Г. Рис. 3.120. Результат выполнения запроса с простым разложением на подзапросы Запрос будет выглядеть так: WITH Year_Abon_Pay AS (SELECT PayYear, AccountCD, SUM(PaySum) AS Total_Sum FROM PaySumma GROUP BY PayYear, AccountCD) SELECT A.AccountCD, A.Fio, God_2012.Total_Sum AS Total_2012, God_2013.Total_Sum AS Total_2013 FROM Abonent A LEFT JOIN Year_Abon_Pay God_2012 ON A.AccountCD = God_2012.AccountCD AND God_2012.PayYear = 2012 LEFT JOIN Year_Abon_Pay God_2013 ON A.AccountCD = God_2013.AccountCD AND God_2013.PayYear = 2013; Особенности использования простого разложения на подзапросы в секции WITH:  производные таблицы, определенные в секции WITH, могут ссылаться друг на друга;  ссылка на производную таблицу (имя_производной_таблицы) может использоваться в любой части основного запроса (в секциях SELECT, FROM и т. д.); 22  одна и та же производная таблица может использоваться несколько раз в основном запросе под разными псевдонимами;  в многострочных запросах на обновление (INSERT, UPDATE и DELETE) подзапросы могут включать секцию WITH, определяющую производные таблицы;  производные таблицы могут использоваться в процедурном языке. 23