Sqlпростые вложенные подзапросы

Sqlпростые вложенные подзапросы

Простые вложенные подзапросы используются для представления множества значений, исследование которых должно осуществляться в каком-либо предикате IN, что иллюстрируется в следующем примере: выдать название и статус поставщиков продукта с номером 11, т.е. помидоров.

Результат:

Название Статус
СЫТНЫЙ рынок
УРОЖАЙ коопторг
ЛЕТО агрофирма
КОРЮШКА кооператив

Как уже отмечалось в п.3.3.1, при обработке полного запроса система выполняет прежде всего вложенный подзапрос. Этот подзапрос выдает множество номеров поставщиков, которые поставляют продукт с кодом ПР = 11, а именно множество (1, 5, 6, 8). Поэтому первоначальный запрос эквивалентен такому простому запросу:

Подзапрос с несколькими уровнями вложенности можно проиллюстрировать на том же примере. Пусть требуется узнать не поставщиков продукта 11, как это делалось в предыдущих запросах, а поставщиков помидоров, являющихся продуктом с номером 11. Для этого можно дать запрос

В данном случае результатом самого внутреннего подзапроса является только одно значение (11). Как уже было показано выше, подзапрос следующего уровня в свою очередь дает в результате множество (1, 5, 6, 8). Последний, самый внешний SELECT, вычисляет приведенный выше окончательный результат. Вообще допускается любая глубина вложенности подзапросов.

Тот же результат можно получить с помощью соединения

При выполнении этого компактного запроса система должна одновременно обрабатывать данные из трех таблиц, тогда как в предыдущем примере эти таблицы обрабатываются поочередно. Естественно, что для их реализации тебуются различные ресурсы памяти и времени, однако этого невозможно ощутить при работе с ограниченным объемом данных в иллюстративной базе ПАНСИОН.

Подзапросы SQL, урок 15 — вложенные запросы

Что такое подзапросы

Подзапросы, внутренние или вложенные запросы это запрос внутри запроса. Обычно, подзапрос используется в конструкции WHERE. И, в большинстве случаев, подзапросы SQL используется, когда вы можете получить значение с помощью запроса, но не знаете конкретного результата.
Подзапросы альтернативный вариант получения данных из множества таблиц.

Наряду с операторами сравнения такими, как =, , >=, Подзапросы SQL: видео урок

SQL Подзапросы

категория
Базы данных
дата 16.07.2009
автор flexxs
голосов 14

[Disclaimer: Данная статья была переведена в рамках «Конкурса на лучший перевод статьи» на сервисе Quizful. Ссылка на оригинал находится внизу страницы.]

Подзапросы, внутренние или вложенные запросы – есть не что иное, как запрос внутри запроса. Обычно, подзапрос используется в конструкции WHERE. И, в большинстве случаев, подзапрос используется, когда вы можете получить значение с помощью запроса, но не знаете конкретного результата.

Подзапросы являются альтернативным путем получения данных из множества таблиц.

Наряду с операторами сравнения такими, как =, , >=, 14 Голосовать

SQL: подзапросы, их виды, корректное использование

Подзапросы, возвращающие единственное значение

Подзапросы (вложенные запросы), возвращающие единственное значение, наиболее часто применяются в случаях, когда значение определённого столбца в основном запросе требуется сравнить с некоторым единственным значением при помощи одного из операторов сравнения (=, , =). Значение, с которым производится сравнение, как раз и возвращается подзапросом (вложенным запросом).

Верные признаки того, что подзапрос вернёт одно единственное значение:

  • в подзапросе применяется одна из агрегатных функций (COUNT, SUM, AVG, MAX, MIN);
  • подзапрос извлекает значение уникального идентификатора, например, первичного ключа.

В остальных случаях нужно быть полностью уверенным, что условиям, указанным в секции WHERE подзапроса, соответствует единственное значение выбираемого столбца.

В примерах работаем с базой данных «Театр». Таблица Play содержит данные о постановках. Таблица Team — о ролях актёров. Таблица Actor — об актёрах. Таблица Director — о режиссёрах. Поля таблиц, первичные и внешние ключи можно увидеть на рисунке ниже (для увеличения нажать левой кнопкой мыши).

