Глава 9
ОБЪЕДИНЕНИЕ ТАБЛИЦЫ С СОБОЙ

В ГЛАВЕ 8, МЫ ПОКАЗАЛИ ВАМ КАК ОБЪЕДИНЯТЬ ДВЕ

или более таблиц - вместе.

Достаточно интересно то, что та же самая методика может использова-

ться чтобы объединять вместе две копии одиночной таблицы.

В этой главе, мы будем исследовать этот процесс. Как вы видете, объе-

динение таблицы с самой собой, далеко не простая вещь, и может быть

очень полезным способом определять определенные виды связей между

пунктами данных в конкретной таблице.

========= КАК ДЕЛАТЬ ОБЪЕДИНЕНИЕ ==========

ТАБЛИЦЫ С СОБОЙ ?

Для объединения таблицы с собой, вы можете сделать каждую строку

таблицы, одновременно, и комбинацией ее с собой и комбинацией с

каждой другой строкой таблицы. Вы затем оцениваете каждую комбинацию

в терминах предиката, также как в обьединениях мультитаблиц. Это позво-

лит вам легко создавать определенные виды связей между различными по-

зициями внутри одиночной таблицы - с помощью обнаружения пар строк

со значением поля, например.

Вы можете изобразить обьединение таблицы с собой, как обьединение

двух копий одной и той же таблицы. Таблица на самом деле не копируется,

но SQL выполняет команду так, как если бы это было сделано.

Другими словами, это обьединение - такое же, как и любое другое обьеди-

нение между двумя таблицами, за исключением того, что в данном случае

обе таблицы идентичны.

ПСЕВДОНИМЫ

Синтаксис команды для объединения таблицы с собой, тот же что и для

объединения многочисленых таблиц, в одном экземпляре.

Когда вы объединяете таблицу с собой, все повторяемые имена столбца,

заполняются префиксами имени таблицы. Чтобы ссылаться к этим столб-

цам внутри запроса, вы должны иметь два различных имени для этой

таблицы.

Вы можете сделать это с помощью определения временных имен назы-

ваемых - .переменными диапазона, переменными корреляции или просто

- псевдонимами

Вы определяете их в предложении FROM запроса. Это очень просто: вы

набираете имя таблицы, оставляете пробел, и затем набираете псевдоним

для нее.

Имеется пример который находит все пары заказчиков имеющих один и тот

же самый рейтинг ( вывод показывается в Рисунке 9.1 ):

SELECT first.cname, second.cname, first.rating

FROM Customers first, Customers second

WHERE first.rating = second.rating;

=============== SQL Execution Log ==============

| |

| Giovanni Giovanni 200 |

| Giovanni Liu 200 |

| Liu Giovanni 200 |

| Liu Liu 200 |

| Grass Grass 300 |

| Grass Cisneros 300 |

| Clemens Hoffman 100 |

| Clemens Clemens 100 |

| Clemens Pereira 100 |

| Cisneros Grass 300 |

| Cisneros Cisneros 300 |

| Pereira Hoffman 100 |

| Pereira Clemens 100 |

| Pereira Pereira 100 |

| |

===============================================

Рисунок 9.1: Объединение таблицы с собой

( обратите внимание что на Рисунке 9.1, как и в некоторых дальнейших

примерах, полный запрос не может уместиться в окне вывода, и следо-

вательно будет усекаться. )

В вышеупомянутой команде, SQL ведет себя так, как если бы он соединял

две таблицы называемые 'первая' и 'вторая'. Обе они - фактически, таблицы

Заказчика, но псевдонимы разрешают им быть обработаными независимо.

Псевдонимы первый и второй были установлены в предложении FROM

запроса, сразу после имени копии таблицы.

Обратите внимание что псевдонимы могут использоваться в предложении

SELECT, даже если они не определены в предложении FROM.

Это - очень хорошо. SQL будет сначала допускать любые такие псевдонимы

