9. ОБЪЕДИНЕНИЕ ТАБЛИЦЫ С СОБОЙ

В ГЛАВЕ 8, МЫ ПОКАЗАЛИ ВАМ КАК ОБЪЕДИНЯТЬ ДВЕ или более таблиц вместе.
Достаточно интересно то, что та же самая методика может использоваться чтобы объединять вместе две копии одиночной таблицы. В этой главе, мы будем исследовать этот процесс. Как вы видете, объединение таблицы с самой собой, далеко не простая вещь, и может быть очень полезным способом определять определенные виды связей между пунктами данных в конкретной таблице.

КАК ДЕЛАТЬ ОБЪЕДИНЕНИЕ ТАБЛИЦЫ С СОБОЙ ?

Для объединения таблицы с собой, вы можете сделать каждую строку таблицы, одновременно, и комбинацией ее с собой и комбинацией с каждой другой строкой таблицы. Вы затем оцениваете каждую комбинацию в терминах предиката, также как в обьединениях мультитаблиц. Это позволит вам легко создавать определенные виды связей между различными позициями внутри одиночной таблицы - с помощью обнаружения пар строк со значением поля, например.

Вы можете изобразить обьединение таблицы с собой, как обьединение двух копий одной и той же таблицы. Таблица на самом деле не копируется, но SQL выполняет команду так, как если бы это было сделано. Другими словами, это обьединение - такое же, как и любое другое обьединение между двумя таблицами, за исключением того, что в данном случае обе таблицы идентичны.

ПСЕВДОНИМЫ

Синтаксис команды для объединения таблицы с собой, тот же что и для объединения многочисленых таблиц, в одном экземпляре. Когда вы объединяете таблицу с собой, все повторяемые имена столбца, заполняются префиксами имени таблицы. Чтобы ссылаться к этим столбцам внутри запроса, вы должны иметь два различных имени для этой таблицы.

Вы можете сделать это с помощью определения временных имен называемых переменными диапазона, переменными корреляции или просто - псевдонимами. Вы определяете их в предложении FROM запроса. Это очень просто: вы набираете имя таблицы, оставляете пробел, и затем набираете псевдоним для нее. Имеется пример который находит все пары заказчиков имеющих один и тот же самый рейтинг (вывод показывается в Таблице 9.1):

SELECT first.cname, second.cname, first.rating
FROM Customers first, Customers second
WHERE first.rating=second.rating;

Таблица 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 не выбирается при наличии того же рейтинга что и он сам потому что его имя не предшествует ему самому в алфавитном порядке. Если бы вы захотели

Таблица 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;

Таблица 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;

Таблица 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;

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

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

Теперь Вы понимаете возможности объединения и можете использовать их для ограничения связей с таблицей, между различными таблицами, или в обоих случаях. Вы могли видеть некоторые возможности объединения при использовании его способностей. Вы теперь познакомились с терминами порядковые переменные, корреляционные переменные и предложения (эта терминология будет меняться от изделия к изделию, так что мы предлагаем Вам познакомится со всеми тремя терминами ). Кроме того Вы поняли, немного, как в действительности работают запросы.

Следующим шагом после комбинации многочисленых таблиц или многочисленых копий одной таблицы в запросе, будет комбинация многочисленных запросов, где один запрос будет производить вывод который будет затем управлять работой другого запроса. Это другое мощное средство SQL, о котором мы расскажем в Главе 10 и более тщательно в последующих главах.

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

Напишите запрос который бы вывел все пары продавцов живущих в одном и том же городе. Исключите комбинации продавцов с ними же, а также дубликаты строк выводимых в обратным порядке.

1. Напишите запрос который вывел бы все пары порядков по данным заказчикам, именам этих заказчиков, и исключал дубликаты из вывода, как в предыдущем вопросе.
2. Напишите запрос который вывел бы имена(cname) и города(city) всех заказчиков с такой же оценкой(rating) как у Hoffmanа. Напишите запрос использующий поле cnum Hoffmanа а не его оценку, так чтобы оно могло быть использовано если его оценка вдруг изменится.