Пример 1. Вывести спектакли режиссёра John Barton. Запрос будет следующим:

Поскольку подзапрос возвращает значение уникального идентификатора, можно быть уверенным в том, что он вернёт единственное значение.

Более редкий случай использования подзапросов, возвращающих единственное значение — в запросах с предикатом BETWEEN, где подзапросы задают границы интервала.

Подзапросы, возвращающие множество значений

Подзапросы, возвращающие множество значений, могут применяться в запросах с предикатами IN и EXISTS и кванторными функциями ALL и ANY.

Пример 2. В таблице TEAM столбец Mainteam содержит данные о том, главная ли роль закреплена с спектакле за актёром. Значение столбца ‘Y’ означает «да», ‘N’ — «нет». Вывести список актёров, которые когда-либо исполняли главные роли. Запрос будет следующим:

Подзапрос вернёт множество значений FName и LName, которые через ключ Actor_ID будет передано в основной запрос и окончательно выведены в качестве результата.

На сайте есть отдельный урок о запросах с предикатом IN.

Пример 3. В таблице ACTOR (актёр) есть столбец SEX, содержащий данные о поле (‘M’ — мужской, ‘F’ — женский) актёра. Вывести спектакли, в которых играют только мужчины. Запрос будет следующим:

Предикат NOT EXISTS принимает подзапрос как аргумент и оценивает его как подходящий, если значения sex для одного или более актёров в таблице actor не равны F.

На сайте есть подробный урок о запросах с предикатами EXISTS и NOT EXISTS.

Пример 4. Определить самый популярный жанр театра. Пишем запрос с использованием кванторной функции ALL:

Кванторная функция ALL проверяет значения количества жанров среди всех спектаклей и затем находит жанр с количеством большим, чем у любого другого жанра, или равным ему.

На сайте есть подробный урок о запросах с кванторными функциями ALL и ANY.

Некоррелирующие и коррелирующие подзапросы

Подзапрос, возвращающий результат или результаты, для получения которых значения указанного столбца не должны соотноситься (коррелировать) со значениями столбцов, указанных в основном запросе, называется некоррелирующим. Результат выполнения некоррелирующего запроса не зависит от значений, возвращаемых основным запросом. Обычно некоррелирующие запросы применяются в запросах, в которых значение определённого столбца сравнивается со значением, возвращаемым подзапросом, в запросах с предикатом IN, кванторными функциями ALL и ANY. Однако уже в запросах с предикатом EXISTS применяются коррелирующие подзапросы.

Подзапрос, возвращающий результат или результаты, для получения которых значения указанного столбца должны соотноситься (коррелировать) со значениями столбцов, указанных в основном запросе, называется коррелирующим. Иными словами, результат, выполнения подзапроса зависит от значений, возвращаемых основным запросом. Часто коррелирующие подзапросы применяются для получения значений одного из столбцов результирующей таблицы и в этих случаях подзапрос, заключённый в скобки, перечисляется через запятую вместе с именами столбцов из таблиц или соединения таблиц.

Пример 5. Вывести список актёров с количеством их ролей. Пишем следующий запрос с коррелирующим подзапросом:

В основном запросе происходит первое обращение к таблице ACTOR, которая получает псевдоним a. В подзапросе происходит второе обращение к таблице ACTOR, которая получает псевдоним a1. При этом в секции WHERE подзапроса указано условие: идентификаторы актёров, возвращаемые основным запросом и подзапросом, должны совпадать. Это условие — характерный признак коррелирующего подзапроса.

Руководство по SQL. Вложенные запросы.

Вложенный запрос – это запрос, который находится внутри другого SQL запроса и встроен внутри условного оператора WHERE.
Данный вид запросов используется для возвращения данных, которые будут использоваться в основном запросе, как условие для ограничения получаемых данных.
Вложенные запросы должны следовать следующим правилам:

  • Вложенный запрос должен быть заключён в родительский запрос.
  • Вложенный запрос может содержать только одну колонку в операторе SELECT.
  • Оператор ORDER BY не может быть использован во вложенном запросе. Для обеспечения функционала ORDER BY, во вложенном запросе может быть использован GROUP BY.
  • Вложенные запросы, возвращающие более одной записи могут использоваться с операторами нескольких значений, как оператор IN.
  • Вложенный запрос не может заканчиваться в функции.
  • SELECT не может включать никаких ссылок на значения BLOB, ARRAY, CLOB и NCLOB.
  • Оператор BETWEEN не может быть использован вместе с вложенным запросом.
