Подзапросы

Подзапросы, которые возвращают одну запись

Пример 1.

 SELECT first_name, last_name, salary
 FROM emp
 WHERE salary =
  (SELECT MIN(salary) FROM emp);

 FIRST_NAME                LAST_NAME                 SALARY
 ------------------------- ------------------------- ----------
 Chad                      Newman                    750

Комментарий:

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

• Определяется результат подзапроса и сохраняется во временном буфере.
• Запрос проверяется на соответствие результата подзапроса.
• Если результат положительный, то данные включены в конечный результат. В противном случае отбрасываются.

Пример 2.

 SELECT first_name, last_name, salary
 FROM emp
 WHERE salary =
  (SELECT MIN(salary)
   FROM emp
   WHERE title = 'Warehouse Manager');

 FIRST_NAME                LAST_NAME                 SALARY
 ------------------------- ------------------------- ----------
 Ben                       Biri                      1100

Комментарий:

В результатах отображается данные работника, зарабатывающего меньше всего на должности "Warehouse Manager".

Подзапросы, которые возвращают более одной записи

Пример 3.

 SELECT first_name, last_name, salary, title
 FROM emp
 WHERE (title, salary) IN
 (SELECT title, MIN(salary)
 FROM emp GROUP BY title);

 FIRST_NAME      LAST_NAME            SALARY   TITLE
 -------------   -------------   -----------   -------------------
 Carmen          Velasquez              2500   President 
 Colin           Magee                  1400   Sales Representative
 Chad            Newman                  750   Stock Clerk 
 Audry           Ropeburn               1550   VP, Administration
 Mark            Quick-To-See           1450   VP, Finance 
 LaDoris         Ngao                   1450   VP,  Operations
 Midori          Nagayama               1400   VP, Sales 
 Ben             Biri                   1100   Warehouse Manager

Комментарий:

В результатах отображается данные сотрудников, которые зарабатывают меньше всего на следующих должностях. Значительным, здесь является предложение GROUP BY. Поскольку подзапрос возвращает более одной записи в предложении WHERE необходимо использовать оператор IN. Использование оператора сравнения = вызовет ошибку ORA-01427.

Пример 4.

 SELECT first_name, last_name, salary
 FROM emp
 WHERE salary < (SELECT AVG(salary) FROM emp);

 FIRST_NAME                LAST_NAME                 SALARY
 ------------------------- ------------------------- ----------
 Molly                     Urguhart                  1200
 Roberta                   Menchu                    1250
 Ben                       Biri                      1100
 George                    Smith                     940
 Akira                     Nozaki                    1200
 Vikram                    Palet                     795
 Chad                      Newman                    750
 Alexander                 Markarian                 850
 Eddie                     Chang                     800
 Radha                     Palet                     795
 Bela                      Dancs                     860
 Sylvie                    Schwartz                  1100

Комментарий:

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

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

Пример 5.

 SELECT first_name, last_name, salary, title
 FROM emp E1
 WHERE salary < (SELECT AVG(salary)
 FROM emp E2
 WHERE E2.title = E1.title)
 ORDER BY title, salary;

 FIRST_NAME      LAST_NAME            SALARY TITLE
 --------------- ---------------- ---------- ----------------------
 Colin           Magee                  1400 Sales Representative
 Andre           Dumas                  1450 Sales Representative
 Chad            Newman                  750 Stock Clerk
 Vikram          Patel                   795 Stock Clerk
 Radha           Patel                   795 Stock Clerk
 Eddie           Chang                   800 Stock Clerk
 Alexander       Markarian               850 Stock Clerk
 Bela            Dancs                   860 Stock Clerk
 George          Smith                   940 Stock Clerk
 Ben             Biri                   1100 Warehouse Manager
 Molly           Urguhart               1200 Warehouse Manager

Для проверки напишем:

 SELECT AVG(salary), title FROM emp GROUP BY title;

 AVG(SALARY) TITLE
 ----------- -------------------------
 2500        President
 1476        Sales Representative
 949         Stock Clerk
 1550        VP, Administration
 1450        VP, Finance
 1450        VP, Operations
 1400        VP, Sales
 1231,4      Warehouse Manager

Комментарий:

В результатах отображается данные сотрудников, которые зарабатывают меньше средней заработной платы по занимаемой должности (поле TITLE). Используя вложенный запрос, он сначала вычисляется, а результаты временно сохраняются во временных буферах. Условие в основном запросе проверяется с результатом подзапроса. Строки включены в результат, если они удовлетворяют условию подзапроса.

[NOT] EXIST

Пример 6.

 SELECT id, first_name, last_name
 FROM emp E 
 WHERE EXISTS
 (SELECT 1 FROM ord O 
  WHERE O.sales_rep_id = E.id);

 ID         FIRST_NAME                LAST_NAME
 ---------- ------------------------- -------------------------
 11         Colin                     Magee
 12         Henry                     Giljum
 13         Yasmin                    Sedeghi
 14         Mai                       Nguyen
 15         Andre                     Dumas

Комментарий:

Выражение логического оператора EXIST является истинним, если результат подзапроса будет возвращать по крайней мере одну запись. В противном случае оно ложно. Оператор NOT EXIST идет вразрез с EXIST. В данном примере отображается данные сотрудников, которые хотя-бы раз "опекались" заказом сделаным через клиента (то есть, их идентификационный номер ID встречается, по крайней мере один раз в таблице ORD). Для проверки мы можем сделать следующий запрос:

 SELECT sales_rep_id, COUNT(*) FROM ord GROUP BY sales_rep_id;

 SALES_REP_ID COUNT(*)
 ------------ ----------
 11           5
 12           3
 13           1
 14           3
 15           4

Пример 7.

 SELECT name FROM product P
 WHERE NOT EXISTS
 (SELECT * FROM item I
  WHERE I.product_id = P.id);

 NAME
 --------------------------------------------------
 Prostar 20 Pound Weight
 Prostar 50 Pound Weight

Комментарий:
Как и в предыдущем примере. Отображаем названия продуктов, которые не появились ни в одном заказе.