Шаг 26 - Снова SELECT - многотабличные запросы

Что ж, мы с вами немного познакомились с объектами Oracle, успели кое-что разобрать и понять. Теперь давайте вернемся к оператору SELECT, так как мы в шаге 19 закончили однотабличные запросы, осталось еще кое-что, а именно многотабличные запросы к БД. Итак, посмотрим на рисунок 1.

26_1.gif (17283 b)

Здесь хорошо видно как строится многотабличный запрос. Я попытался изобразить как можно понятнее. Имеется две таблицы, ORDERS и CUSTOMERS. Формулировка запроса звучит следующим образом. Вывести список всех заказов, включая номер и стоимость заказа, а так же имя клиента и лимит кредита. Как видно, информация, которую необходимо выбрать находится в двух таблицах. Как же произвести выборку из двух таблиц сразу? Достаточно просто! Необходимо ввести запрос следующего вида:

SQL> SELECT ORDER_NUM, AMOUNT, COMPANY, CREDIT_LIMIT
  2  FROM ORDERS, CUSTOMERS
  3  WHERE CUST = CUST_NUM
  4  /						  A
ORDER_NUM     AMOUNT COMPANY                        CREDIT_LIMIT
--------- ---------- ------------------------------ ------------
   112961       31,5 Сметенные огнем                      35,324
   113012      3,745 Рога и копыта                        50,765
   112989      1,458 Перекати поле                        65,887
   113051       1,42 Просто Балбесы                       60,653
   112968      3,978 Большой Бил                          65,345
   113036       22,5 Смешной клоун                        35,645
   113045         45 Апельсин                             50,834
   112963      3,276 Крупное предприятие                  50,455
   113013        652 Просто Балбесы                       60,653
   113058      1,478 Унесенные ветром                     55,323
   112997        652 Пустой Коробок                       40,855
   112983        702 Крупное предприятие                  50,455
   113024        7,1 Безбашенные балбесы                  20,765
   113062       2,43 Пустой Коробок                       40,855
   112979         15 Безбашенные балбесы                  20,765
   113027      4,104 Крупное предприятие                  50,455
   113007      2,925 Апельсин                             50,834
   113069      31,35 Молочная компания                    25,634
   113034        632 Смешной клоун                        35,645
   112922        760 Просто Балбесы                       60,653
   112975        2,1 Рога и копыта                        50,765
   113055        150 Унесенные ветром                     55,323
   113048       3,75 Зашаренные                           50,126
   112993      1,896 Злыдень Карпорейтед                  65,902
   113065       2,13 Злыдень Карпорейтед                  65,902
   113003      5,625 Унесенные ветром                     55,323
   113049        710 Просто Балбесы                       60,653
   112987       27,5 Крупное предприятие                  50,455
   113057     600,34 Рога и копыта                        50,765
   113042       22,5 Волопас супермаркет                  20,923

30 rows selected

Данные получены! Сам запрос, вроде бы не отличается от тех, которые мы строили раньше. Но есть одно отличие, оператор SELECT в предложении FROM содержит не одну, а две таблицы и предложение WHERE сравнивает два столбца из двух разных таблиц, а именно ORDERS.CUST и CUSTOMERS.CUST_NUM! Вот это и называется, если говорить языком формулировок "объеденение двух таблиц по равенству". Надеюсь, все очень хорошо видно из рисунка 1.

Идем дальше, в основном, почти все многотабличные запросы, это запросы типа - предок/потомок. Такие запросы построены на том, что у одной таблицы имеется первичный ключ, а у другой внешний (вторичный) ключ. Сравнивая значение первичного ключа с внешним ключом получаем объединение таблиц по принципу предок/потомок. Посмотрим на Рисунок 2:

26_2.gif (15083 b)

Здесь хорошо видно, что первичный ключ таблицы OFFICES (столбец OFFICE) является предком для внешнего ключа таблицы потомка SALESREPS (столбец REP_OFFICE). Здесь хорошо видно, что значение 12 из предка объемлют четыре значения в таблице потомке. Такие отношения таблиц имеют еще понятие ссылочной целостности и нормализации таблиц, но этого мы еще коснемся позже. А сейчас давайте сформулируем запрос на объединение двух таблиц с использованием отношения предок/потомок. Итак, вывести список всех служащих, включая города и регионы, в которых они работают.

SQL> SELECT NAME, CITY, REGION
  2  FROM SALESREPS, OFFICES
  3  WHERE REP_OFFICE = OFFICE
  4  /

NAME                           CITY                           REGION
------------------------------ ------------------------------ ------------------------------
Вася Пупкин                    Бубурино                       Восток
Маша Распутина                 Красный Мотоцикл               Восток
Филип Киркоров                 Котрогайка                     Запад
Света Разина                   Красный Мотоцикл               Восток
Наташа Королева                Чугуевск                       Восток
Игорь Николаев                 Чугуевск                       Восток
Крис Кельми                    Бубурино                       Восток
Игорь Петров                   Котрогайка                     Запад
Дима Маликов                   Чугуевск                       Восток
Маша Сидорова                  Запиндрищинск                  Запад
Максим Галкин                  Чугуевск                       Восток

11 rows selected

Получаем результирующий запрос(таблицу) с объединением по равенству с использованием отношения предок/потомок. Здесь, сами значения ключей мы не выводим вследствии того, что для результата запроса они не наглядны. Гораздо понятнее названия городов и регионов, тем более, что запоминать цифры, которые им соответствуют нет смысла! :) Как видно, для этих двух таблиц можно сформировать запрос где они поменяются ролями! Используя то же отношение предок потомок, но для столбцов MGR и EMPL_NUM. Дадим такой запрос - вывести список офисов, включая их имена и рапорты! :)

SQL> SELECT CITY, NAME, TITLE
  2  FROM OFFICES, SALESREPS
  3  WHERE MGR = EMPL_NUM
  4  /

CITY                           NAME                           TITLE
------------------------------ ------------------------------ --------------------
Запиндрищинск                  Игорь Петров                   Рапорт продажа
Красный Мотоцикл               Света Разина                   Круто втюхал
Чугуевск                       Наташа Королева                Рапорт пусто
Бубурино                       Вася Пупкин                    Рапорт продажа
Котрогайка                     Игорь Петров                   Рапорт продажа

Получаем результат, только теперь таблицы поменялись ролями! А в остальном, все практически то же самое, что и в предыдущем запросе! Так же при объединении таблиц можно применять все стандартные условия поиска. Например, изменим предыдущий запрос на такое условие - вывести список офисов, включая их имена и рапорты, а так же план продаж, которых превышает $600.00:

SQL> SELECT CITY, NAME, TITLE
  2  FROM OFFICES, SALESREPS
  3  WHERE MGR = EMPL_NUM AND
  4  TARGET > 600.00
  5  /

CITY                           NAME                           TITLE
------------------------------ ------------------------------ --------------------
Чугуевск                       Наташа Королева                Рапорт пусто
Котрогайка                     Игорь Петров                   Рапорт продажа

2 rows selected

Получаем, только две записи, но уже видно, что работает условие поиска. Так же имеется возможность связывать таблицы, когда имеется более чем два связанных столбца в таблицах. Таким образом, к таблицам ORDERS и PRODUCTS, можно дать запрос следующего вида - вывести список всех заказов, в том числе и их стоимость и описание товаров:

SQL> SELECT ORDER_NUM, AMOUNT, DESCRIPTION
  2  FROM ORDERS, PRODUCTS
  3  WHERE MFR = MFR_ID AND PRODUCT = PRODUCT_ID
  4  /
      ORDER_NUM     AMOUNT DESCRIPTION
--------------- ---------- -------------------
         112961       31,5 Доска профильная
         113012      3,745 Рейка деревянная
         112989      1,458 Электродвигатель
         113051       1,42 Труба алюминиевая
         112968      3,978 Рейкя пластмассовая
         113036       22,5 Монитор LG
         113045         45 Телевизор SAMSUNG
         112963      3,276 Рейка пластмассовая
         113013        652 Стекломаст рулоны
         113058      1,478 Стол оффисный
         112997        652 Стекломаст рулоны
         112983        702 Рейкя пластмассовая
         113024        7,1 Труба алюминиевая
         113062       2,43 Электродвигатель
         112979         15 Монитор LG
         113027      4,104 Носки черные
         113007      2,925 Наушники SONY
         113069      31,35 Осветитель ртутный
         113034        632 Бочка металлическая
         112922        760 Носки черные
         112975        2,1 Бочка пластмассовая
         113055        150 Карандаш простой
         113048       3,75 Профиль специальный
         112993      1,896 Бочка металлическая
         113065       2,13 Труба алюминиевая
         113003      5,625 Профиль специальный
         113049        710 Труба алюминиевая
         112987       27,5 Коробка картонная
         113057     600,34 Карандаш простой
         113042       22,5 Телевизор SAMSUNG

30 rows selected

Здесь, хорошо видно, что связь предок/потомок, построена по значениям двух пар столбцов данных таблиц, а именно MFR, MFR_ID и PRODUCT, PRODUCT_ID. Таким, образом, можно составлять достаточно гибкую логику в запросах к двум или более таблицам. Пока с этим все, продолжим в следующий раз.


Предыдущий Шаг | Следующий Шаг | Оглавление
Автор Летучий Сергей.