Sqlвложенный подзапрос с оператором сравнения, отличным от in


Содержание

Организация Web-доступа к базам данных с использованием SQL-запросов (стр. 11 из 22)

SELECT DISTINCT X.ПС

FROM Поставки X

FROM Поставки Y

Здесь X и Y – произвольные псевдонимы таблицы Поставки, определяемые во фразе FROM и используемые как явные уточнители во фразах SELECT и WHERE. Напомним, что псевдонимы определены лишь в пределах одного запроса.

Вложенный подзапрос с оператором сравнения, отличным от IN

Выдать номера поставщиков, находящихся в том же городе, что и поставщик с номером 6.

Результат: SELECT ПС FROM Поставщики WHERE Город = ( SELECT Город FROM Поставщики WHERE ПС = 6 );

В подобных запросах можно использовать и другие операторы сравнения ( X.ПС );

X.ПР 17

Действие этого запроса можно пояснить следующим образом: «Поочередно для каждой строки таблицы Поставки, скажем X, выделить значение номера продукта (ПР), если и только если это значение не входит в некоторую строку, скажем, Y, той же таблицы, а значение столбца номер поставщика (ПС) в строке Y не равно его значению в строке X».

Отметим, что в этой формулировке должен быть использован по крайней мере один псевдоним – либо X, либо Y.

Запросы, использующие EXISTS

Квантор EXISTS (существует) – понятие, заимствованное из формальной логики. В языке SQL предикат с квантором существования представляется выражением EXISTS (SELECT * FROM …).

Такое выражение считается истинным только тогда, когда результат вычисления «SELECT * FROM …» является непустым множеством, т.е. когда существует какая-либо запись в таблице, указанной во фразе FROM подзапроса, которая удовлетворяет условию WHERE подзапроса. (Практически этот подзапрос всегда будет коррелированным множеством.)

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

Результат: SELECT Название FROM Поставщики WHERE EXISTS ( SELECT * FROM Поставки WHERE ПС = Поставщики.ПС AND ПР = 11 );

Название СЫТНЫЙ УРОЖАЙ КОРЮШКА ЛЕТО

Система последовательно выбирает строки таблицы Поставщики, выделяет из них значения столбцов Название и ПС, а затем проверяет, является ли истинным условие существования, т.е. су-ществует ли в таблице Поставки хотя бы одна строка со значением ПР=11 и значением ПС, равным значению ПС, выбранному из таблицы Поставщики. Если условие выполняется, то полученное значение столбца Название включается в результат.

Предположим, что первые значения полей Название и ПС равны, соответственно, ‘СЫТНЫЙ’ и 1. Так как в таблице Поставки есть строка с ПР=11 и ПС=1, то значение ‘СЫТНЫЙ’ должно быть включено в результат.

Хотя этот первый пример только показывает иной способ формулировки запроса для задачи, решаемой и другими путями (с помощью оператора IN или соединения), EXISTS представляет собой одну из наиболее важных возможностей SQL. Фактически любой запрос, который выражается через IN, может быть альтернативным образом сформулирован также с помощью EXISTS. Однако обратное высказывание несправедливо.

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

Результат: SELECT Название, Статус FROM Поставщики WHERE NOT EXISTS ( SELECT * FROM Поставки WHERE ПС = Поставщики.ПС AND ПР = 11 );

Название

Статус ПОРТОС кооператив ШУШАРЫ совхоз ТУЛЬСКИЙ универсам ОГУРЕЧИК ферма

Функции в подзапросе

Теперь, после знакомства с различными формулировками вложенных подзапросов и псевдонимами легче понять текст и алгоритм реализации запроса на получение тех поставщиков продуктов для Сырников, которые поставляют эти продукты за минимальную цену:

SELECT Продукт, Цена, Название, Статус

FROM Продукты, Состав, Блюда, Поставки, Поставщики

WHERE Продукты.ПР = Состав.ПР

AND Состав.БЛ = Блюда.БЛ

AND Поставки.ПР = Состав.ПР

AND Поставки.ПС = Поставщики.ПС

AND Блюдо = ‘Сырники’

AND Цена = ( SELECT MIN(Цена)

FROM Поставки X

WHERE X.ПР = Поставки.ПР );

Естественно, что это коррелированный подзапрос: здесь сначала определяется минимальная цена продукта, входящего в состав Сырников, и только затем выясняется его поставщик.

На этом примере мы закончим знакомство с вложенными подзапросами, предложив попробовать свои силы в составлении ряда запросов, с помощью механизма таких подзапросов:


1. Выдать названия всех мясных блюд.

2. Выдать количество всех блюд, в состав которых входят помидоры.

3. Выдать блюда, продукты для которых поставляются агрофирмой ЛЕТО.

Для SQL это означает, что две таблицы можно объединять тогда и только тогда, когда:

a. они имеют одинаковое число столбцов, например, m;

b. для всех i (i = 1, 2, …, m) i-й столбец первой таблицы и i-й столбец второй таблицы имеют в точности одинаковый тип данных.

Например, выдать названия продуктов, в которых нет жиров, либо входящих в состав блюда с кодом БЛ = 1:

Результат:

Продукт SELECT Продукт FROM Продукты WHERE Жиры = 0 UNION SELECT Продукт FROM Соста WHERE БЛ = 1 Майонез Лук Помидоры Зелень Яблоки Сахар

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

Предложением с UNION можно объединить любое число таблиц (проекций таблиц). Так, к предыдущему запросу можно добавить (перед точкой с запятой) конструкцию

3.3. Вложенные подзапросы

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

Существуют простые и коррелированные вложенные подзапросы. Они включаются в WHERE (HAVING) фразу с помощью условий IN, EXISTS или одного из условий сравнения ( = | <> | | >= ). Простые вложенные подзапросы обрабатываютя системой «снизу вверх». Первым обрабатывается вложенный подзапрос самого нижнего уровня. Множество значений, полученное в результате его выполнения, используется при реализации подзапроса более высокого уровня и т.д.

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

Следует отметить, что SQL обладает большой избыточностью в том смысле, что он часто предоставляет несколько различных способов формулировки одного и того же запроса. Поэтому во многих примерах данной главы будут использованы уже знакомые нам по предыдущей главе концептуальные формулировки запросов. И несмотря на то, что часть из них успешнее реализуется с помощью соединений, здесь все же будут приведены их варианты с использованием вложенных подзапросов. Это связано с необходимостью детального знакомства с созданием и принципом выполнения вложенных подзапросов, так как существует немало задач (особенно на удаление и изменение данных), которые не могут быть реализованы другим способом. Кроме того, разные формулировки одного и того же запроса требуют для своего выполнения различных ресурсов памяти и могут значительно отличаться по времени реализации в разных СУБД.

3.3.2. Простые вложенные подзапросы.

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

SELECT Название, Статус
FROM Поставщики
WHERE ПС IN
( SELECT ПС
FROM Поставки
WHERE ПР = 11 );
Название Статус
СЫТНЫЙ рынок
УРОЖАЙ коопторг
ЛЕТО агрофирма
КОРЮШКА кооператив

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

SELECT Название, Статус
FROM Поставщики
WHERE ПС IN (1, 5, 6, 8);

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

SELECT Название, Статус
FROM Поставщики
WHERE ПС IN
( SELECT ПС
FROM Поставки
WHERE ПР IN
( SELECT ПР
FROM Продукты
WHERE Продукт = ‘Помидоры’ ));

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

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

SELECT Название, Статус
FROM Поставщики, Поставки, Продукты
WHERE Поставщики.ПС = Поставки.ПС
AND Поставки.ПР = Продукты.ПР
AND Продукт = ‘Помидоры’;

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

3.3.3. Использование одной и той же таблицы во внешнем и вложенном подзапросе.

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

Илон Маск рекомендует:  Что такое код xml_set_start_namespace_decl_handler
SELECT DISTINCT ПС
FROM Поставки
WHERE ПР IN
( SELECT ПР
FROM Поставки
WHERE ПС = 6);
ПС
1
3
5
6
8

Отметим, что ссылка на Поставки во вложенном подзапросе означает не то же самое, что ссылка на Поставки во внешнем запросе. В действительности, два имени Поставки обозначают различные значения. Чтобы этот факт стал явным, полезно использовать псевдонимы, например, X и Y:

SELECT DISTINCT X.ПС
FROM Поставки X
WHERE X.ПР IN
( SELECT Y.ПР
FROM Поставки Y
WHERE Y.ПС = 6 );

Здесь X и Y – произвольные псевдонимы таблицы Поставки, определяемые во фразе FROM и используемые как явные уточнители во фразах SELECT и WHERE. Напомним, что псевдонимы определены лишь в пределах одного запроса.

3.3.4. Вложенный подзапрос с оператором сравнения, отличным от IN.

Выдать номера поставщиков, находящихся в том же городе, что и поставщик с номером 6.

SELECT ПС
FROM Поставщики
WHERE Город =
( SELECT Город
FROM Поставщики
WHERE ПС = 6 );


ПС
1
4
6

В подобных запросах можно использовать и другие операторы сравнения (<>, = или >), однако, если вложенный подзапрос возвращает более одного значения и не используется оператор IN, будет возникать ошибка.

3.3.5. Коррелированные вложенные подзапросы.

Выдать название и статус поставщиков продукта с номером 11.

SELECT Название, Статус
FROM Поставщики
WHERE 11 IN
( SELECT ПР
FROM Поставки
WHERE ПС = Поставщики.ПС );

Такой подзапрос отличается от рассмотренного в п.3.3.2 тем, что вложенный подзапрос не может быть обработан прежде, чем будет обрабатываться внешний подзапрос. Это связано с тем, что вложенный подзапрос зависит от значения Поставщики.ПС а оно изменяется по мере того, как система проверяет различные строки таблицы Поставщики. Следовательно, с концептуальной точки зрения обработка осуществляется следующим образом:

Система проверяет первую строку таблицы Поставщики. Предположим, что это строка поставщика с номером 1. Тогда значение Поставщики.ПС будет в данный момент имеет значение, равное 1, и система обрабатывает внутренний запрос

( SELECT ПР
FROM Поставки
WHERE ПС = 1 );

получая в результате множество (9, 11, 12, 15). Теперь система может завершить обработку для поставщика с номером 1. Выборка значений Название и Статус для ПС=1 (СЫТНЫЙ и рынок) будет проведена тогда и только тогда, когда ПР=11 будет принадлежать этому множеству, что, очевидно, справедливо.

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

    Рассмотрим пример использования одной и той же таблицы во внешнем подзапросе и коррелированном вложенном подзапросе.

    Выдать номера всех продуктов, поставляемых только одним по-ставщиком.

    SELECT DISTINCT X.ПР
    FROM Поставки X
    WHERE X.ПР NOT IN
    ( SELECT Y.ПР
    FROM Поставки Y
    WHERE Y.ПС <> X.ПС );
    X.ПР
    17

    Действие этого запроса можно пояснить следующим образом: «Поочередно для каждой строки таблицы Поставки, скажем X, выделить значение номера продукта (ПР), если и только если это значение не входит в некоторую строку, скажем, Y, той же таблицы, а значение столбца номер поставщика (ПС) в строке Y не равно его значению в строке X».

    Отметим, что в этой формулировке должен быть использован по крайней мере один псевдоним — либо X, либо Y.

    3.3.6. Запросы, использующие EXISTS.

    Квантор EXISTS (существует) — понятие, заимствованное из формальной логики. В языке SQL предикат с квантором существования представляется выражением EXISTS (SELECT * FROM . ).

    Такое выражение считается истинным только тогда, когда результат вычисления «SELECT * FROM . » является непустым множеством, т.е. когда существует какая-либо запись в таблице, указанной во фразе FROM подзапроса, которая удовлетворяет условию WHERE подзапроса. (Практически этот подзапрос всегда будет коррелированным множеством.)

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

    SELECT Название
    FROM Поставщики
    WHERE EXISTS
    ( SELECT *
    FROM Поставки
    WHERE ПС = Поставщики.ПС
    AND ПР = 11 );
    Название
    СЫТНЫЙ
    УРОЖАЙ
    КОРЮШКА
    ЛЕТО

    Система последовательно выбирает строки таблицы Поставщики, выделяет из них значения столбцов Название и ПС, а затем проверяет, является ли истинным условие существования, т.е. су-ществует ли в таблице Поставки хотя бы одна строка со значением ПР=11 и значением ПС, равным значению ПС, выбранному из таблицы Поставщики. Если условие выполняется, то полученное значение столбца Название включается в результат.

    Предположим, что первые значения полей Название и ПС равны, соответственно, ‘СЫТНЫЙ’ и 1. Так как в таблице Поставки есть строка с ПР=11 и ПС=1, то значение ‘СЫТНЫЙ’ должно быть включено в результат.

    Хотя этот первый пример только показывает иной способ формулировки запроса для задачи, решаемой и другими путями (с помощью оператора IN или соединения), EXISTS представляет собой одну из наиболее важных возможностей SQL. Фактически любой запрос, который выражается через IN, может быть альтернативным образом сформулирован также с помощью EXISTS. Однако обратное высказывание несправедливо.

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

    SELECT Название, Статус
    FROM Поставщики
    WHERE NOT EXISTS
    ( SELECT *
    FROM Поставки
    WHERE ПС = Поставщики.ПС
    AND ПР = 11 );
    Название Статус
    ПОРТОС кооператив
    ШУШАРЫ совхоз
    ТУЛЬСКИЙ универсам
    ОГУРЕЧИК ферма

    3.3.7. Функции в подзапросе.

    Теперь, после знакомства с различными формулировками вложенных подзапросов и псевдонимами легче понять текст и алгоритм реализации запроса (п. 3.1) на получение тех поставщиков продуктов для Сырников, которые поставляют эти продукты за минимальную цену:

    SELECT Продукт, Цена, Название, Статус
    FROM Продукты, Состав, Блюда, Поставки, Поставщики
    WHERE Продукты.ПР = Состав.ПР
    AND Состав.БЛ = Блюда.БЛ
    AND Поставки.ПР = Состав.ПР
    AND Поставки.ПС = Поставщики.ПС
    AND Блюдо = ‘Сырники’
    AND Цена = ( SELECT MIN(Цена)
    FROM Поставки X
    WHERE X.ПР = Поставки.ПР );

    Естественно, что это коррелированный подзапрос: здесь сначала определяется минимальная цена продукта, входящего в состав Сырников, и только затем выясняется его поставщик.

    На этом примере мы закончим знакомство с вложенными подзапросами, предложив попробовать свои силы в составлении ряда запросов, с помощью механизма таких подзапросов:

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


    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 …

    SQL Подзапросы

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

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

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

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

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

    SQL — Подзапросы

    Дата публикации: 2020-12-13


    От автора: в SQL подзапросы — или внутренние запросы, или вложенные запросы — это запрос внутри другого запроса SQL, который вложен в условие WHERE.

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

    Подзапросы могут использоваться с инструкциями SELECT, INSERT, UPDATE и DELETE вместе с операторами типа =, ,> =,

    Sqlвложенный подзапрос с оператором сравнения, отличным от in

    Выдать номера поставщиков, находящихся в том же городе, что и поставщик с номером 6.

    Результат:
    ПС
    1
    4
    6

    В подобных запросах можно использовать и другие операторы сравнения (<>, = или >), однако, если вложенный подзапрос возвращает более одного значения и не используется оператор IN, будет возникать ошибка.

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

    Подзапросы мы кратко рассмотрели в статье Подзапросы и временные таблицы. Здесь мы более подробно рассмотрим связанный тип подзапросов. Подзапрос называется связанным (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вложенный подзапрос с оператором сравнения, отличным от in

    Выдать номера поставщиков, находящихся в том же городе, что и поставщик с номером 6.

    Результат:
    ПС
    1
    4
    6

    В подобных запросах можно использовать и другие операторы сравнения (<>, = или >), однако, если вложенный подзапрос возвращает более одного значения и не используется оператор IN, будет возникать ошибка.

    Sqlвложенный подзапрос с оператором сравнения, отличным от in

    В выражении SELECT мы можем вводить подзапросы четырьмя способами:

    В условии в выражении WHERE

    В условии в выражении HAVING

    В качестве таблицы для выборки в выражении FROM

    В качестве спецификации столбца в выражении SELECT


    Рассмотрим некоторые из этих случаев. Например, получим все товары, у которых цена выше средней:

    Чтобы получить нужные товары, нам вначале надо выполнить подзапрос на получение средней цены товара: SELECT AVG(Price) FROM Products .

    Оператор IN

    Нередко подзапросы применяются вместе с оператором IN , который выбирает из набора значений. И подзапрос как раз может предоставить требуемый набор значений. Например, выберем все товары из таблицы Products, на которые есть заказы в таблице Orders:

    То есть подзапрос в данном случае выбирает все идентификаторы товаров из Orders, затем по этим идентификаторам извлекаютя товары из Products.

    Добавив оператор NOT , мы можем выбрать те товары, на которые нет заказов в таблице Orders:

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

    Получение набора значений

    При использовании в операторах сравнения подзапросы должны возвращать одно скалярное значение. Но иногда возникает необходимость получить набор значений. Чтобы при использовании в операторах сравнения подзапрос мог возвращать набор значений, перед ним необходимо использовать один из операторов: ALL , SOME или ANY .

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

    Если бы мы в данном случае опустили бы ключевое слово ALL, то мы бы столкнулись с ошибкой.

    Допустим, если данный подзапрос возвращает значения vаl1, val2 и val3, то условие фильтрации фактически было бы аналогично объединению этих значений через оператор AND:

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

    Как работает оператор ALL:

    x > ALL (1, 2) эквивалентно x > 2

    x эквивалентно x

    x = ALL (1, 2) эквивалентно (x = 1) AND (x = 2)

    x <> ALL (1, 2) эквивалентно x NOT IN (1, 2)

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

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

    Как работает оператор ANY (а также SOME):

    x > ANY (1, 2) эквивалентно x > 1

    x эквивалентно x

    x = ANY (1, 2) эквивалентно x NOT (1, 2)

    x <> ANY (1, 2) эквивалентно (x <> 1) OR (x <> 2)

    Подзапрос как спецификация столбца

    Результат подзапроса может представлять отдельный столбец в выборке. Например, выберем все заказы и добавим к ним информацию о названии товара:

    Подзапросы в команде INSERT

    В команде INSERT подзапросы могут применяться для определения значения, которое вставляется в один из столбцов:

    Подзапросы в команде UPDATE

    В команде UPDATE подзапросы могут применяться:

    В качестве устанавливаемого значения после оператора SET

    Как часть условия в выражении WHERE

    Так, увеличим в таблице Orders количество купленных товаров компании Apple на 2:

    Или установим для заказа цену товара, полученную в результате подзапроса:

    Подзапросы в команде DELETE

    В команде DELETE подзапросы также применяются как часть условия. Так, удалим все заказы на Galaxy S8:

    Sqlвложенный подзапрос с оператором сравнения, отличным от in


    Говоря нестрого, подзапрос представляет собой выражение SELECT-FROM-WHERE, которое вложено в другое такое предложение. (Подзапрос может включать также фразу GROUP BY. Однако комбинация ORDER BY и UNION недопустима.) Обычно подзапросы используются для представления множества значений, исследование которых должно осуществляться в каком-либо предикате IN, как иллюстрируется в следующем примере.

    Простой подзапрос

    Выбрать фамилии поставщиков, поставляющих деталь «винт».

    Фамилия
    Блейк
    Кларк

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

    и, следовательно, получаем приведенный ранее результат.
    Неявное уточнение фамилии в этом примере требует дополнительного обсуждения. Заметим, в частности, что «НОМЕР_ПОСТАВЩИКА» слева от IN неявным образом уточняется именем таблицы Поставщики, в то время как «НОМЕР_ПОСТАВЩИКА» в подзапросе неявно уточняется именем таблицы Детали. Справедливо следующее общее правило: предполагается, что неуточненное имя поля должно уточняться именем таблицы (или псевдонимом таблицы), указанным в той фразе FROM, которая является непосредственной частью того же самого запроса или подзапроса. В случае поля НОМЕР_ПОСТАВЩИКА слева от IN этой фразой является «FROM Поставщики», а в случае поля НОМЕР_ПОСТАВЩИКА в подзапросе — это фраза «FROM Детали». Для большей ясности повторим первоначальный запрос с явно указанными предполагаемыми уточнениями:

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

    Коррелированный подзапрос

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

    Пояснение. В последней строке приведенного запроса неуточненная ссылка на НОМЕР_ПОСТАВЩИКА уточняется неявным образом именем таблицы Детали. Другая ссылка явно уточняется именем таблицы Поставщики. Этот пример отличается от предыдущих тем, что внутренний подзапрос не может быть обработан раз навсегда прежде, чем будет обрабатываться внешний запрос, поскольку этот внутренний подзапрос зависит от переменной, а именно от Поставщики.НОМЕР_ПОСТАВЩИКА, значение которой изменяется по мере того, как система проверяет различные строки таблицы Поставщики. Следовательно, с концептуальной точки зрения обработка осуществляется следующим образом.

      Система проверяет первую строку таблицы Поставщики. Предположим, что это строка поставщика 1. Тогда переменная Поставщики.НОМЕР_ПОСТАВЩИКА в данный момент имеет значение 1, и система обрабатывает внутренний запрос

    получая в результате множество (‘гайка’, ‘болт’). Теперь она может завершить обработку для 1 поставщика. Выборка значения ФАМИЛИЯ для 1, а именно Смит, будет произведена тогда и только тогда, когда ‘винт’ принадлежит этому множеству, что, очевидно, несправедливо.

  • Далее система будет повторять обработку такого рода для следующего поставщика и т. д. до тех пор, пока не будут рассмотрены все строки таблицы Поставщики.
  • Такой подзапрос, как в этом примере, называется коррелированным. Коррелированный подзапрос — это такой подзапрос, результат которого зависит от некоторой переменной. Эта переменная принимает свое значение в некотором внешнем запросе. Обработка такого подзапроса, следовательно, должна повторяться для каждого значения переменной в запросе, а не выполняться раз навсегда. Далее будет приведен другой пример коррелированного подзапроса.

    Подзапрос с оператором сравнения, отличным от IN

    Выдать номера поставщиков, находящихся в том же городе, что и поставщик №1.

    Иногда пользователь может знать, что заданный подзапрос должен возвратить в точности одно значение, как в рассматриваемом примере. В таком случае можно использовать вместо обычного IN более простой оператор сравнения (например, =, > и т. д.). Однако, если подзапрос возвращает более одного значения и не используется оператор IN, будет возникать ошибка. Ошибка не возникнет, если подзапрос не возвратит вообще ни одного значения. При этом сравнение интерпретируется в точности так, как если бы подзапрос возвратил неопределенное значение. Более того, подзапрос не может включать фразу GROUP BY, если он используется с простым оператором сравнения, например с =, > и т. д.

    Стандартные функции.

    Хотя и весьма мощное во многих отношениях, предложение SELECT в том виде, как оно было до сих пор описано, остается все еще неадекватным для многих практических задач. Например, даже настолько простои запрос как «Сколько имеется поставщиков?» нельзя выразить, используя только введенные до сих пор конструкции. Для того чтобы усилить его основные возможности по выборке данных, в SQL предусматривается ряд специальных стандартных функций. В настоящее время доступны функции COUNT (число значений), SUM (сумма), AVG (среднее), МАХ (максимум) и MIN (минимум). Кроме специального случая «COUNT (*)» (см. ниже) каждая из этих функций оперирует совокупностью значений в одном столбце некоторой таблицы, возможно, производной, т. е. сконструированной некоторым образом из заданных базовых таблиц, и продуцирует в качестве ее результата единственное значение, определенное следующим образом:

    Для функций SUM и AVG рассматриваемый столбец должен содержать числовые значения. В общем случае аргументу функции может предшествовать ключевое слово DISTINCT (различный), указывающее, что избыточные дублирующие значения должны быть исключены перед тем, как будет применяться функция. Однако для функций МАХ и MIN ключевое слово DISTINCT не имеет отношения к делу и должно быть опущено. Для функции COUNT ключевое слово DISTINCT должно быть специфицировано. Специальная функция COUNT (*), для которой использование DISTINCT не допускается, предусмотрена для подсчета всех строк в таблице без исключения каких-либо дубликатов. Если DISTINCT специфицируется, то аргумент должен состоять только из имени столбца, например Вec. Если DISTINCT не специфицировано, аргумент может представлять собой арифметическое выражение, например Вес*.1000.

    В столбце-аргументе всегда перед применением функции исключаются все неопределенные значения, независимо от того, специфицировано ли DISTINCT, за исключением случая COUNT (*), при котором неопределенные значения обрабатываются точно так же, как и значения, не являющиеся неопределенными. Если оказывается, что аргумент — пустое множество, функция COUNT принимает значение нуль. Все другие функции принимают в этом случае неопределенное значение.

    ФУНКЦИЯ COUNT ВО ФРАЗЕ SELECT

    Выдать общее количество поставщиков

    ФУНКЦИЯ ВО ФРАЗЕ SELECT СО СПЕЦИФИКАЦИЕЙ DISTINCT

    Выдать общее количество поставщиков, поставляющих в настоящее время детали:

    СТАНДАРТНЫЕ ФУНКЦИИ ВО ФРАЗЕ SELECT

      Выдать самую тяжелую деталь

    Результат: 20
    Выдать среднее значение веса деталей

    ИСПОЛЬЗОВАНИЕ ФРАЗЫ GROUP BY

    Оператор GROUP BY (группировать по) перекомпоновывает таблицу, представленную фразой FROM, в разделы или группы таким образом, чтобы в каждой группе все строки имели одно и то же значение поля, указанного во фразе GROUP BY.
    Результат:

    Каждое выражение во фразе SELECT должно принимать единственное значение для группы, т. е. оно может быть либо самим полем, указанным во фразе GROUP BY, либо арифметическим выражением, включающим это поле, либо константой, либо такой функцией, как SUM, которая оперирует всеми значениями данного поля в группе и сводит эти значения к единственному значению. Заметим, что фраза GROUP BY не предполагает ORDER BY (упорядочить по). Также можно использовать фразу Where с Group by.Строки, не удовлетворяющие фразе WHERE, исключаются до того, как будет осуществляться какое-либо группирование.

    Объединение.

    Объединением двух множеств называется множество всех элементов, принадлежащих какому-либо одному или обоим исходным множествам. Поскольку отношение — это множество (множество строк), можно построить объединение двух отношений. Результатом будет множество, состоящее из всех строк, входящих в какое-либо одно или в оба первоначальных отношения. Если, однако, этот результат сам по себе должен быть другим отношением, а не просто разнородной смесью строк, то два исходных отношения должны быть совместимыми по объединению. Нестрого говоря, строки в обоих отношениях должны быть одной и той же «формы». Что касается SQL, то две таблицы совместимы по объединению (и к ним может быть применен оператор UNION) тогда и только тогда, когда:

    1. они имеют одинаковое число столбцов, например, m;
    2. для всех i (i=l,2. m) i-й столбец первой таблицы и i-й столбец второй таблицы имеют в точности одинаковый тип данных:
      • если тип данных — DECIMAL (p, q), то р должно быть одинаковым для обоих столбцов и q должно быть одинаковым для обоих столбцов;
      • если тип данных -CHAR (n), то n должно быть одинаковым для обоих столбцов;
      • если тип данных — VARCHAR (n), то n должно быть одинаковым для обоих столбцов;
      • если NOT NULL специфицировано для какого-либо из этих столбцов, то такая же спецификация должна быть для другого столбца.

    Запрос, требующий использования UNION

    Выдать номера поставщиков, которые проживают в Лондоне или поставляют детали с весом большим 18.

    Из этого простого примера следует несколько соображений:

    • Избыточные дубликаты всегда исключаются из результата UNION. Поэтому, хотя в рассматриваемом примере деталь выбирается обеими из двух составляющих предложений SELECT, в окончательном результате она появляется только один раз.
    • Любое число предложений SELECT может быть соединено операторами UNION.
    • Любая фраза ORDER BY в запросе должна входить как часть только в последнее предложение SELECT и должна указывать столбцы, по которым осуществляется упорядочение, путем указания их порядковых позиций, т. е. их номеров.
    • В связи с оператором UNION часто оказывается полезной возможность включения констант во фразу SELECT. Например, можно указать, какому из двух условий WHERE удовлетворяет каждая из отдельных деталей.
    Понравилась статья? Поделиться с друзьями:
    Кодинг, CSS и SQL