Язык 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.

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

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

В задании обратим внимание на следующую подфразу "зарплату, максимальную для их должности". Такую величину можно вычислить только с помощью вспомогательного запроса, возвращающего список, состоящий из пар значений "максимальная_зарплата, должность". Прекрасно! Считаем, что уже существует такой список и назовём его бесхитростно 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

Многострочные подзапросы

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

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

• 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

Многострочный подзапрос с оператором сравнения 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

Пример многострочного запроса с оператором сравнения 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.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

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

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

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

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

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