Подзапросы (subqueries
) реализуются в запросах, обработка которых должна выполняться за несколько шагов, и конечный результат в которых зависит от результатов выполнения какого-то дочернего запроса или подзапроса. Подзапрос, который размещается в операторе внутри конструкции WHERE
, называется вложенным (nested subquery
).
Анализ с получением n-го количества первых показателей (Top-N Analysis)
Следующий запрос предусматривает получение данных о десяти первых сотрудниках с точки зрения их зарплаты. Точно так же легко можно извлечь данные и о десяти последних с точки зрения зарплаты сотрудников, просто применив вместо конструкции ORDER BY DESC
конструкцию ORDER BY
.
SQL> SELECT emp_id, emp_name, job, manager, salary FROM (SELECT emp_id, emp_name, job, manager, salary, RANK() OVER (ORDER BY SALARY DESC NULLS LAST) AS Employee_Rank FROM employees ORDER BY SALARY DESC NULLS LAST) WHERE employee_Rank < 5;
Подзапросы могут представлять собой как однострочные, так многострочные операторы SQL. Давайте вкратце рассмотрим каждый из этих видов подзапросов.
Однострочные подзапросы
Однострочные подзапросы (one-row subquery) полезны при необходимости получения в рамках главного запроса ответа на вопрос на основе пока что неизвестных значений, например: “У каких сотрудников зарплата выше, чем у сотрудника с идентификационным номером 9999?”. Для получения ответа на этот вопрос сначала (однократно) может выполняться подзапрос или внутренний запрос, результаты которого далее используются в основном или внешнем запросе. Ниже приведен пример однострочного запроса:
SQL> SELECT first_name||last_name, dept 2 FROM employee 3 WHERE sal > 4 (SELECT sal 5 FROM emp 6 WHERE empno= 9999);
Многострочные запросы
Многострочный подзапрос (multiple-row subquery) предусматривает возврат множества строк в выводе, поэтому вместе с ним необходимо применять многострочные операции сравнения наподобие IN, ANY
и ALL
. В случае использования с ним однострочной операции Oracle будет выдавать такую довольно распространенную ошибку:
ERROR: ORA-01427: single-row subquery returns more than one row однострочный подзапрос возвращает более одной строки
Многостолбцовые подзапросы
Многостолбцовыми подзапросами (multiple-column subqueries
) называются запросы, в которых внутренний запрос извлекает значения более чем одного столбца. Строки, возвращаемые такими подзапросами, далее вычисляются в основном запросе путем сравнения соответствующих пар, столбцов со столбцами и строк со строками.
Усовершенствованные подзапросы
Коррелированные подзапросы (correlated subqueries) являются сложнее обычных и позволяют получать ответы на вопросы вроде: “Как выглядят имена все тех сотрудников, которые имеют зарплату ниже средней в своем отделе?”. Показатель средней зарплаты может вычисляться во внутреннем запросе, а информация о сотрудниках получаться во внешнем или основном запросе. Однако этот внутренний запрос должен обязательно выполняться для каждого сотрудника в основном (внешнем) запросе, потому что средние показатели отделов будут зависеть от номера отдела сотрудника во внешнем запросе.
Операции EXISTS и NOT EXISTS
Операция EXISTS
позволяет выполнять проверку на предмет существования строк во внутреннем запросе или подзапросе при использовании подзапросов, а операция NOT EXISTS
— на предмет не существования строк во внутреннем запросе. В приведенном ниже операторе операция EXISTS
вернет TRUE
в случае извлечения подзапросом хотя бы одной строки:
SQL> SELECT department_id FROM departments d WHERE EXISTS (SELECT * FROM employees e WHERE d.department_id = e.department_id);