Илон Маск рекомендует:  Что такое код unionrect

Вложенный запрос имеет следующий вид:

Предположим, что у нас есть таблица developers, которая содержит следующие записи:

Попробуем выполнить следующий вложенный запрос:

Предположим, что у нас есть клон таблицы developers, который имеет имя developers_clone и имеет следующую структуру:

И не содержит данных:

Теперь попробуем выполнить для этой же таблицы следующий запрос:

В результате выполнения данного запроса таблица developers_clone будет содержать следующие данные:

Другими словами, мы скопировали все данные из таблицы developers в таблицу developers_clone.

Теперь мы изменим данные в таблице developers воспользовавшись данными из таблицы developers_clone с помощью следующего запроса:

В результате этого наша таблица содержащая изначальные данные:

Будет хранить следующие данные:

И наконец, попробуем выполнить удаление данных из таблицы с помощью вложенного запроса:

В результате таблица developers содерит следующие записи:

Очистим таблицу developers:

Теперь восстановим данные таблицы developers, с помощью резервной таблицы developers_clone используя следующий запрос:

Наша таблица developers имеет исходный вид:

На этом мы заканчиваем изучение вложенных запросов.
В следующей статье мы рассмотрим использование последовательностей.

Образовательный блог — всё для учебы

1) Вложенные подзапросы

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

Синтаксическая форма: [NOT] 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.CNum<>First.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.CNum<>T.CNum AND F.SNum=S.SNum) – возвращает номера, имена и города проживания всех продавцов, обслуживших нескольких покупателей.
SELECT * FROM SalesPeople Frst WHERE EXISTS (SELECT * FROM Customer Send WHERE Frst.SNum=Send.SNum AND 1 [NOT] <=|>| =| > 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 AmtALL(SELECT Rating FROM Customer WHERE City=’Rome’)

5) Предикат уникальности

UNIQUE|DISTINCT ( )

Предикат служит для проверка уникальности (отсутствия дублей) в выходных данных подзапроса. Причем в предикате UNIQUT строки с NULL значениями считаются уникальными, а в предикате DISTINCT два неопределенных значения считаются равными друг другу.

6) Предикат совпадений

MATCH [UNIQUE] [PARTIAL|FULL] ( )

Предикат 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 …

Связанные подзапросы

Подзапросы мы кратко рассмотрели в статье Подзапросы и временные таблицы. Здесь мы более подробно рассмотрим связанный тип подзапросов. Подзапрос называется связанным (correlated), если любые значения вложенного запроса зависят от внешнего запроса. В примере ниже показано использование связанного подзапроса:

В этом примере вложенный запрос должен логически выполниться несколько раз, поскольку он содержит столбец Id, который принадлежит таблице Employee во внешнем запросе, и значение столбца Id изменяется каждый раз, когда проверяется другая строка таблицы Employee во внешнем запросе.

Давайте проследим, как система может выполнять запрос в этом примере. Сначала система выбирает первую строку таблицы Employee (для внешнего запроса) и сравнивает табельный номер сотрудника в этом столбце (25348) со значениями столбца Works_on.EmpId вложенного запроса. Поскольку для этого сотрудника имеется только одно значение ProjectNumber равное p2, вложенный запрос возвращает значение p2. Это единственное значение результирующего набора вложенного запроса не равно значению p3 внешнего запроса, условие внешнего запроса (WHERE ‘p3’ IN. ) не удовлетворяется и, следовательно, внешний запрос не возвращает никаких строк для этого сотрудника.

Далее система берет следующую строку таблицы Employee и снова сравнивает номера сотрудников в обеих таблицах. Для этой строки в таблице Works_on есть две строки, для которых значение ProjectNumber равно p1 и p3 соответственно. Следовательно, вложенный запрос возвращает результат p1 и p3. Значение одного из элементов этого результирующего набора равно константе p3, поэтому условие удовлетворяется, и отображается соответствующее значение второй строки столбца LastName (‘Фролов’). Такой же обработке подвергаются все остальные строки таблицы Employee, и в конечном результате возвращается набор из трех строк.

