Подзапросы SQL

Простые скалярные подзапросы

Приведем примеры простых скалярных подзапросов.

Пример 1.
Определить наименования деталей, цена которых больше цены детали ‘болт’.

SELECT dname
FROM D
WHERE dprice > (SELECT dprice
                FROM D
                WHERE dname = ’болт’)

Данный подзапрос относится к скалярным, так как возвращает единственное значение - цену детали болт.

Подзапрос является простым, потому что он может рассматриваться независимо от внешнего запроса. СУБД сначала выполняет подзапрос, в результате чего получает цену детали болт - значение 10, а затем помещает это значение во внешний запрос и выполняет его.

Пример 2.
Определить номера деталей, цена которых меньше средней цены деталей.

SELECT dname
FROM D
WHERE dprice < (SELECT AVG(dprice)
                FROM D)

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

SELECT pnum
FROM PD
WHERE volume = (SELECT min(volume)
                FROM PD)

Пример 4.
Определить номера деталей, которых поставляется больше, чем деталей с номером 2.

SELECT pnum
FROM PD
GROUP BY dnum
HAVING sum(volume) > (SELECT sum(volume)
                      FROM PD
                      WHERE dnum = 2)

Пример 5.
Вывести следующую информацию о деталях: наименование, цена, отклонение от средней цены.

SELECT dname, dprice, dprice - (SELECT AVG(dprice) FROM PD) AS dif
FROM PD

В результате получим таблицу:

Простые табличные подзапросы

Если подзапрос возвращает множество значений, то его результат следует обрабатывать специальным образом. Для этого предназначены операции IN, ANY, SOME и ALL.

Такие операции могут использоваться с подзапросами, возвращающими таблицу, состоящую из одного столбца значений.

Операция IN

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

Пример 6.
Определить наименования поставщиков, которые поставляют детали.

SELECT pname
FROM P
WHERE pnum in (SELECT pnum
               FROM PD)

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

СУБД сначала выполняет подзапрос, в результате чего получает множество номеров поставщиков, которые поставляют детали. Затем СУБД проверяет номер каждого поставщика из таблицы P на принадлежность полученному множеству. При вхождении в множество наименование поставщика помещается в результирующую таблицу.

Пример 7.
Определить наименования поставщиков, которые не поставляют деталь с номером 2.

SELECT pname
FROM P
WHERE pnum not in (SELECT pnum
                   FROM PD
                   WHERE dnum = 2)

Пример 8.
Определить наименования поставщиков, которые поставляют только деталь с номером 1.

SELECT pname
FROM PD
WHERE pnum in (SELECT pnum
               FROM PD
               WHERE dnum = 1) AND pnum not in (SELECT pnum
                                                FROM PD
                                                WHERE dnum <> 1)

Операции ANY, SOME, ALL

Если подзапросу предшествует ключевое слово ANY, то условие сравнения считается выполненным, когда оно выполняется хотя бы для одного из значений, которые получаются после выполнения подзапроса.

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

Если в результате выполнения подзапроса получено пустое множество, то для операции ALL условие сравнения будет считаться выполненным, а для ключевого слова ANY - не выполненным.

Ключевое слово SOME является синонимом ANY и используется для повышения наглядности текстов запросов.

Пример 9.
Определить наименования поставщиков, которые поставляют детали.

SELECT pname
FROM P
WHERE pnum = ANY(SELECT pnum
                 FROM PD)

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

СУБД сначала выполняет подзапрос, в результате чего получает множество номеров поставщиков, которые поставляют детали. Затем СУБД проверяет номер каждого поставщика из таблицы P на равенство хотя бы одному из номеров из полученного множества. При выполнении условия наименование поставщика помещается в результирующую таблицу.

Пример 10.
Определить наименование детали с максимальной ценой.

SELECT dname
FROM D
WHERE dprice >= ALL(SELECT dprice
                    FROM PD)

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

SELECT dname
FROM D
WHERE dprice = (SELECT max(dprice)
               FROM PD)

Сложные табличные подзапросы

Операция EXISTS

Результат выполнения таких операций представляет собой значения TRUE или FALSE.

Для операции EXISTS результат равен TRUE, если в возвращаемой подзапросом таблице присутствует хотя бы одна строка. Если в результирующей таблице подзапроса пуста, то операция EXISTS возвращает значение FALSE. Для операции NOT EXISTS используются обратные правила обработки.

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

Пример 11.
Определить наименования поставщиков, которые поставляют детали.

SELECT pname
FROM P
WHERE EXISTS(SELECT *
             FROM PD
             WHERE PD.pnum = P.pnum)

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

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

Первой выбирается строка с информацией о поставщике Иванов. В подзапрос вместо P.pnum подставляется значение 1 (номер поставщика Иванова), после чего подзапрос выполняется.

Подзапрос возвращает три первых строки из таблицы PD, соответствующие поставкам Иванова, поэтому результат операции EXISTS равен TRUE, и наименование Иванов помещается в результирующую таблицу.

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

Создание самосоединений

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

Пример 12.
Определить наименования поставщиков, которые поставляют и деталь с номером 1, и деталь с номером 2.

Один из вариантов решения задачи можно записать с помощью подзапроса следующим образом.

SELECT pnum
FROM PD
WHERE dnum = 1 AND pnum in (SELECT pnum
                            FROM PD
                            WHERE dnum = 2)

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

SELECT PD.pnum
FROM PD INNER JOIN PD AS PD1 ON PD.pnum = PD1.pnum
WHERE PD.dnum = 1 AND PD1.dnum = 2

Пример 13.
Определить наименования поставщиков, которые поставляют и деталь с номером 1, и деталь с номером 2, и деталь с номером 3.

SELECT PD.pnum
FROM (PD INNER JOIN PD AS PD1 ON PD.pnum=PD1.pnum)
INNER JOIN PD AS PD2 ON PD1.pnum=PD2.pnum
WHERE PD.dnum=1 AND PD1.dnum=2 AND PD2.dnum=3

Резюмирую

Из этой статьи вы узнали что такое подзапрос в SQL. Теперь вы легко отличите скалярный запрос от табличного, и простой запрос от сложного.

Также мы рассмотрели на примерах такие операции, как IN, ANY, SOME и ALL.