Кубанский государственный университет
Опубликован: 24.12.2013 | Доступ: свободный | Студентов: 647 / 5 | Длительность: 24:28:00
Лекция 8:

Язык SQL

8.5.5 Подзапросы

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

Подзапрос может быть вложен:

  • в список фразы FROM; подзапрос готовит промежуточную таблицу, данные которой использует основной запрос;
  • в условия фраз WHERE и HAVING; подзапрос выбирает одну или несколько строк, сравниваемых основным запросом (в том числе используя IN и BETWEEN).

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

Синтаксис запроса с простым подзапросом, включённым во фразу WHERE:

SELECT ...
FROM имя_табл1
WHERE имя сравнение (SELECT ...
FROM имя_табл2
WHERE условие )

Вместо имени во фразе WHERE может использоваться выражение. Подзапросы могут использоваться также в инструкциях INSERT, UPDATE и DELETE.

Рассмотрим приём построения запроса с подзапросом методом нисходящего проектирования.

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

job ename sal
PRESIDENT KING 5,000.00
ANALYST SCOTT 3,000.00
ANALYST FORD 3,000.00
MANAGER JONES 2,975.00
SALESNAN ALLEN 1,600.00
CLERK MILLER 1,300.00

В задании обратим внимание на следующую подфразу "зарплату, максимальную для их должности". Такую величину можно вычислить только с помощью вспомогательного запроса, возвращающего список, состоящий из пар значений "максимальная_зарплата, должность". Прекрасно! Считаем, что уже существует такой список и назовём его бесхитростно LIST. Его формат:

LIST=(MAX1(sal),    job1, ...)

Теперь можно написать основной запрос:

SELECT   job, ename, sal FROM emp
WHERE     (sal,   job)   IN LIST ORDER    BY    sal DESC;

Запрос, получающий список LIST


SELECT    MAX(sal), job
FROM emp GROUP   BY job;

Остается вставить этот запрос в предыдущий запрос вместо заглушки LIST. К сожалению, в Cache такой подход напрямую реализовать нельзя, поскольку оператор IN может работать только со скалярными элементами. Однако, используя функцию CONCAT, объединяющую два поля в одну строку, легко получить эквивалентный запрос:

SELECT job, ename, sal
FROM emp
WHERE  {fn CONCAT(sal,job)} IN
(SELECT {fn CONCAT(MAX(sal),job)} FROM emp GROUP BY job) ORDER BY sal DESC
Однострочные подзапросы

Однострочный подзапрос возвращает ровно одну строку. С однострочными подзапросами используются однострочные операторы сравнения: >, =, >=, <, <>, <=.

Пример однострочного подзапроса приведён в листинге 8.12.

Обязательно запишите задание, по которому составлен этот запрос.

SELECT ename, job, sal
FROM emp
WHERE mgr = (SELECT empno FROM emp
WHERE ename='BLAKE')
AND sal < (SELECT sal
FROM emp
WHERE empno=7844)
ename job sal
WARD SALESMAN 1250
MARTIN SALESMAN 1250
JAMES CLERK 950
Пример 8.12. Пример однострочного запроса
Многострочные подзапросы

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

Операторы сравнения для многострочных подзапросов:

  • IN (подзапрос) — равенство любому из значений; можно понимать так: "находится в списке, полученном подзапросом";
  • ANY/SOME (подзапрос) — сравнение выполняется хотя бы для одного значения из списка, полученного подзапросом;
  • ALL (подзапрос) — сравнение верно для всех значений;
  • EXISTS (подзапрос) — значение существует в списке, полученном подзапросом;
  • NOT EXISTS (подзапрос) —значение не существует в списке, полученном подзапросом.

Пример многострочного подзапроса с оператором сравнения IN:

SELECT ename, sal, deptno FROM emp
WHERE sal IN (SELECT MIN(sal) FROM emp GROUP BY deptno)

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

Примеры многострочных подзапросов

В листинге 8.13 приведен пример многострочного подзапроса с оператором сравнения ANY. Сравнение "<ANY"(меньше хотя бы одного из значений) эквивалентно сравнению "меньше максимального значения".

SELECT ename, job, sal
FROM emp
WHERE sal < ANY (SELECT sal FROM emp
WHERE job='SALESMAN')
AND job<>'ANALYST'
ename job sal
SMITH CLERK 800
WARD SALESMAN 1250
MARTIN SALESMAN 1250
TURNER SALESMAN 1500
ADAMS CLERK 1100
JAMES CLERK 950
MILLER CLERK 1300
Пример 8.13. Пример многострочного запроса

Многострочный подзапрос с оператором сравнения ALL приведён в листинге 8.14. Сравнение "< ALL" (меньше всех значений)" эквивалентно сравнению "меньше минимального значения".

SELECT ename, job, sal
FROM emp
WHERE sal < ALL (SELECT sal FROM emp
WHERE job='MANAGER')
AND job <> 'CLERK'
ename job sal
ALLEN SALESMAN 1600
WARD SALESMAN 1250
MARTIN SALESMAN 1250
TURNER SALESMAN 1500
Пример 8.14. Пример многострочного запроса с оператором ALL

Пример многострочного запроса с оператором сравнения EXISTS приведён ниже. В поиске людей принятых на работу одновременно с Джеймсом мы немного переусердствовали с псевдонимами (e2 можно было не задавать)

 
SELECT ename, job, sal,hiredate
FROM emp AS e1
WHERE EXISTS (SELECT * FROM emp e2
WHERE e1.hiredate=e2.hiredate AND ename='JAMES')
ename job sal hiredate
JAMES CLERK 950 1981-12-03
FORD ANALYST 3000 1981-12-03
Пример 8.15. Пример много строчного запроса с оператором EXISTS
Коррелированные подзапросы

Обычный подзапрос выполняется первым, внешний запрос вторым. Коррелированными называются подзапросы, выполняющиеся для каждой строки-кандидата из внешнего запроса (рисунок 8.4).

Процесс выполнения коррелированного запроса

Рис. 8.4. Процесс выполнения коррелированного запроса

Отсюда вытекает необходимый признак: коррелированный подзапрос содержит столбец из внешнего запроса.

Пример коррелированного подзапроса: Найти всех работников, которые получают зарплату выше средней в своем отделе:

SELECT ename, sal salary, deptno FROM emp e
WHERE sal >   (SELECT AVG(sal)   FROM emp WHERE deptno=e.deptno)
ORDER BY deptno

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

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

ename salary deptno
KING 5000 10
JONES 2975 20
SCOTT 3000 20
FORD 3000 20
ALLEN 1600 30
BLAKE 2850 30

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

SELECT ename, sal salary, deptno FROM emp e
WHERE sal > средняя_заработная_плата(отдел) ORDER BY deptno

Псевдоним e для emp подставлен для использования в будущем подзапросе. Мы ведь знаем, что

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

Остается написать подзапрос, вычисляющий среднюю_заработную_плату для отдела с номером e.deptno, определенным внешним запросом

SELECT AVG(sal) FROM emp
WHERE deptno=e.deptno

Остаётся заменить им заглушку во внешнем запросе.

Во многих СУБД коррелированные подзапросы можно размещать, вопреки традиции и синтаксису, во фразе SELECT. Приведём в качестве примеров, такой совсем неумно составленный, но выполняющийся и в Oracle и в Cache, запрос

SELECT ename,
(SELECT job FROM emp where ename=e1.ename) JOB FROM emp e1

Конечно, последний пример не следует расценивать как призыв, писать не стандартно.