В следующем разделе приводятся дополнительные примеры по связанным подзапросам.

Подзапросы и функция EXISTS

Функция EXISTS принимает вложенный запрос в качестве аргумента и возвращает значение false, если вложенный запрос не возвращает строк и значение true в противном случае. Рассмотрим работу этой функции на нескольких примерах, начиная со следующего примера:

В этом примере происходит выборка фамилий всех сотрудников, работающих над проектом p1. Вложенный запрос функции EXISTS почти всегда зависит от переменной с внешнего запроса. Поэтому функция EXISTS обычно определяет связанный подзапрос.

Давайте проследим, как Database Engine может обрабатывать запрос в этом примере. Сначала внешний запрос рассматривает первую строку таблицы Employee (сотрудник Фролов). Далее функция EXISTS определяет, есть ли в таблице Works_on строки, чьи номера сотрудников совпадают с номером сотрудника в текущей строке во внешнем запросе и чей ProjectNumber равен p1. Поскольку сотрудник Фролов не работает над проектом p1, вложенный запрос возвращает пустой набор, вследствие чего функция EXISTS возвращает значение false. Таким образом, сотрудник Фролов не включается в конечный результирующий набор. Этому процессу подвергаются все строки таблицы Employee, после чего выводится конечный результирующий набор.

В примере ниже показано использование функции NOT EXISTS:

В этом примере происходит выборка фамилий сотрудников, чей отдел не расположен в Санкт-Петербурге.

Список выбора инструкции SELECT во внешнем запросе с функцией EXISTS не обязательно должен быть в форме SELECT *, как в предыдущем примере. Можно использовать альтернативную форму SELECT colum_list, где column_list представляет список из одного или нескольких столбцов таблицы. Обе формы равнозначны, потому что функция EXISTS только проверяет на наличие (или отсутствие) строк в результирующем наборе. По этой причине в данном случае правильнее использовать форму SELECT *.

Что использовать, соединения или подзапросы?

Почти все инструкции SELECT для соединения таблицы посредством оператора соединения JOIN можно заменить инструкциями подзапроса и наоборот. Конструкция инструкции SELECT с использованием оператора соединения часто более удобно читаемая и легче понимаемая, а также может помочь компоненту Database Engine найти более эффективную стратегию для выборки требуемых данных. Но некоторые задачи легче поддаются решению посредством подзапросов, а другие при помощи соединений.

Преимущества подзапросов

Подзапросы будет более выгодно использовать в таких случаях, когда требуется вычислить агрегатное значение «на лету» и использовать его в другом запросе для сравнения. Это показано в примере ниже:

В этом примере происходит выборка табельных номеров сотрудников и дат начала их работы над проектом (EnterDate) для всех сотрудников, у которых дата начала работы равна самой ранней дате. Решить эту задачу с помощью соединения будет нелегко, поскольку для этого нужно поместить агрегатную функцию в предложении WHERE, а это не разрешается. (Эту задачу можно решить, используя два отдельных запроса по отношении к таблице Works_on.)

Преимущества соединений

Использовать соединения вместо подзапросов выгоднее в тех случаях, когда список выбора инструкции SELECT в запросе содержит столбцы более чем из одной таблицы. Это показано в примере ниже:

В этом примере происходит выборка информации о всех сотрудниках (табельный номер, фамилия и должность), которые начали участвовать в работе над проектом с 15 апреля 2007 г. Список выбора инструкции SELECT в запросе содержит столбцы Id и LastName из таблицы Employee и столбец Job из таблицы Works_on. По этой причине решение с применением подзапроса возвратило бы ошибку, поскольку подзапросы могут отображать информацию только из внешней таблицы.

SQL — Урок 5. Вложенные запросы

В прошлом уроке мы столкнулись с одним неудобством. Когда мы хотели узнать, кто создал тему «велосипеды», и делали соответствующий запрос:

Вместо имени автора, мы получали его идентификатор. Это и понятно, ведь мы делали запрос к одной таблице — Темы, а имена авторов тем хранятся в другой таблице — Пользователи. Поэтому, узнав идентификатор автора темы, нам надо сделать еще один запрос — к таблице Пользователи, чтобы узнать его имя:

В SQL предусмотрена возможность объединять такие запросы в один путем превращения одного из них в подзапрос (вложенный запрос). Итак, чтобы узнать, кто создал тему «велосипеды», мы сделаем следующий запрос:

То есть, после ключевого слова WHERE, в условие мы записываем еще один запрос. MySQL сначала обрабатывает подзапрос, возвращает >WHERE внешнего запроса.

В одном запросе может быть несколько подзапросов, синтаксис у такого запроса следующий:

Обратите внимание, что подзапросы могут выбирать только один столбец, значения которого они будут возвращать внешнему запросу. Попытка выбрать несколько столбцов приведет к ошибке.

Давайте для закрепления составим еще один запрос, узнаем, какие сообщения на форуме оставлял автор темы «велосипеды»:

Теперь усложним задачу, узнаем, в каких темах оставлял сообщения автор темы «велосипеды»:

Давайте разберемся, как это работает.

    Сначала MySQL выполнит самый глубокий запрос:

Полученный результат (id_topic:4,1) передаст во внешний запрос, который примет вид:

Т.е. мы можем использовать любые операторы, используемые с ключевым словом WHERE (их мы изучали в прошлом уроке).

Видеоуроки php + mysql

Если этот сайт оказался вам полезен, пожалуйста, посмотрите другие наши статьи и разделы.

Использование вложенных SQL-запросов

Подзапросы

Язык SQL разрешает использовать в других операторах языка DML подзапросы , которые являются внутренними запросами, определяемыми оператором SELECT .

Подзапрос — очень мощное средство языка SQL. Он позволяет строить сложные иерархии запросов, многократно выполняемые в процессе построения результирующего набора или выполнения одного из операторов изменения данных ( DELETE , INSERT , UPDATE ).

Условно подзапросы иногда подразделяют на три типа, каждый из которых является сужением предыдущего:

  • табличный подзапрос , возвращающий набор строк и столбцов;
  • подзапрос строки , возвращающий только одну строку, но, возможно, несколько столбцов (такие подзапросы часто используются во встроенном SQL );
  • скалярный подзапрос , возвращающий значение одного столбца в одной строке.

Подзапрос позволяет решать следующие задачи:

  • определять набор строк, добавляемый в таблицу на одно выполнение оператора INSERT ;
  • определять данные, включаемые в представление, создаваемое оператором CREATE VIEW ;
  • определять значения, модифицируемые оператором UPDATE ;
  • указывать одно или несколько значений во фразах WHERE и HAVING оператора SELECT ;
  • определять во фразе FROM таблицу как результат выполнения подзапроса ;
  • применять коррелированные подзапросы . Подзапрос называется коррелированным, если запрос, содержащийся в предикате, имеет ссылку на значение из таблицы (внешней к данному запросу), которая проверяется посредством данного предиката.

Hекоторые СУБД (например, СУБД Oracle ) позволяют на основе подзапроса создавать новые таблицы с помощью оператора CREATE TABLE .

Простым примером использования подзапроса может служить следующий оператор:

В данном операторе подзапрос всегда должен возвращать единственное значение , которое будет проверяться в предикате. Если подзапрос вернет более одного значения, то СУБД выдаст сообщение об ошибке выполнения SQL -оператора.

В случае если подзапрос не выберет ни одной строки, то предикат будет равен UNKNOWN , что большинством СУБД интерпретируется как FALSE .

Стандарт определяет запись предиката в форме » значение оператор подзапрос «. Однако некоторые СУБД также позволяют записывать предикат в форме, указывающей подзапрос слева от оператора сравнения.

Очень часто с подзапросами используются агрегирующие функции, предоставляющие возможность сформулировать условие типа «больше, чем среднее по группе».

Если результатом подзапроса становится группа строк (это случается всегда, когда условие не гарантирует уникальности значения проверяемого предикатом внутреннего запроса), то следует использовать оператор IN , осуществляющий выбор одного значения из указываемого множества .

В этом случае предикат принимает значение TRUE , если хотя бы одно из значений, возвращаемых подзапросом , удовлетворяет условию.

