Учебники

Подзапросы для решения запросов

Май 22, 2019

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

Подзапрос может использоваться в любом месте, где разрешено выражение, при условии, что оно возвращает единственное значение. Это означает, что подзапрос, который возвращает одно значение, также может быть указан как объект в списке предложений FROM. Это называется встроенным представлением, потому что когда подзапрос используется как часть предложения FROM, он обрабатывается как виртуальная таблица или представление. Подзапрос может быть размещен либо в предложении FROM, предложении WHERE, либо в предложении HAVING основного запроса.

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

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

( SELECT [DISTINCT] subquery_select_parameter
  FROM {table_name | view_name}
               {table_name | view_name} ...
  [WHERE search_conditions]
  [GROUP BY column_name [,column_name ] ...]
  [HAVING search_conditions] )

Типы подзапросов

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

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

Коррелированный подзапрос : коррелированные подзапросы зависят от данных, предоставляемых внешним запросом. Этот тип подзапроса также включает подзапросы, которые используют оператор EXISTS для проверки существования строк данных, удовлетворяющих заданным критериям.

Однорядный подзапрос

Однострочный подзапрос используется, когда результаты внешнего запроса основаны на одном неизвестном значении. Хотя этот тип запроса формально называется «однорядный», это имя подразумевает, что запрос возвращает несколько столбцов, но только одну строку результатов. Однако однострочный подзапрос может возвращать только один ряд результатов, состоящий только из одного столбца, во внешний запрос.

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

SELECT first_name, salary, department_id
FROM employees
WHERE salary = (SELECT MIN (salary) 
		FROM employees);

Предложение HAVING используется, когда групповые результаты запроса должны быть ограничены на основании некоторого условия. Если результат подзапроса необходимо сравнить с групповой функцией, вы должны вложить внутренний запрос в предложение HAVING внешнего запроса. 

SELECT department_id, MIN (salary)
FROM employees
GROUP BY department_id
HAVING MIN (salary)  < (SELECT AVG (salary)
			FROM employees)

Несколько Строк Sub Query

Многострочные подзапросы — это вложенные запросы, которые могут возвращать более одной строки результатов в родительский запрос. Многострочные подзапросы чаще всего используются в предложениях WHERE и HAVING. Поскольку он возвращает несколько строк, он должен обрабатываться операторами сравнения множеств (IN, ALL, ANY). Оператор IN имеет то же значение, что обсуждалось в предыдущей главе, оператор ANY сравнивает указанное значение с каждым значением, возвращаемым подзапросом, в то время как ALL сравнивает значение с каждым значением, возвращаемым подзапросом.

Приведенный ниже запрос показывает ошибку, когда подзапрос из одной строки возвращает несколько строк. 

SELECT	first_name, department_id
FROM employees
WHERE department_id = (SELECT department_id
			FROM employees
			WHERE LOCATION_ID = 100)
department_id = (select
               *
ERROR at line 4:
ORA-01427: single-row subquery returns more than one row

Использование операторов с несколькими строками

  • [> ALL] Больше, чем самое высокое значение, возвращаемое подзапросом

  • [<ALL] Меньше, чем наименьшее значение, возвращаемое подзапросом

  • [<ANY] Меньше, чем самое высокое значение, возвращаемое подзапросом

  • [> ANY] Больше, чем наименьшее значение, возвращаемое подзапросом

  • [= ANY] Равен любому значению, возвращаемому подзапросом (аналогично IN)

[> ALL] Больше, чем самое высокое значение, возвращаемое подзапросом

[<ALL] Меньше, чем наименьшее значение, возвращаемое подзапросом

[<ANY] Меньше, чем самое высокое значение, возвращаемое подзапросом

[> ANY] Больше, чем наименьшее значение, возвращаемое подзапросом

[= ANY] Равен любому значению, возвращаемому подзапросом (аналогично IN)

Выше SQL можно переписать с помощью оператора IN, как показано ниже. 

SELECT	first_name, department_id
FROM employees
WHERE department_id IN (SELECT department_id
                   	    FROM departments
                   	    WHERE LOCATION_ID = 100)

Обратите внимание, что в приведенном выше запросе IN сопоставляет идентификаторы отделов, возвращенные из подзапроса, сравнивает его с идентификатором основного запроса и возвращает имя сотрудника, удовлетворяющего условию.

Объединение было бы лучшим решением для вышеуказанного запроса, но для иллюстрации в нем использовался подзапрос.

Коррелированный подзапрос

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

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

Рассмотрим приведенный ниже запрос SELECT. В отличие от ранее рассмотренных подзапросов, подзапрос в этом операторе SELECT не может быть разрешен независимо от основного запроса. Обратите внимание, что внешний запрос указывает, что строки выбираются из таблицы сотрудников с псевдонимом e1. Внутренний запрос сравнивает столбец номера отдела сотрудников (DepartmentNumber) таблицы сотрудников с псевдонимом e2 с тем же столбцом для имени таблицы псевдонимов e1. 

SELECT EMPLOYEE_ID, salary, department_id
FROM   employees E
WHERE salary > (SELECT AVG(salary)
                FROM   EMP T
                WHERE E.department_id = T.department_id)

Подзапрос с несколькими столбцами

Подзапрос с несколькими столбцами возвращает более одного столбца во внешний запрос и может быть указан в предложении FROM, WHERE или HAVING внешнего запроса. Например, приведенный ниже запрос показывает исторические данные сотрудника для тех, чья текущая зарплата находится в диапазоне от 1000 до 2000 и работает в отделе 10 или 20. 

SELECT first_name, job_id, salary
FROM emp_history
WHERE (salary, department_id) in (SELECT salary, department_id
				  FROM employees
 				  WHERE salary BETWEEN 1000 and 2000 
				  AND department_id BETWEEN 10 and 20)
ORDER BY first_name;

Когда подзапрос с несколькими столбцами используется в предложении FROM внешнего запроса, он создает временную таблицу, на которую могут ссылаться другие предложения внешнего запроса. Эта временная таблица более формально называется встроенным представлением. Результаты подзапроса обрабатываются как любая другая таблица в предложении FROM. Если временная таблица содержит сгруппированные данные, сгруппированные подмножества обрабатываются как отдельные строки данных в таблице. Рассмотрим предложение FROM в следующем запросе. Встроенное представление, сформированное подзапросом, является источником данных для основного запроса.

120
Share: