Скачать презентацию 6 Подзапросы Copyright Oracle Corporation 2001 All Скачать презентацию 6 Подзапросы Copyright Oracle Corporation 2001 All

08-Lec.ppt

  • Количество слайдов: 29

6 Подзапросы Copyright © Oracle Corporation, 2001. All rights reserved. 6 Подзапросы Copyright © Oracle Corporation, 2001. All rights reserved.

Что такое подзапрос? Подзапрос –это команда SELECT, включенная в предложение другой команды SQL. Главный Что такое подзапрос? Подзапрос –это команда SELECT, включенная в предложение другой команды SQL. Главный запрос 6 -2 SELECT. . . FROM. . . WHERE. . . (SELECT. . . FROM. . . WHERE. . . ) Copyright © Oracle Corporation, 2001. All rights reserved. Подзапрос

Использование подзапроса для решения проблемы У кого оклад больше, чем у Абеля? Главный запрос: Использование подзапроса для решения проблемы У кого оклад больше, чем у Абеля? Главный запрос: ? У кого из служащих оклад больше, чем у Абеля? Подзапрос: ? 6 -3 Каков оклад у Абеля? Copyright © Oracle Corporation, 2001. All rights reserved.

Синтаксис подзапросов SELECT FROM WHERE • • 6 -4 список_выбора таблица выражение оператор (SELECT Синтаксис подзапросов SELECT FROM WHERE • • 6 -4 список_выбора таблица выражение оператор (SELECT список_выбора FROM таблица); Подзапрос (внутренний запрос) выполняется один раз до главного запроса. Результат подзапроса используется главным запросом (внешним запросом). Copyright © Oracle Corporation, 2001. All rights reserved.

Использование подзапроса SELECT last_name FROM employees 11000 WHERE salary > (SELECT salary FROM employees Использование подзапроса SELECT last_name FROM employees 11000 WHERE salary > (SELECT salary FROM employees WHERE last_name = 'Abel'); 6 -5 Copyright © Oracle Corporation, 2001. All rights reserved.

Указания по использованию подзапросов • • • В однострочных подзапросах используются однострочные операторы. • Указания по использованию подзапросов • • • В однострочных подзапросах используются однострочные операторы. • 6 -6 Подзапрос должен быть заключен в скобки. В многострочных подзапросах используются многострочные операторы. Подзапрос должен находиться справа от оператора сравнения. Copyright © Oracle Corporation, 2001. All rights reserved.

Типы подзапросов • Однострочный подзапрос Главный запрос Подзапрос возвращает ST_CLERK • Многострочный подзапрос Главный Типы подзапросов • Однострочный подзапрос Главный запрос Подзапрос возвращает ST_CLERK • Многострочный подзапрос Главный запрос Подзапрос 6 -7 возвращает ST_CLERK SA_MAN Copyright © Oracle Corporation, 2001. All rights reserved.

Однострочные подзапросы • • Возвращают только одну строку Используют однострочные операторы сравнения Operator Meaning Однострочные подзапросы • • Возвращают только одну строку Используют однострочные операторы сравнения Operator Meaning = > Greater than >= Greater than or equal to < Less than <= Less than or equal to <> 6 -8 Equal to Not equal to Copyright © Oracle Corporation, 2001. All rights reserved.

Выполнение однострочных подзапросов SELECT last_name, job_id, salary FROM employees WHERE job_id = ST_CLERK (SELECT Выполнение однострочных подзапросов SELECT last_name, job_id, salary FROM employees WHERE job_id = ST_CLERK (SELECT job_id FROM employees WHERE employee_id = 141) AND salary > 2600 (SELECT salary FROM employees WHERE employee_id = 143); 6 -9 Copyright © Oracle Corporation, 2001. All rights reserved.

Использование групповых функций в подзапросах SELECT last_name, job_id, salary FROM employees 2500 WHERE salary Использование групповых функций в подзапросах SELECT last_name, job_id, salary FROM employees 2500 WHERE salary = (SELECT MIN(salary) FROM employees); 6 -10 Copyright © Oracle Corporation, 2001. All rights reserved.

Предложение HAVING с подзапросами • • Сервер Oracle сначала выполняет подзапрос Сервер Oracle возвращает Предложение HAVING с подзапросами • • Сервер Oracle сначала выполняет подзапрос Сервер Oracle возвращает результаты в предложение HAVING главного запроса SELECT FROM GROUP BY HAVING 6 -11 department_id, MIN(salary) employees department_id 2500 MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id = 50); Copyright © Oracle Corporation, 2001. All rights reserved.

Что неправильно в этой команде? м SELECT employee_id, last_name оч FROM employees тр WHERE Что неправильно в этой команде? м SELECT employee_id, last_name оч FROM employees тр WHERE salary = ос ог (SELECT MIN(salary) мн FROM employees с р GROUP BY department_id); пе о од п ны то ра й ERROR at line 4: ORA-01427: single-row subquery returns more than ны оч one row р ст о дн О 6 -12 Copyright © Oracle Corporation, 2001. All rights reserved. ро ап з ом с

Будет ли выполнена эта команда? й ени ач SELECT last_name, job_id х зн FROM Будет ли выполнена эта команда? й ени ач SELECT last_name, job_id х зн FROM employees аки WHERE job_id = ник ает (SELECTаjob_id щ р employees FROM озв в не WHERE last_name = 'Haas'); с про за Под no rows selected 6 -13 Copyright © Oracle Corporation, 2001. All rights reserved.

Многострочные подзапросы • • Возвращают более одной строки Используют многострочные операторы сравнения Оператор Значение Многострочные подзапросы • • Возвращают более одной строки Используют многострочные операторы сравнения Оператор Значение IN Равно любому члену списка ANY Сравнение значения с любым значением, возвращаемым подзапросом ALL 6 -14 Сравнение значения с каждым значением, возвращаемым подзапросом Copyright © Oracle Corporation, 2001. All rights reserved.

Использование оператора ANY в многострочных подзапросах SELECT employee_id, last_name, job_id, salary FROM employees 9000, Использование оператора ANY в многострочных подзапросах SELECT employee_id, last_name, job_id, salary FROM employees 9000, 6000, 4200 WHERE salary < ANY (SELECT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG'; 6 -15 Copyright © Oracle Corporation, 2001. All rights reserved.

Использование оператора ALL в многострочных подзапросах SELECT employee_id, last_name, job_id, salary FROM employees 9000, Использование оператора ALL в многострочных подзапросах SELECT employee_id, last_name, job_id, salary FROM employees 9000, 6000, 4200 WHERE salary < ALL (SELECT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG'; 6 -16 Copyright © Oracle Corporation, 2001. All rights reserved.

Неопределенные значения в подзапросе SELECT emp. last_name FROM employees emp WHERE employee_id NOT IN Неопределенные значения в подзапросе SELECT emp. last_name FROM employees emp WHERE employee_id NOT IN (SELECT mgr. manager_id FROM employees mgr); no rows selected 6 -17 Copyright © Oracle Corporation, 2001. All rights reserved.

Итоги • • Проблемы, решаемые с помощью подзапросов. Создавайте подзапросы, когда запрос основан на Итоги • • Проблемы, решаемые с помощью подзапросов. Создавайте подзапросы, когда запрос основан на неизвестных значениях. SELECT FROM WHERE 6 -18 список_выбора таблица выражение оператор (SELECT FROM список_выбора таблица); Copyright © Oracle Corporation, 2001. All rights reserved.

Многостолбцовые подзапросы Главный запрос WHERE (MANAGER_ID, DEPARTMENT_ID) IN Подзапрос 100 102 124 90 60 Многостолбцовые подзапросы Главный запрос WHERE (MANAGER_ID, DEPARTMENT_ID) IN Подзапрос 100 102 124 90 60 50 Каждая строка главного запроса сравнивается с значениями из многострочного и многостолбцового подзапроса 6 -19 Copyright © Oracle Corporation, 2001. All rights reserved.

Сравнения столбцов в многостолбцовом запросе могут быть : • • 6 -20 парными; непарными. Сравнения столбцов в многостолбцовом запросе могут быть : • • 6 -20 парными; непарными. Copyright © Oracle Corporation, 2001. All rights reserved.

Подзапрос с парным сравнением Вывод сведений о сотрудниках, которые одновременно подчиняются такому же менеджеру Подзапрос с парным сравнением Вывод сведений о сотрудниках, которые одновременно подчиняются такому же менеджеру и работают в одном отделе, что и сотрудники с номерами (EMPLOYEE_ID) 178 или 174. SELECT employee_id, manager_id, department_id FROM employees WHERE (manager_id, department_id) IN (SELECT manager_id, department_id FROM employees WHERE employee_id IN (178, 174)) AND employee_id NOT IN (178, 174); 6 -21 Copyright © Oracle Corporation, 2001. All rights reserved.

Подзапрос с непарным сравнением Вывод сведений о сотрудниках, которые подчиняются такому же менеджеру, что Подзапрос с непарным сравнением Вывод сведений о сотрудниках, которые подчиняются такому же менеджеру, что и сотрудники с номерами (EMPLOYEE_ID) 178 или 174, и работают в одном отделе, что и сотрудники с номерами 178 или 174. SELECT FROM WHERE AND 6 -22 employee_id, manager_id, department_id employees manager_id IN (SELECT manager_id FROM employees WHERE employee_id IN (174, 141)) department_id IN (SELECT department_id FROM employees WHERE employee_id IN (174, 141)) employee_id NOT IN(174, 141); Copyright © Oracle Corporation, 2001. All rights reserved.

Использование подзапроса в предложении FROM SELECT FROM WHERE AND 6 -23 a. last_name, a. Использование подзапроса в предложении FROM SELECT FROM WHERE AND 6 -23 a. last_name, a. salary, a. department_id, b. salavg employees a, (SELECT department_id, AVG(salary) salavg FROM employees GROUP BY department_id) b a. department_id = b. department_id a. salary > b. salavg; Copyright © Oracle Corporation, 2001. All rights reserved.

Связанные подзапросы используются при построчной обработке. Каждый подзапрос выполняется один раз для каждой строки Связанные подзапросы используются при построчной обработке. Каждый подзапрос выполняется один раз для каждой строки внешнего запроса. SELECT столбец1, столбец2, . . . FROM таблица 1 outer WHERE столбец1 оператор (SELECT столбец1, столбец2 FROM таблица 2 WHERE выражение 1 = outer. выражение); Подзапрос ссылается на столбец таблицы, из которой выбирает родительский запрос. 6 -24 Copyright © Oracle Corporation, 2001. All rights reserved.

Использование связанных подзапросов Поиск всех сотрудников, зарабатывающих больше среднего оклада по отделу, в котором Использование связанных подзапросов Поиск всех сотрудников, зарабатывающих больше среднего оклада по отделу, в котором они работают. SELECT last_name, salary, department_id FROM employees outer WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = outer. department_id) ; Каждый раз, когда обрабатывается строка внешнего запроса , выполняется внутренний запрос. 6 -25 Copyright © Oracle Corporation, 2001. All rights reserved.

Использование связанных подзапросов Вывод сведений о сотрудниках, которые меняли занимаемую должность хотя бы дважды. Использование связанных подзапросов Вывод сведений о сотрудниках, которые меняли занимаемую должность хотя бы дважды. SELECT e. employee_id, last_name, e. job_id FROM employees e WHERE 2 <= (SELECT COUNT(*) FROM job_history WHERE employee_id = e. employee_id); 6 -26 Copyright © Oracle Corporation, 2001. All rights reserved.

Использование оператора EXISTS • Оператор EXISTS проверяет существование строк в наборе результатов подзапроса. • Использование оператора EXISTS • Оператор EXISTS проверяет существование строк в наборе результатов подзапроса. • Если значение строки подзапроса найдено: – поиск во внутреннем запросе прерывается; – условие отмечается как верное (TRUE). • Если значение строки подзапроса не найдено: – условие отмечается как ложное (FALSE); – поиск во внутреннем запросе продолжается. 6 -27 Copyright © Oracle Corporation, 2001. All rights reserved.

Использование оператора EXISTS Поиск служащих, которым подчиняется хотя бы один служащий. SELECT employee_id, last_name, Использование оператора EXISTS Поиск служащих, которым подчиняется хотя бы один служащий. SELECT employee_id, last_name, job_id, department_id FROM employees outer WHERE EXISTS ( SELECT 'X' FROM employees WHERE manager_id = outer. employee_id); 6 -28 Copyright © Oracle Corporation, 2001. All rights reserved.

Использование оператора NOT EXISTS Поиск отделов, в которых нет служащих. SELECT department_id, department_name FROM Использование оператора NOT EXISTS Поиск отделов, в которых нет служащих. SELECT department_id, department_name FROM departments d WHERE NOT EXISTS (SELECT 'X' FROM employees WHERE department_id = d. department_id); 6 -29 Copyright © Oracle Corporation, 2001. All rights reserved.