Однако применение оператора IN имеет и некоторые смысловые недостатки: в запросе четко не определяется, сколько строк должны быть результатом выполнения запроса. При построении отношений для реальной модели данных это может приводить к некоторой неоднозначности и зависимости от самих данных. В противном случае, если модель данных предполагает в качестве постоянного результата подзапроса наличие только одной строки и, соответственно, использует оператор сравнения = , а структура данных позволяет ввод значений, когда в результате подзапроса будет более одной строки, то при использовании такого SQL -оператора в какой-то момент времени может проявиться ошибка.

Если в запросе участвуют более двух таблиц, то для большей наглядности имена полей иногда квалифицируют именами таблиц, указывая их через точку. Стандарт позволяет не квалифицировать имя поля именем таблицы в том случае, если не возникает неоднозначности ( поле сначала ищется в таблице, указанной фразой FROM текущего запроса, затем внешнего запроса и т.д.).

Очень часто вместо записи оператора SELECT с использованием подзапроса можно применять соединения. Однако на практике большинство СУБД подзапросы выполняют более эффективно. Тем не менее, при проектировании комплекса программ с критичными требованиями по быстродействию, разработчик должен проанализировать план выполнения SQL -оператора для конкретной СУБД .

Наиболее продвинутые СУБД , такие как Oracle , предоставляют ряд SQL -операторов, позволяющих оценить производительность выполнения конкретного оператора языка SQL , а также определить уровень оптимизации, применяемый для данного оператора.

Подзапрос может быть указан как в предикате, определяемом фразой WHERE , так и в предикате по группам, определяемом фразой HAVING .

Тема 9. Подзапросы в SQL

Использование подзапросов в условиях команды SELECT позволяет создавать сложные запросы, решающие задачи, неразрешимые другим способом.

Подзапрос заключается в круглые скобки и представляет собой вложенную команду SELECT, вложение подзапросов неограниченно. Причем, основной запрос и используемый в нем подзапрос могут обращаться как к одним и тем же таблицам, так и к разным таблицам баз данных. В подзапросе можно использовать почти все опции команды SELECT, кроме опций ORDER BY и INTO.

Подзапрос используется в выражении условия WHERE. Подзапросы дают возможность получать данные из одних таблиц, ориентируясь на условия, содержащиеся в других таблицах. С их помощью можно обойти ограничение на использование агрегатных функций SQL в условии WHERE. Дело в том, что нельзя напрямую использовать агрегатные функции в предложении WHERE, так как предикаты оцениваются в терминах одиночной строки, а агрегатные функ­ции – в терминах групп строк. Подзапросы дают возможность в условии сравнивать значения одного и того же поля таблицы между собой.

При использовании подзапросов необходимо учитывать тип возвращаемого результата подзапроса. Если известно, что оператор SELECT возвращает одно значение, то можно использовать все арифметические операторы сравнения (например, проверку на равенство). Если известно, что оператор SELECT возвращает множество значений, то используется проверка на вхождение (IN) или не вхождение (NOT IN) в множество возвращаемых значений.

Запрос с подзапросом может быть некоррелирован или коррелирован. Некоррелированный подзапрос выполняется в первую очередь, затем полученный результат подставляется в условие и выполняется внешний запрос.

Например, для выборки фамилий сотрудников (поле FAM) из таблицы KADR с заработной платой (поле ZARP) выше средней можно использовать запрос с подзапросом (для таблицы KADR используется локальный псевдоним S1):

SELECT S1.FAM FROM KADR S1 WHERE ZARP >

(SELECT AVG(ZARP) FROM KADR)

В коррелированном подзапросе внутренний запрос ссылается на внешний запрос и выполняется поочередно для каждой строки внешнего запроса (многократно). Выбирается первая строка во внешнем запросе, для нее выполняется внутренний запрос, затем вторая строка и так далее. Такая конструкция выполняется намного дольше, чем запрос с некоррелированным подзапросом.

Например, для выборки фамилий сотрудников (поле FAM) и зарплаты (поле ZARP) из таблицы KADR с заработной платой выше средней по каждой лаборатории можно использовать запрос с подзапросом (для таблицы KADR используются локальные псевдонимы S1 во внешнем запросе и S2 в подзапросе для обеспечения сравнения):