на веру, но будет отклонять команду если они не определены далее в предло-

жении FROM запроса.

Псевдоним существует - только пока команда выполняется !

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

имеют никакого значения.

Теперь, когда имеются две копии таблицы Заказчиков, чтобы работать с

ними, SQL может обрабатывать эту операцию точно также как и любое

другое обьединение - берет каждую строку из одного псевдонима и срав-

нивает ее с каждой строкой из другого псевдонима.

УСТРАНЕНИЕ ИЗБЫТОЧНОСТИ

Обратите внимание что наш вывод имеет два значение для каждой комби-

нации, причем второй раз в обратном порядке. Это потому, что каждое

значение показано первый раз в каждом псевдониме, и второй раз( сим-

метрично) в предикате.

Следовательно, значение A в псевдониме сначала выбирается в комбина-

ции со значением B во втором псевдониме, а затем значение A во втором

псевдониме выбирается в комбинации со значением B в первом псевдониме.

В нашем примере, Hoffman выбрался вместе с Clemens, а затем Clemens

выбрался вместе с Hoffman. Тот же самый случай с Cisneros и Grass,

Liu и Giovanni, и так далее. Кроме того каждая строка была сравнена

сама с собой, чтобы вывести строки такие как - Liu и Liu.

Простой способ избежать этого состoит в том, чтобы налагать порядок

на два значения, так чтобы один мог быть меньше чем другой или

предшествовал ему в алфавитном порядке. Это делает предикат

ассиметричным, поэтому те же самые значения в обратном порядке не

будут выбраны снова, например:

SELECT tirst.cname, second.cname, first.rating

FROM Customers first, Customers second

WHERE first.rating = second.rating

AND first.cname < second.cname;

Вывод этого запроса показывается в Рисунке 9.2.

Hoffman предшествует Periera в алфавитном порядке, поэтому комбинация

удовлетворяет обеим условиям предиката и появляется в выводе. Когда

та же самая комбинация появляется в обратном порядке - когда Periera

в псевдониме первой таблицы сравнтвается с Hoffman во второй таблице

псевдонима - второе условие не встречается.

Аналогично Hoffman не выбирается при наличии того же рейтинга что и

он сам потому что его имя не предшествует ему самому в алфавитном

порядке. Если бы вы захотели включить сравнение строк с ними же

=============== SQL Execution Log ==============

| |

| SELECT first.cname, second.cname, first.rating |

| FROM Customers first, Customers second |

| WHERE first.rating = second.rating |

| AND first.cname < second.cname |

| =============================================== |

| cname cname rating |

| ------- --------- ------- |

| Hoffman Pereira 100 |

| Giovanni Liu 200 |

| Clemens Hoffman 100 |

| Pereira Pereira 100 |

| Gisneros Grass 300 |

=================================================

Рисунок 9.2: Устранение избыточности вывода в обьединении с собой.

в запросах подобно этому, вы могли бы просто использовать

< = вместо <.

ПРОВЕРКА ОШИБОК

Таким образом мы можем использовать эту особенность SQL для проверки

определенных видов ошибок. При просмотре таблицы Порядков, вы можете

видеть что поля cnum и snum должны иметь постоянную связь.

Так как каждый заказчик должен быть назначен к одному и только одному

продавцу, каждый раз когда определенный номер заказчика появляется в

таблице Порядков, он должен совпадать с таким же номером продавца.

Следующая команда будет определять любые несогласованности в этой

области:

SELECT first.onum, tirst.cnum, first.snum,

second.onum, second.cnum,second.snum

FROM Orders first, Orders second

WHERE first.cnum = second.cnum

AND first.snum < > second.snum;

Хотя это выглядит сложно, логика этой команды достаточно проста.

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

первым псевдонимом, и проверять ее в комбинации с каждой строкой

таблицы Порядков под вторым псевдонимом, одну за другой. Если комби-

