Pl sql not exists синтаксис. Использование оператора EXISTS. Примеры на связанные подзапросы
Кроме того, можно применять оператор EXISTS . Этот оператор часто используется с коррелированными подзапросами для проверки существования значения, извлекаемого внешним запросом, в наборе результатов, извлекаемых внутренним запросом. Если подзапрос возвращает хотя бы одну строку, оператор возвращает значение TRUE . Если значение не существует, оператор возвращает значение FALSE . Соответственно, с помощью оператора NOT EXISTS проверяется отсутствие значения, извлекаемого внешним запросом, в наборе результатов, извлекаемых внутренним запросом.
Оператор EXISTS проверяет существование строк в наборе результатов подзапроса.
поиск не продолжается во внутреннем запросе;
условие помечается как TRUE .
Если значение строки подзапроса найдено:
условие помечается как FALSE ;
поиск продолжает выполняться во внутреннем запросе.
Если значение строки подзапроса не найдено:
Оператор EXISTS
С вложенными инструкциями SELECT применимы все логические операторы. Кроме того, можно применять оператор EXISTS . Этот оператор часто используется с коррелированными подзапросами для проверки существования значения, извлекаемого внешним запросом, в наборе результатов, извлекаемых внутренним запросом. Если подзапрос возвращает хотя бы одну строку, оператор возвращает значение TRUE . Если значение не существует, оператор возвращает значение FALSE . Соответственно, с помощью оператора NOT EXISTS проверяется отсутствие значения, извлекаемого внешним запросом, в наборе результатов, извлекаемых внутренним запросом.
Предикат языка SQL EXISTS выполняет логическую задачу. В запросах SQL этот предикат используется в выражениях вида
EXISTS (SELECT * FROM ИМЯ_ТАБЛИЦЫ ...).
Это выражение возвращает истину, когда по запросу найдена одна или более строк, соответствующих условию, и ложь, когда не найдено ни одной строки.
Для NOT EXISTS всё наоборот. Выражение
NOT EXISTS (SELECT * FROM ИМЯ_ТАБЛИЦЫ ...)
возвращает истину, когда по запросу не найдено ни одной строки, и ложь, когда найдена хотя бы одна строка.
Наиболее простые запросы с предикатом SQL EXISTS
В примерах работаем с базой данных библиотеки и ее таблицами "Книга в пользовании" (BOOKINUSE) и "Пользователь" (USER). Пока нам потребуется лишь таблица "Книга в пользовании" (BOOKINUSE).
| Author | Title | Pubyear | Inv_No | User_ID |
| Толстой | Война и мир | 2005 | 28 | 65 |
| Чехов | Вишневый сад | 2000 | 17 | 31 |
| Чехов | Избранные рассказы | 2011 | 19 | 120 |
| Чехов | Вишневый сад | 1991 | 5 | 65 |
| Ильф и Петров | Двенадцать стульев | 1985 | 3 | 31 |
| Маяковский | Поэмы | 1983 | 2 | 120 |
| Пастернак | Доктор Живаго | 2006 | 69 | 120 |
| Толстой | Воскресенье | 2006 | 77 | 47 |
| Толстой | Анна Каренина | 1989 | 7 | 205 |
| Пушкин | Капитанская дочка | 2004 | 25 | 47 |
| Гоголь | Пьесы | 2007 | 81 | 47 |
| Чехов | Избранные рассказы | 1987 | 4 | 205 |
| Пастернак | Избранное | 2000 | 137 | 18 |
Пример 1. Определить ID пользователей, которым выданы книги Толстого, которым также выданы книги Чехова. Во внешнем запросе отбираются данные о пользователях, которым выданы книги Толстого, а предикат EXISTS задаёт дополнительное условие, которое проверяется в во внутреннем запросе - пользователи, которым выданы книги Чехова. Дополнительным условием во внутреннем запросе является совпадение идентификаторов пользователей из внешнего и внутреннего запросов: User_ID=tols_user.user_id. Запрос будет следующим:
Этот запрос вернёт следующий результат:
Различия предикатов EXISTS и IN
При первом взгляде на запросы с предикатом EXISTS может возникнуть впечатление, что он идентичен предикату IN . Это не так. Хотя они очень похожи. Предикат IN ведет поиск значений из диапазона, заданного в его аргументе, и если такие значения есть, то выбираются все строки, соответствующие этому диапазону. Результат же действия предиката EXISTS представляет собой ответ "да" или "нет" на вопрос о том, есть ли вообще какие-либо значения, соответствующие указанным в аргументе. Кроме того, перед предикатом IN указывается имя столбца, по которому следует искать строки, соответствующие значениям в диапазоне. Разберём пример, показывающий отличие предиката EXISTS от предиката IN, и задачу, решаемую с помощью предиката IN.
Пример 4. Определить ID пользователей, которым выданы книги авторов, книги которых выданы пользователю с ID 31. Запрос будет следующим:
| User_ID |
| 120 |
| 65 |
| 205 |
Внутренний запрос (после IN) выбирает авторов: Чехов; Ильф и Петров. Внешний запрос выбирает всех пользователей, которым выданы книги этих авторов. Видим, что, в отличие от предиката EXISTS, предикат IN предваряется именем столбца, в данном случае - Author.
Запросы с предикатом EXISTS и дополнительными условиями
Если дополнительно к предикату EXISTS в запросе применить хотя бы одно дополнительное условие, например, заданное с помощью агрегатных функций , то такие запросы могут служить уже для простого анализа данных. Продемонстрируем это на следующем примере.
Пример 5. Определить ID пользователей, которым выдана хотя бы одна книга Пастернака, и которым при этом выдано более 2 книг. Пишем следующий запрос, в котором первое условие задаётся предикатом EXISTS со вложенным запросом, а второе условие с оператором HAVING всегда должно следовать после вложенного запроса:
Результат выполнения запроса:
| User_ID |
| 120 |
Как видно из таблицы BOOKINUSE, книга Пастернака выдана также пользователю с ID 18, но ему выдана всего одна книга и он не попадает в выборку. Если применить к подобному запросу ещё раз функцию COUNT, но уже для подсчёта выбранных строк (потренируйтесь в этом самостоятельно), то можно получить сведения о том, сколько пользователей, читающих книги Пастернака, при этом читают также книги других авторов. Это уже из сферы анализа данных.
Запросы с предикатом EXISTS к двум таблицам
Запросы с предикатом EXISTS могут извлекать данные из более чем одной таблицы. Многие задачи можно с тем же результатом решить с помощью оператора JOIN , но в ряде случаев использование EXISTS позволяет составить менее громоздкий запрос. Использовать EXISTS предпочительнее в тех случаях, когда в результирующую таблицу попадут столбцы лишь из одной таблицы.
В следующем примере из той же базы данных помимо таблицы BOOKINUSE потребуется также таблица "Пользователь" (USER).
Результатом выполнения запроса будет следующая таблица:
| Author |
| Чехов |
| Маяковский |
| Пастернак |
Как и в случае использования оператора JOIN, в случаях более одной таблицы следует использовать псевдонимы таблиц для проверки соответствия значений ключей, соединяющих таблицы. В нашем примере псевдонимы таблиц - bk и us, а ключ, соединяющий таблицы - User_ID.
Предикат EXISTS в соединениях более двух таблиц
Сейчас мы увидим более предметно, почему использовать EXISTS предпочительнее в тех случаях, когда в результирующую таблицу попадут столбцы лишь из одной таблицы.
Работаем с базой данных "Недвижимость". Таблица Deal содержит данные о сделках. Для наших заданий в этой таблице будет важен столбец Type с данными о типе сделки - продажа или аренда. Таблица Object содержит данные об объектах. В этой таблице нам понадобятся значения столбцов Rooms (число комнат) и LogBalc, содержащего данные о наличии лоджии или балкона в булевом формате: 1 (да) или 0 (нет). Таблицы Client, Manager и Owner содержат данные соответственно о клиентах, менеджерах фирмы и собственниках объектов недвижимости. В этих таблицах FName и LName соответственно имя и фамилия.
Пример 7. Определить клиентов, купивших или взявших в аренду объекты, у которых нет лоджии или балкона. Пишем следующий запрос, в котором предикатом EXISTS задано обращение к результату соединения двух таблиц:
Так как из таблицы Client столбцы выбираются при помощи оператора "звёздочка", то будут выведены все столбцы этой таблицы, в которой будет столько строк, сколько насчитывается клиентов, соответствующих условию, заданному предикатом EXISTS. Из таблиц, к соединению которых обращается вложенный запрос, нам не требуется выводить ни одного столбца. Поэтому для экономии машинного времени извлекается лишь один столбец. Для этого после слова SELECT прописана единица. Этот же приём применён и в запросах в следующих примерах.
Написать запрос SQL с предикатом EXISTS самостоятельно, а затем посмотреть решение
Продолжаем писать вместе запросы SQL с предикатом EXISTS
Пример 9. Определить собственников объектов, которые были взяты в аренду. Пишем следующий запрос, в котором предикатом EXISTS также задано обращение к результату соединения двух таблиц:
Как и в предыдущем примере, из таблицы, к которой обращён внешний запрос, будут выведены все поля.
Пример 10. Определить число собственников, с объектами которых провёл менеджер Савельев. Пишем запрос, в котором внешний запрос обращается к соединению трёх таблиц, а предикатом EXISTS задано обращение лишь к одной таблице:
Все запросы проверены на существующей базе данных. Успешного использования!
Реляционные базы данных и язык SQL
В этом учебном материале вы узнаете, как использовать SQL условие EXISTS с синтаксисом и примерами.
Описание
SQL условие EXISTS используется в сочетании с подзапросом и считается выполненным, если подзапрос возвращает хотя бы одну строку. Его можно использовать в операторе SELECT, INSERT, UPDATE или DELETE.
Синтаксис
Синтаксис условия EXISTS в SQL:
Параметры или аргументы
subquery подзапрос является оператором SELECT. Если subquery возвращает хотя бы одну запись в своем наборе результатов, предложение EXISTS оценивается как true и условие EXISTS будет выполнено. Если subquery не возвращает никаких записей, предложение EXISTS оценивается как false, и условие EXISTS не будет выполнено.Примечание
Операторы SQL, использующие условие EXISTS, очень неэффективны, поскольку подзапрос повторно запускается для КАЖДОЙ строки в таблице внешнего запроса. Есть более эффективные способы написания большинства запросов, которые не используют условие EXISTS.
Пример — использование условия EXISTS с оператором SELECT
Давайте начнем с примера, который показывает, как использовать условие EXISTS с оператором SELECT.
В этом примере у нас есть таблица customers со следующими данными:
Теперь давайте найдем все записи из таблицы customers , где есть хотя бы одна запись в таблице orders с тем же customer_id . Выполните следующий SELECT запрос:
Будет выбрано 4 записи. Вот результаты, которые вы должны получить:
| customer_id | first_name | last_name | favorite_website |
|---|---|---|---|
| 4000 | Justin | Bieber | google.com |
| 5000 | Selena | Gomez | bing.com |
| 7000 | Tom | Cruise | oracle.com |
| 8000 | Johnny | Depp | NULL |
В этом примере в таблице customers есть 4 записи, у которых значение customer_id имеется в таблице orders .
Пример — использование условия EXISTS с оператором UPDATE
Давайте рассмотрим пример, в котором используется условие EXISTS в операторе UPDATE.
В этом примере у нас есть таблица products
со следующими данными:
Теперь давайте обновим таблицу summary_data значениями из таблицы products . Введите следующий SQL оператор:
PgSQL
Будет обновлено 5 записей. Снова выберите данные из таблицы summary_data :
PgSQL
SELECT * FROM summary_data;
В этом примере будет обновлено поле current_category в таблице summary_data данными category_id из таблицы products , где значения product_id совпадают. Первые 5 записей в таблице summary_data были обновлены.
Подсказка : Если бы мы не включили условие EXISTS, запрос UPDATE обновил бы поле current_category на NULL в 6-й строке таблицы summary_data (поскольку таблица products не имеет записи, где product_id = 8).
Пример — использование условия EXISTS с оператором DELETE
Давайте посмотрим на пример, который использует условие EXISTS в опертаоре DELETE.
В этом примере у нас есть таблица customer со следующими данными:
Введите следующий оператор DELETE:
PgSQL
Будет удалена 1 запись. Выберите данные из таблицы orders еще раз:
PgSQL
SELECT * FROM orders;
SELECT *FROM orders; |
Вот результаты, которые вы должны получить.
«Раньше было проще» — Подумал я, садясь за оптимизацию очередного запроса в SQL management studio. Когда я писал под MySQL, реально все было проще — или работает, или нет. Или тормозит или нет. Explain решал все мои проблемы, больше ничего не требовалось. Сейчас у меня есть мощная среда разработки, отладки и оптимизации запросов и процедур/функций, и все это нагромождение создает по-моему только больше проблем. А все почему? Потому что встроенный оптимизатор запросов — зло. Если в MySQL и PostgreSQL я напишу
Select * from a, b, c where a.id = b.id, b.id = c.id
и в каждой из табличек будет хотя бы по 5к строк — все зависнет. И слава богу! Потому что иначе в разработчике, в лучшем случае, вырабатывается ленность писать правильно, а в худшем он вообще не понимает что делает! Ведь этот же запрос в MSSQL пройдет аналогично
Select * from a join b on a.id = b.id join c on b.id = c.id
Встроенный оптимизатор причешет быдлозапрос и все будет окей.
Он так же сам решит, что лучше делать — exist или join и еще много чего. И все будет работать максимально оптимально.
Только есть одно НО. В один прекрасный момент оптимизатор споткнется о сложный запрос и спасует, и тогда вы получите большущую проблему. И получите вы ее, возможно, не сразу, а когда вес таблиц достигнет критической массы.
Так вот к сути статьи. exists и in — очень тяжелые операции. Фактически это отдельный подзапрос для каждой строчки результата. А если еще и присутствует вложенность, то это вообще туши свет. Все будет окей, когда возвращается 1, 10, 50 строк. Вы не почувствуете разницы, а возможно join будет даже медленнее. Но когда вытаскивается 500 — начнутся проблемы. 500 подзапросов в рамках одного запроса — это серьезно.
Пусть с точки зрения человеческого понимания in и exists лучше, но с точки зрения временных затрат для запросов, возвращающих 50+ строк — они не допустимы.
Нужно оговориться, что естественно, если где-то убывает — где-то должно прибывать. Да, join более ресурсоемок по памяти, ведь держать единовременно всю таблицу значений и оперировать ею — накладнее, чем дергать подзапросы для каждой строки, быстро освобождая память. Нужно смотреть конкретно по запросу и замерять — критично ли будет использование лишней памяти в угоду времени или нет.
Приведу примеры полных аналогий. Вообще говоря, я не встречал еще запросов такой степени сложности, которые не могли бы быть раскручены в каскад join’ов. Пусть на это уйдет день, но все можно раскрыть.
Select * from a where a.id in (select id from b) select * from a where exists (select top 1 1 from b where b.id = a.id) select * from a join b on a.id = b.id select * from a where a.id not in (select id from b) select * from a where not exists (select top 1 1 from b where b.id = a.id) select * from a left join b on a.id = b.id where b.id is null
Повторюсь — данные примеры MSSQL оптимизатор оптимизирует под максимальную производительность и на таких простейших запросах тупняков не будет никогда.
Рассмотрим теперь пример реального запроса, который пришлось переписывать из-за того что на некоторых выборках он просто намертво зависал (структура очень упрощена и понятия заменены, не нужно пугаться некоей не оптимальности структуры бд).
Нужно вытащить все дубликаты «продуктов» в разных аккаунтах, ориентируясь на параметры продукта, его группы, и группы-родителя, если таковая есть.
Select d.PRODUCT_ID from PRODUCT s, PRODUCT_GROUP sg left join M_PG_DEPENDENCY sd on (sg.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_CHILD_ID), PRODUCT d, PRODUCT_GROUP dg left join M_PG_DEPENDENCY dd on (dg.PRODUCT_GROUP_ID = dd.M_PG_DEPENDENCY_CHILD_ID) where s.PRODUCT_GROUP_ID=sg.PRODUCT_GROUP_ID and d.PRODUCT_GROUP_ID=dg.PRODUCT_GROUP_ID and sg.PRODUCT_GROUP_PERSPEC=dg.PRODUCT_GROUP_PERSPEC and sg.PRODUCT_GROUP_NAME=dg.PRODUCT_GROUP_NAME and s.PRODUCT_NAME=d.PRODUCT_NAME and s.PRODUCT_TYPE=d.PRODUCT_TYPE and s.PRODUCT_IS_SECURE=d.PRODUCT_IS_SECURE and s.PRODUCT_MULTISELECT=d.PRODUCT_MULTISELECT and dg.PRODUCT_GROUP_IS_TMPL=0 and ((sd.M_PG_DEPENDENCY_CHILD_ID is null and dd.M_PG_DEPENDENCY_CHILD_ID is null) or exists (select 1 from PRODUCT_GROUP sg1, PRODUCT_GROUP dg1 where sd.M_PG_DEPENDENCY_PARENT_ID = sg1.PRODUCT_GROUP_ID and dd.M_PG_DEPENDENCY_PARENT_ID = dg1.PRODUCT_GROUP_ID and sg1.PRODUCT_GROUP_PERSPEC=dg1.PRODUCT_GROUP_PERSPEC and sg1.PRODUCT_GROUP_NAME=dg1.PRODUCT_GROUP_NAME and))
Так вот это тот случай, когда оптимизатор спасовал. И для каждой строчки выполнялся тяжеленный exists, что убивало базу.
Select d.PRODUCT_ID from PRODUCT s join PRODUCT d on s.PRODUCT_TYPE=d.PRODUCT_TYPE and s.PRODUCT_NAME=d.PRODUCT_NAME and s.PRODUCT_IS_SECURE=d.PRODUCT_IS_SECURE and s.PRODUCT_MULTISELECT=d.PRODUCT_MULTISELECT join PRODUCT_GROUP sg on s.PRODUCT_GROUP_ID=sg.PRODUCT_GROUP_ID join PRODUCT_GROUP dg on d.PRODUCT_GROUP_ID=dg.PRODUCT_GROUP_ID and sg.PRODUCT_GROUP_NAME=dg.PRODUCT_GROUP_NAME and sg.PRODUCT_GROUP_PERSPEC=dg.PRODUCT_GROUP_PERSPEC left join M_PG_DEPENDENCY sd on sg.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_CHILD_ID left join M_PG_DEPENDENCY dd on dg.PRODUCT_GROUP_ID = dd.M_PG_DEPENDENCY_CHILD_ID left join PRODUCT_GROUP sgp on sgp.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_PARENT_ID left join PRODUCT_GROUP dgp on dgp.PRODUCT_GROUP_ID = dd.M_PG_DEPENDENCY_PARENT_ID and sgp.PRODUCT_GROUP_NAME = dgp.PRODUCT_GROUP_NAME and isnull(sgp.PRODUCT_GROUP_IS_TMPL, 0) = isnull(dgp.PRODUCT_GROUP_IS_TMPL, 0) where (sd.M_PG_DEPENDENCY_CHILD_ID is null and dd.M_PG_DEPENDENCY_CHILD_ID is null) or (sgp.PRODUCT_GROUP_NAME is not null and dgp.PRODUCT_GROUP_NAME is not null) go
После данных преобразований производительность вьюхи увеличилась экспоненциально количеству найденных продуктов. Вернее сказать, время поиска оставалось практически независимым от числа совпадений и было всегда очень маленьким. Как и должно быть.
Это наглядный пример того, как доверие MSSQL оптимизатору может сыграть злую шутку. Не доверяйте ему, не ленитесь, join’те ручками, каждый раз думайте что лучше в данной ситуации — exists, in или join.
SQL позволяет вкладывать запросы друг в друга. Обычно подзапрос возвращает одно значение, которое проверяется на предмет истинности предиката.
Виды условий поиска:
. Сравнение с результатом вложенного запроса (=, >=)
. Проверка на принадлежность результатам подзапроса (IN)
. Проверка на существование (EXISTS)
. Многократное (количественное) сравнение (ANY, ALL)
Примечания по вложенным запросам:
. Подзапрос должен выбирать только один столбец (за исключением подзапроса с предикатом EXISTS), и тип данных его результата должен соответствовать типу данных значения, указанному в предикате.
. В ряде случаев можно использовать ключевое слово DISTINCT для гарантии получения единственного значения.
. Во вложенном запросе нельзя включать раздел ORDER BY и UNION.
. Подзапрос может находиться и лева и справа от условия поиска.
. В подзапросах могут использоваться функции агрегирования без раздела GROUP BY, которые автоматически выдают специальное значение для любого количества строк, специальный предикат IN, а также выражения, основанные на столбцах.
. По возможности следует вместо подзапросов использовать соединение таблиц JOIN.
Примеры на вложенные запросы :
SELECT * FROM Orders WHERE SNum=(SELECT SNum FROM SalesPeople WHERE SName=’Motika’)
SELECT * FROM Orders WHERE SNum IN (SELECT SNum FROM SalesPeople WHERE City=’London’)
SELECT * FROM Orders WHERE SNum=(SELECT DISTINCT SNum FROM Orders WHERE CNum=2001)
SELECT * FROM Orders WHERE Amt>(SELECT AVG(Amt) FROM Orders WHERE Odate=10/04/1990)
SELECT * FROM Customer WHERE CNum=(SELECT SNum+1000 FROM SalesPeople WHERE SName=’Serres’)
2) Связанные подзапросы
В SQL можно создавать подзапросы со ссылкой на таблицу из внешнего запроса. В этом случае подзапрос выполняется многократно, по одному разу для каждой строки таблицы из внешнего запроса. Поэтому важно, чтобы подзапрос использовал индекс. Подзапрос может обращаться к той же таблице, чтоб и внешний. Если внешний запрос возвращает относительно небольшое число строк, то связанный подзапрос будет работать быстрее несвязанного. Если подзапрос возвращает небольшое число строк, то связанный запрос выполнится медленнее несвязанного.
Примеры на связанные подзапросы:
SELECT * FROM SalesPeople Main WHERE 1(SELECT AVG(Amt) FROM Orders O2 WHERE O2.CNum=O1.CNum) //возвращает все заказы, величина которых превосходит среднюю величины заказа для данного покупателя
3) Предикат EXISTS
Синтаксическая форма: EXISTS ()
Предикат использует подзапрос в качестве аргумента и оценивает его как истинный, если в подзапросе есть выходные данные, а в противном случае как ложный. Выполняется подзапрос один раз и может содержать несколько столбцов, поскольку их значения не проверяются, а просто фиксируется результат наличия строк.
Примечания по предикату EXISTS:
. EXISTS – предикат, возвращающий значение TRUE или FALSE, и его можно применять отдельно или вместе с другими булевыми выражениями.
. EXISTS не может использовать функции агрегирования в своем подзапросе.
. В коррелирующих (связанных, зависимых – Correlated) подзапросах предикат EXISTS выполняется для каждой строки внешней таблицы.
. Можно комбинировать предикат EXISTS с соединениями таблиц.
Примеры на предикат EXISTS:
SELECT * FROM Customer WHERE EXISTS(SELECT * FROM Customer WHERE City=’San Jose’) – возвращает всех покупателей, если кто-то из них проживает в San Jose.
SELECT DISTINCT SNum FROM Customer First WHERE NOT EXISTS (SELECT * FROM Customer Send WHERE Send.SNum=First.SNum AND Send.CNumFirst.CNum) – возвращает номера продавцов, обслуживших только одного покупателя.
SELECT DISTINCT F.SNum, SName, F.City FROM SalesPeople F, Customer S WHERE EXISTS (SELECT * FROM Customer T WHERE S.SNum=T.SNum AND S.CNumT.CNum AND F.SNum=S.SNum) – возвращает номера, имена и города проживания всех продавцов, обслуживших нескольких покупателей.
SELECT * FROM SalesPeople Frst WHERE EXISTS (SELECT * FROM Customer Send WHERE Frst.SNum=Send.SNum AND 1
4) Предикаты количественного сравнения
Синтаксическая форма: {=|>|=|} ANY|ALL ()
Эти предикаты используют в качестве аргумента подзапрос, однако, по сравнению с предикатом EXISTS, они применяются в конъюнкции с предикатами отношения (=,>=). В этом смысле они сходны с предикатом IN, но применяются только с подзапросами. Стандарт допускает использовать вместо ANY ключевое слово SOME, однако не все СУБД его поддерживают.
Примечания по предикатам сравнения:
. Предикат ALL принимает значение TRUE, если каждое значение, выбранное в процессе выполнения подзапроса, удовлетворяет условию, заданному в предикате внешнего запроса. Чаще всего он используется с неравенствами.
. Предикат ANY принимает значение TRUE, если хотя бы одно значение, выбранное в процессе выполнения подзапроса, удовлетворяет условию, заданному в предикате внешнего запроса. Чаще всего он используется с неравенствами.
. Если подзапрос не возвращает строк, то ALL автоматически принимает значение TRUE (считается, что условие сравнения выполняется), а для ANY – FALSE.
. Если сравнение не имеет значения TRUE ни для одной строки и есть одна или несколько строк с NULL значением, то ANY возвращает UNKNOWN.
. Если сравнение не имеет значения FALSE ни для одной строки и есть одна или несколько строк с NULL значением, то ALL возвращает UNKNOWN.
Примеры на предикат количественного сравнения:
SELECT * FROM SalesPeople WHERE City=ANY(SELECT City FROM Customer)
SELECT * FROM Orders WHERE Amt ALL(SELECT Rating FROM Customer WHERE City=’Rome’)
5) Предикат уникальности
UNIQUE|DISTINCT ()
Предикат служит для проверка уникальности (отсутствия дублей) в выходных данных подзапроса. Причем в предикате UNIQUT строки с NULL значениями считаются уникальными, а в предикате DISTINCT два неопределенных значения считаются равными друг другу.
6) Предикат совпадений
MATCH ()
Предикат MATCH проверяет, будет ли значение строки запроса совпадать со значением любой строки, полученной в результате подзапроса. От предикатов IN И ANY такой подзапрос отличается тем, что позволяет обрабатывать «частичные» (PARTIAL) совпадения, которые могут встречаться среди строк, имеющих часть NULL-значений.
7) Запросы в разделе FROM
Фактически допустимо использовать подзапрос везде, где допускается ссылка на таблицу.
SELECT CName, Tot_Amt FROM Customer, (SELECT CNum, SUM(Amt) AS Tot_Amt FROM Orders GROUP BY CNum) WHERE City=’London’ AND Customer.CNum=Orders.CNum
//подзапрос возвращает суммарную величину заказов, сделанных каждым покупателем из Лондона.
8) Рекурсивные запросы
WITH RECURSIVE
Q1 AS SELECT … FROM … WHERE …
Q2 AS SELECT … FROM … WHERE …