SELECT FAM, ZARP FROM KADR S1 WHERE ZARP >=

(SELECT AVG(ZARP) FROM KADR S2 WHERE S2.LAB=S1.LAB)

В большинстве СУБД в запросах с подзапросами можно использовать логическую операцию EXISTS, соответствующую математическому квантору «Существует». С помощью логической операции EXISTS можно проверить, возвращает ли тот или иной SELECT оператор (подзапрос) какие-то значения. Соответственно, можно в условии определять только те записи, для которых существует (или не существует — NOT EXISTS) какая-то информация.

Какие поля возвращает оператор SELECT внутри логической операции EXISTS неважно. Неважно так же и количество найденных значений. Важен только тот факт, было ли найдено хотя бы одно значение, удовлетворяющее данному оператору SELECT в подзапросе.

Формат логической операции EXISTS: EXISTS (SELECT подзапрос).

Сравнивая операцию EXISTS с операцией IN, можно заметить, что одни и те же запросы можно выразить с помощью разных средств (а именно, EXISTS или IN). Какое из средств выбрать, должно определяться логикой задачи и эффективностью исполнения. Последнее, в свою очередь, зависит от реализации и может принципиально различаться для SQL-серверов разных фирм.

В запросах с подзапросами можно использовать логическую операцию ALL, соответствующую математическому квантору «Для всех». Операция ALL возвращает значение «истина», если указанная перед ALL операция сравнения истинна для каждого значения, возвращаемого оператором SELECT в подзапросе. Можно использовать операцию ANY, которая возвращает значение «истина», если указанная перед ANY операция сравнения истинна хотя бы для одного значения, возвращаемого оператором SELECT в подзапросе. Вместо ключевого слова ANY можно использовать слово SOME.

Во многих СУБД (но не во всех) SQL-команду INSERT можно использовать с подзапросом – вложенной командой SELECT, если множество дополняемых данных является результатом запроса:

Например, дополнение в таблицу STUD1 фамилий студентов (поле FAM) из таблицы STUD2 осуществляется следующей командой:

INSERT INTO STUD1 (FAM) VALUES SELECT DISTINCT FAM FROM STUD2

При модификации данных можно использовать подзапрос – вложенную команду SELECT для формирования операнда условия:

При удалении записей в SQL можно использовать подзапрос – вложенную команду SELECT для формирования операнда условия:

Удаляются записи, удовлетворяющие условию, указанному после опции WHERE.

Результаты работы двух или более операторов SELECT могут быть объединены в одну выборку с помощью операторов UNION или UNION ALL. Оператор UNION, помещенный между двумя операторами SELECT делает из двух выборок одну, причем повторяющиеся записи отсутствуют в результирующей выборке. Результатом будет множество, состоящее из всех строк, входящих в какую-либо выборку или в несколько выборок. Но при этом результаты исходных выборок должны иметь одинаковое число полей (столбцов), тип и ширина i-го поля одной выборки должны совпадать с типом и шириной i-го поля любой другой выборки. При использовании опции UNION часто оказывается полезным включение константы в получаемый результат выборки. Заголовки колонок в выборке определяются первым запросом. Например, текстовую константу можно использовать в качестве поясняющего текста при выборе из таблицы STUD фамилий студентов (поле FAM), получающих стипендию (поле STIP) больше 2000 или проживающих в городе Алматы (город проживания указывается в поле ADRESS):

SELECT FAM AS Фамилии, “стипендия>2000” AS Признак_выборки FROM STUD

WHERE STIP> 2000

SELECT FAM, “ город Алматы ” FROM STUD WHERE ADRESS LIKE “%Алматы”

Оператором UNION можно соединить любое число команд SELECT, но опция ORDER BY в запросе с использованием оператора UNION может входить только в последнее предложение SELECT. При указании критерия упорядочивания указываются номера полей в получаемой выборке. Например, при выборке из таблицы KADR фамилий сотрудников (поле FAM), имеющих заработную плату (поле ZARP) меньше 10000 или проживающих в городе Алматы (город проживания указывается в поле ADRESS), можно сначала упорядочить данные по второй колонке (признак выборки), а затем по первой колонке (фамилии в алфавитном порядке):

Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL