Галерея
7757 8119 8300 8698 8817 9504 9722 9937
Интересные записи
Топовые новости
Новое

Инструкция SUBQUERY

Подзапрос (subquery) — это вложенный запрос. Подзапросы могут встречаться в разных частях инструкций SQL.

В SQL поддерживаются следующие типы подзапросов.

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

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

Табличные подзапросы.

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

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

Подзапросы, извлекающие несколько столбцов и несколько строк.

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

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

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

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


Синтаксис SQL 2003

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

SELECT
столбец1,
столбец2,
… (скалярный подзапрос)
FROM таблица!, … (вложенный табличный подзапрос) AS имя_ таблицы подзапроса]
WHERE нечто=(скалярный подзапрос) OR нечто IN (табличный подзапрос)

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

SELECT column1
FROM tablel AS t1
WHERE foo IN (SELECT value1 FROM table2 AS t2 WHERE t2.pk_identifier=t1.pk_identifier)

Обратите внимание, что предложение IN используется только в качестве примера. Можно использовать любой оператор сравнения.


Ключевые слова

скалярный подзапрос

Скалярный подзапрос добавляется в список элементов инструкции SELECT или в предложение WHERE или HAVING запроса.

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

Вложенный табличный подзапрос добавляется только в предложение FROM, при этом также используется предложение AS.

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

Табличный подзапрос добавляется только в предложение WHERE с использованием таких операторов, как IN, ANY, SOME, EXISTS или ALL, которые действуют на несколько значений. Табличные подзапросы возвращают одну или несколько строк, содержащих одно значение.


Общие правила

Подзапросы позволяют получить одно или несколько значений и поместить их в инструкцию SELECT, INSERT, UPDATE или DELETE или в другой подзапрос. Подзапросы можно использовать везде, где разрешено применение выражений. Также подзапросы часто можно заменить инструкциями JOIN. Производительность подзапросов может быть ниже, чем производительность инструкций JOIN (это зависит от платформы).

Подзапросы всегда заключаются в скобки.

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

Скалярные подзапросы могут возвращать только одно значение. Некоторые операторы предложения WHERE принимают только одиночное значение (например,=, >== = AVG (SELECTcolumn 1 FROM salesTable…). Обойти это ограничение можно, если выполнить агрегат в подзапросе, а не во внешнем запросе.


Советы и хитрости программирования

Платформы большинства производителей не позволяют ссылаться на большие типы данных (например, CLOB и BLOB в Oracle и IMAGE и TEXT в SQL Server), а также на типы-массивы (например, TABLE или CURSOR в SQL Server).

Все платформы поддерживают подзапросы, но не все производители поддерживают все типы подзапросов. В 3.6 показаны типы запросов, поддерживаемые разными производителями.

Подзапросы не ограничиваются только инструкциями SELECT. Их можно также использовать в инструкциях INSERT, UPDATE и DELETE, которые содержат предложение WHERE. Подзапросы часто применяются для следующих целей.

  • Для указания строк, вставляемых в целевую таблицу с использованием инструкции INSERT … SELECT, CREATE TABLE … SELECT или SELECT … INTO.
  • Для указания строк представления или материализованного представления в инструкции CREAТЕ VIEW.
  • Для указания значений, связанных с существующими строками при помощи инструкции UPDATE.
  • Для указания значений для условий в предложениях WHERE и HAVING инструкций SELECT, UPDATE и DELETE.
  • Для создания представления таблицы (таблиц) «на ходу» (то есть вложенные табличные подзапросы).

Примеры

В этом разделе приводятся примеры, которые одинаково подходят для DB2, MySQL, Oracle, PostgreSQL и SQL Server.

Ниже показан простой скалярный подзапрос в списке элементов инструкции SELECT.

SELECT job, (SELECT AVG(salary)
FROM employee) AS Avg Sal
FROM employee

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

SELECT
AVG(edlevel),
AVG(salary)
FROM (SELECT edlevel, salary FROM employee) AS emprand
GROUP BY edlevel

Запомните, что на некоторых платформах такой запрос может не выполниться без предложения AS, в котором производной таблице присваивается имя.

В следующем примере показан стандартный табличный подзапрос в составе предложения WHERE. В этом случае нам нужны все номера проектов сотрудников из департамента А00.

SELECT
projno
FROM emp_act
WHERE empno IN (
SELECT
empno
FROM employee
WHERE workdept=’A00′
);

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

В следующем примере мы хотим получить имена сотрудников и уровень их старшинства. Результирующий набор получается при помощи коррелированного подзапроса.

SELECT firstname, lastname, (SELECT COUNTS)
FROM employee senior
WHERE employee.hiredate > senior.hiredate) as senioritype
FROM employee

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

Коррелированные подзапросы зависят от значений, полученных внешним запросом, которые должны быть получены до того, как будет обрабатываться внутренний запрос. Такими запросами сложно овладеть, но они предоставляют уникальные программные возможности. В следующем примере мы получаем информацию о заказах (orders), где количество проданного (quantity) меньше среднего (average) количества других продаж товаров того же наименования (title).

SELECT
s1.ord_num,
s1.title_id,
s1.qty
FROM sales AS s1
WHERE s1.qty (
SELECT
AVG(s2.qty)
FROM sales AS s2
WHERE s2.title_id=s1.title_id
)

Той же самой цели мы можем достигнуть, используя рефлексивное соединение (self-join). Однако бывают ситуации, когда коррелированный подзапрос является единственным способом достижения необходимого результата.

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

UPDATE course SET
ends=(SELECT min(c.begins)
FROM course AS с
WHERE c.oegins BETWEEN course.begins AND course.ends)
WHERE EXISTS
(SELECT *
FROM course AS с
WHERE c.begins BETWEEN course.begins AND course.ends)

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

DELETE
FROM course
WHERE EXISTS
(SELECT * FR0M course AS с
WHERE course.id > c.id AND (course.begins BETWEEN c.begins AND c.ends OR course.ends BETWEEN c, begins AND c.ends))


DB2

Платформа DB2 поддерживает типы подзапросов стандарта ANSI. Разрешается использование скалярных подзапросов в списке элементов инструкции SELECT, вложенных табличных подзапросов — в предложении FROM, а также скалярных и векторных подзапросов в составе предложений WHERE и HAVING. Платформа DB2 позволяет применять коррелированные подзапросы в списке элементов инструкции SELECT и в предложениях WHERE и НА VING.


MySQL

Платформа MySQL поддерживает применение вложенных табличных подзапросов в списках элементов и в предложении.


Oracle

Платформа поддерживает подзапросы стандарта ANSI, хотя применяется другая номенклатура. В Oracle вложенные табличные подзапросы, использующиеся в предложении FROM, называются встроенными представлениями (inline view). И это правильно, поскольку вложенные табличные подзапросы — это, по сути, представления, создаваемые «на ходу». В Oracle подзапросы, которые используются в предложениях WHERE и HAVING, называются вложенными подзапросами (nested subquery). Oracle позволяет использовать коррелированные подзапросы в списке элементов инструкции SELECT и в предложениях WHERE и HAVING.


PostgreSQL

Платформа PostgreSQL поддерживает подзапросы стандарта ANSI в предложениях FROM, WHERE и HAVING. Однако подзапросы в предложении HAVING не могут включать в себя предложения ORDER BY, FOR UPDATE и LIMIT. В настоящее время PostgreSQL не поддерживает подзапросы в списке элементов инструкции SELECT.


SQL Server

Платформа SQL Server поддерживает подзапросы стандарта ANSI. Скалярные подзапросы можно использовать практически везде, где могут использоваться стандартные выражения. Подзапросы SQL Server не могут содержать предложений COMPUTE и FOR BROWSE. Можно использовать предложение ORDER BY, если также используется предложение ТОР.

Источник