Операторы SELECT могут быть внедрены в тело другого оператора SELECT. Внешний оператор SELECT использует результат выполнения внутреннего оператора для определения содержания окончательного результата всей операции. Внутренние запросы могут находиться в конструкциях WHERE и HAVING внешнего оператора SELECT – в этом случае они получают название подзапросов, или вложенных запросов.
Отметим относительно приводимых ниже примеров, что производители СУБД, как правило, реализуют не весь стандарт ISO, а основные его части, плюс добавляют какие-то свои функции. Соответственно, некоторые из представленных ниже запросов (с NATURAL JOIN, SOME/ANY, ALL) будут части СУБД работать, в части – нет.
Существует 3 типа подзапросов:
- Скалярный подзапрос возвращает значение, выбираемое из пересечения одного столбца с одной строкой, т.е. единственное значение. Скалярный подзапрос может использоваться везде, где требуется указать единственное значение.
- Строковый подзапрос возвращает значения нескольких столбцов таблицы, но в виде единственной строки. Строковый подзапрос может использоваться везде, где применяется конструктор строковых значений.
- Табличный подзапрос возвращает значения одного или нескольких столбцов таблицы, размещенные в более чем одной строке. Табличный подзапрос может использоваться везде, где допускается указывать таблицу, например, как операнд предиката IN.
Пример 1. Составьте список персонала, работающею в отделении компании, расположенном по адресу 463 Main St.
SELECT staffNo, fName, IName, position
FROM Staff
WHERE branchNo =
(SELECT branchNo
FROM Branch
WHERE street = '163 Main St' )
Имеется 2 таблицы – Staff (персонал) и Branch (отделения компании). В таблице Staff есть поле branchNo, являющееся внешним ключом и соответствующим отделению компании, в котором работает сотрудник. Так как в таблице Staff хранится не информация об адресе отделения, а только его идентификатор, внутренний скалярный подзапрос возвращает из таблицы Branch идентификатор подразделения, находящегося по искомому адрес (предполагается, что по адресу располагается только одно подразделение). Внешний запрос выдает информацию о сотрудниках подразделения.
Пример 2. Составьте список всех сотрудников, имеющих зарплату выше средней, указав, насколько их зарплата превышает среднюю зарплату по предприятию.
SELECT staffNo, fName, IName, position,
salary - (SELECT AVG(salary) FROM Staff) AS salDiff
FROM Staff
WHERE salary > (SELECT AVG(salary) FROM Staff ) ;
К подзапросам применяются следующие правила и ограничения:
- В подзапросах не должна использоваться конструкция ORDER BY, хотя она может присутствовать во внешнем операторе SELECT.
- Список выборки SELECT подзапроса должен состоять из имен отдельных столбцов или составленных из них выражений, за исключением случая, когда в подзапросе используется ключевое слово EXISTS.
- По умолчанию имена столбцов в подзапросе относятся к таблице, имя которой указано в конструкции FROM подзапроса. Однако разрешается ссылаться и на столбцы таблицы, указанной в конструкции FROM внешнего запроса, для чего используются уточненные имена столбцов (как описано ниже).
- Если подзапрос является одним из двух операндов, участвующих в операции сравнения, то подзапрос должен указываться в правой части этой операции. Например, приведенный ниже вариант записи запроса из предыдущего примера является некорректным, поскольку подзапрос размещен в левой части операции сравнения со значением столбца salary.
SELECT staffNo, fName, IName, position, salary
FROM Staff
WHERE (SELECT AVG(salary) FROM Staff) < salary
- некорректный вариант
Пример 3. Вложенные подзапросы и использование предиката IN. Составьте перечень сдаваемых в аренду объектов, за которые отвечают работники отделения компании, расположенного по адресу '163 Main st'.
SELECT propertyNo, street, city, postcode, type, rooms, rent
FROM PropertyForRent
WHERE staffNo IN
(SELECT staffNo
FROM Staff
WHERE brancliNo =
(SELECT branchNo
FROM Branch
WHERE street = '163 Main St ' ) )
В данном примере используется еще одна таблица PropertyForRent, ссылающаяся на таблицу Staff поле staffNo.
