Шаг 30 - Снова SELECT - самообъеденения, правила и т.д.

Разберем еще один интересный аспект "самообъединения". Некоторые запросы используют отношения существующие внутри одной таблицы. Например, требуется вывести список имен всех служащих и их руководителей. Вся эта информация содержится в одной таблице SALESREPS. Каждому служащему соответствует одна строка в таблице SALESREPS, а столбец MANAGER содержит идентификатор служащего, являющегося руководителем! Немного запутано? Но не так сложно, как может показаться. Просто столбец MANAGER, это внешний ключ для самой таблицы SALESREPS! Вот и все! Тогда, если пытаться создать запрос вида "первичный ключ - внешний ключ", можно было бы записать примерно так:

SELECT NAME, NAME
	FROM SALESREPS, SALESREPS
	WHERE MANAGER = EMPL_NUM
/

Но здесь есть некоторая глупость! Нельзя дважды ссылаться на одну и ту же таблицу в части FROM! Что ж, тогда можно было бы записать так:

SELECT NAME, NAME
	FROM SALESREPS
	WHERE MANAGER = EMPL_NUM
/

- "Постойте",- скажете вы, но это же однотабличный запрос! И будете правы, действительно он выродился до уровня простого выражения. Здесь получается по условию MANAGER = EMPL_NUM ищутся строки, в которых служащий является своим руководителем, но таких строк нет и по этому запрос ничего не вернет! А вот здесь и напрашивается использовать то, что мы рассматривали в предыдущем шаге. Правильно псевдонимы таблиц! Давайте перепишем наш запрос, вот так:

SELECT EMPS.NAME, MGRS.NAME
	FROM EMPS, MGRS
	WHERE EMPS.MANAGER = MGRS.EMPL_NUM
/

Теперь все верно, только нужно добавить исходную таблицу для псевдонимов, вот так:

SELECT EMPS.NAME, MGRS.NAME
	FROM SALESREPS EMPS, SALESREPS MGRS
	WHERE EMPS.MANAGER = MGRS.EMPL_NUM
/

Вот собственно и получился запрос к двум таблицам, но с небольшим обманом, зато по всем правилам!

30_1.gif (15952 b)

На рисунке хорошо видно как все это получается при использовании псевдонимов столбцов. Кстати можно сделать и так:

SELECT SALESREPS.NAME, MGRS.NAME
	FROM SALESREPS, SALESREPS MGRS
	WHERE SALESREPS.MANAGER = MGRS.EMPL_NUM
/

SQL> SELECT SALESREPS.NAME, MGRS.NAME
  2  	FROM SALESREPS, SALESREPS MGRS
  3  	WHERE SALESREPS.MANAGER = MGRS.EMPL_NUM
  4  /

NAME                           NAME
------------------------------ ------------------------------
Вася Пупкин                    Наташа Королева
Маша Распутина                 Света Разина
Филип Киркоров                 Игорь Петров
Света Разина                   Игорь Петров
Наташа Королева                Света Разина
Игорь Николаев                 Наташа Королева
Крис Кельми                    Игорь Николаев
Игорь Петров                   Света Разина
Дима Маликов                   Наташа Королева
Маша Сидорова                  Игорь Петров
Максим Галкин                  Игорь Петров

11 rows selected

И вот, наконец, получаем результат самообьединения таблицы SALESREPS. В этом запросе я использовал один раз полное имя таблицы, а второй раз ее псевдоним! Собственно вы можете делать как вам нравится! :) Теперь, я приведу более "навороченные примеры самообьединения", а вы просто посмотрите и постарайтесь уяснить как это работает.

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

SELECT SALESREPS.NAME, SALESREPS.QUOTA, MGRS.QUOTA
	FROM SALESREPS, SALESREPS MGRS
	WHERE SALESREPS.MANAGER = MGRS.EMPL_NUM
	AND SALESREPS.QUOTA > MGRS.QUOTA	
/

SQL> SELECT SALESREPS.NAME, SALESREPS.QUOTA, MGRS.QUOTA
  2  	FROM SALESREPS, SALESREPS MGRS
  3  	WHERE SALESREPS.MANAGER = MGRS.EMPL_NUM
  4  	AND SALESREPS.QUOTA > MGRS.QUOTA
  5  /
  
NAME                 QUOTA   QUOTA
------------------ ------- -------
Вася Пупкин            350     200
Маша Распутина         300     275
Игорь Николаев         300     200
Крис Кельми            400     300
Игорь Петров           350     275
Дима Маликов           275     200
Максим Галкин          400     350

7 rows selected

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

А вот, еще один запрос: Вывести список служащих, которые работают со своими руководителями в различных офисах, включая имена и офисы как служащих, так и руководителей.

SELECT EMPS.NAME, EMP_OFFICE.CITY, MGRS.NAME, MGR_OFFICE.CITY
	FROM SALESREPS EMPS, SALESREPS MGRS, OFFICES EMP_OFFICE,
	OFFICES MGR_OFFICE
	WHERE EMPS.REP_OFFICE = EMP_OFFICE.OFFICE
	AND MGRS.REP_OFFICE = MGR_OFFICE.OFFICE
	AND EMPS.MANAGER = MGRS.EMPL_NUM
	AND EMPS.REP_OFFICE <> MGRS.REP_OFFICE
/

Получаем:

SQL> SELECT EMPS.NAME, EMP_OFFICE.CITY, MGRS.NAME, MGR_OFFICE.CITY
  2  	FROM SALESREPS EMPS, SALESREPS MGRS, OFFICES EMP_OFFICE,
  3  	OFFICES MGR_OFFICE
  4  	WHERE EMPS.REP_OFFICE = EMP_OFFICE.OFFICE
  5  	AND MGRS.REP_OFFICE = MGR_OFFICE.OFFICE
  6  	AND EMPS.MANAGER = MGRS.EMPL_NUM
  7  	AND EMPS.REP_OFFICE <> MGRS.REP_OFFICE
  8  /

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

7 rows selected

Снова семь строк и теперь уже четыре столбца, но таблиц уже две! Можете сами придумать что-нибудь по сложнее, это будет полезно! Замечу кстати, что при увеличении количества таблиц в запросе вы все более и более используете ресурсы сервера SQL, следовательно в БД рассчитанных на оперативную обработку транзакций (OLTP) время реакции на такие запросы будет увеличиваться, хотя это уже отдельная тема! :) В конечном итоге объединение представляет собой частный случай более общей комбинации данных из двух таблиц. Это есть ни что иное как "ДЕКАРТОВО ПРОИЗВЕДЕНИЕ" или просто "ПРОИЗВЕДЕНИЕ" таблиц.

Допустим, если дать запрос вида: Показать все возможные комбинации служащих и городов

SELECT NAME, CITY
	FROM SALESREPS, OFFICES
/

SQL> SELECT NAME, CITY
  2  	FROM SALESREPS, OFFICES
  3  /


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

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

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

55 rows selected

Получаем 55 строк, чего и следовало ожидать - 5ть офисов и одиннадцать служащих (5 * 11 = 55). Вот собственно пока и все с многотабличными запросами и с объединениями. Я кое-что пока опускаю, так как мы с этим встретимся позднее и тогда вам будет понятнее то, что мы будем собственно изучать! Осталось совсем немного с оператором SELECT и в скором времени мы приступим к PL/SQL!


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