нация строк удовлетворяет предикату, она выбирается для вывода. В этом

случае предикат будет рассматривать эту строку, найдет строку где поле

cnum=2008 а поле snum=1007, и затем рассмотрит каждую следующую

строку с тем же самым значением поля cnum. Если он находит что какая

-то из их имеет значение отличное от значения поля snum, предикат

будет верен, и выведет выбранные поля из текущей комбинации строк.

Если же значение snum с данным значением cnum в наш таблице сов-

падает, эта команда не произведет никакого вывода.

БОЛЬШЕ ПСЕВДОНИМОВ

Хотя обьединение таблицы с собой - это первая ситуация когда понятно

что псевдонимы необходимы, вы не ограничены в их использовании что

бы только отличать копию одлной таблицы от ее оригинала. Вы можете

использовать псевдонимы в любое время когда вы хотите создать альтер-

нативные имена для ваших таблиц в команде. Например, если ваши

таблицы имеют очень длинные и сложные имена, вы могли бы определить

простые односимвольные псевдонимы, типа a и b, и использовать их вмес-

то имен таблицы в предложении SELECT и предикате. Они будут также

использоваться с соотнесенными подзапросами(обсуждаемыми в Главе 11).

ЕЩЕ БОЛЬШЕ КОМПЛЕКСНЫХ ОБЪЕДИНЕНИЙ

Вы можете использовать любое число псевдонимов для одной таблицы

в запросе, хотя использование более двух в данном предложении

SELECT * будет излишеством.

Предположим что вы еще не назначили ваших заказчиков к вашему

продавцу. Компании должна назначить каждому продавцу первонача-

льно трех заказчиков, по одному для каждого рейтингового значения.

Вы лично можете решить какого заказчика какому продавцу назначить,

но следующий запрос вы используете чтобы увидеть все возможные

комбинации заказчиков которых вы можете назначать.

( Вывод показывается в Рисунке 9.3 ):

SELECT a.cnum, b.cnum, c.cnum

FROM Customers a, Customers b, Customers c

WHERE a.rating = 100

AND b.rating = 200

AND c.rating = 300;

=============== SQL Execution Log ==============

| |

| AND c.rating = 300; |

| =============================================== |

| cnum cnum cnum |

| ----- ------ ------ |

| 2001 2002 2004 |

| 2001 2002 2008 |

| 2001 2003 2004 |

| 2001 2003 2008 |

| 2006 2002 2004 |

| 2006 2002 2008 |

| 2006 2003 2004 |

| 2006 2003 2008 |

| 2007 2002 2004 |

| 2007 2002 2008 |

| 2007 2003 2004 |

| 2007 2003 2008 |

=================================================

Рисунок 9.3 Комбинация пользователей с различными значениями

рейтинга

Как вы можете видеть, этот запрос находит все комбинации заказчиков с

тремя значениями оценки, поэтому первый столбец состоит из заказчиков

с оценкой 100, второй с 200, и последний с оценкой 300. Они повторяются

во всех возможных комбинациях. Это - сортировка группировки которая

не может быть выполнена с GROUP BY или ORDER BY, поскольку они

сравнивают значения только в одном столбце вывода.

Вы должны также понимать, что не всегда обязательно использовать

каждый псевдоним или таблицу которые упомянуты в предложении FROM

запроса, в предложении SELECT. Иногда, предложение или таблица стано-

вятся запрашиваемыми исключительно потому что они могут вызываться в

предикате запроса. Например, следующий запрос находит всех заказчиков

размещенных в городах где продавец Serres ( snum 1002 ) имеет заказиков

( вывод показывается в Рисунке 9.4 ):

SELECT b.cnum, b.cname

FROM Customers a, Customers b

WHERE a.snum = 1002

AND b.city = a.city;

=============== SQL Execution Log ============

| |

| SELECT b.cnum, b.cname |

| FROM Customers a, Customers b |

| WHERE a.snum = 1002 |

| AND b.city = a.city; |

| ==============================================|

| cnum cname |

| ------ --------- |

| 2003 Liu |

| 2008 Cisneros |

| 2004 Grass |

=============================================

Рисунок 9.4 Нахождение заказчиков в городах относящихся

к Serres.

Псевдоним a будет делать предикат неверным за исключением случая

когда его значение столбца snum = 1002. Таким образом псевдоним

опускает все, кроме заказчиков продавца Serres. Псевдоним b будет

верным для всех строк с тем же самым значением города что и текущее

значение города для a; в ходе запроса, строка псевдонима b будет верна

один раз когда значение города представлено в a.

Нахождение этих строк псевдонима b - единственая цель псевдонима a,

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

собственные заказчики Serres выбираются при нахождении их в том же

самом городе что и он сам, поэтому выбор их из псевдонима a необязате-

лен. Короче говоря, псевдоним назходит строки заказчиков Serres, Liu и

Grass. Псевдоним b находит всех заказчиков размещенных в любом из их

городов ( San Jose и Berlin соответственно ) включая, конечно, самих -

Liu и Grass.

Вы можете также создать обьединение которое включает и различные таб-

лицы и псевдонимы одиночной таблицы. Следующий запрос объединяет

таблицу Пользователей с собой: чтобы найти все пары заказчиков обслужи-

ваемых одним продавцом. В то же самое время, этот запрос объединяет

заказчика с таблицей Продавцов с именем этого продавца ( вывод показан

на Рисунке 9.5 ):

SELECT sname, Salespeople.snum, first.cname

second.cname

FROM Customers first, Customers second, Salespeople

WHERE first.snum = second.snum

AND Salespeople.snum = first.snum

AND first.cnum < second.cnum;

=============== SQL Execution Log ==================

| |

| SELECT cname, Salespeople.snum, first.cname |

| second.cname |

| FROM Customers first, Customers second, Salespeople |

| WHERE first.snum = second.snum |

| AND Salespeople.snum = first.snum |

| AND first.cnum < second.cnum; |

| ====================================================|

| cname snum cname cname |

| ------ ------ -------- -------- |

| Serres 1002 Liu Grass |

| Peel 1001 Hoffman Clemens |

=====================================================

Рисунок 9.5: Объединение таблицы с собой и с другой таблицей

================ РЕЗЮМЕ =================

Теперь Вы понимаете возможности объединения и можете использовать

их для ограничения связей с таблицей, между различными таблицами, или

в обоих случаях. Вы могли видеть некоторые возможности объединения при

использовании его способностей. Вы теперь познакомились с терминами

порядковые переменные, корреляционные переменные и предложения

(эта терминология будет меняться от изделия к изделию, так что мы предла-

гаем Вам познакомится со всеми тремя терминами ). Кроме того Вы поняли,

немного, как в действительности работают запросы.

Следующим шагом после комбинации многочисленых таблиц или многочис-

леных копий одной таблицы в запросе, будет комбинация многочисленных

запросов, где один запрос будет производить вывод который будет затем уп-

равлять работой другого запроса. Это другое мощное средство SQL, о кото-

ром мы расскажем в Главе 10 и более тщательно в последующих главах.

************** РАБОТА С SQL **************

1. Напишите запрос который бы вывел все пары продавцов живущих в

одном и том же городе. Исключите комбинации продавцов с ними же,

а также дубликаты строк выводимых в обратным порядке.

2. Напишите запрос который вывел бы все пары порядков по данным

заказчикам, именам этих заказчиков, и исключал дубликаты из вывода,

как в предыдущем вопросе.

3. Напишите запрос который вывел бы имена(cname) и города(city) всех

заказчиков с такой же оценкой(rating) как у Hoffmanа. Напишите запрос

использующий поле cnum Hoffmanа а не его оценку, так чтобы оно могло

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

( См. Приложение A для ответов. )

---

---