Как делать объединение таблицы с собой?
Для объединения таблицы с собой, вы можете сделать каждую строку таблицы, одновременно, и комбинацией ее с собой и комбинацией с каждой другой строкой таблицы. Вы затем оцениваете каждую комбинацию в терминах предиката, также как в объединениях мультитаблиц. Это позволит вам легко создавать определенные виды связей между различными позициями внутри одиночной таблицы — с помощью обнаружения пар строк со значением поля, например.
Вы можете изобразить объединение таблицы с собой, как объединение двух копий одной и той же таблицы. Таблица на самом деле не копируется, но SQL выполняет команду так, как если бы это было сделано. Другими словами, это объединение — такое же, как и любое другое объединение между двумя таблицами, за исключением того, что в данном случае обе таблицы идентичны.
Псевдонимы
Синтаксис команды для объединения таблицы с собой, тот же что и для объединения многочисленных таблиц, в одном экземпляре. Когда вы объединяете таблицу с собой, все повторяемые имена столбца, заполняются префиксами имени таблицы. Чтобы ссылаться к этим столбцам внутри запроса, вы должны иметь два различных имени для этой таблицы.
Вы можете сделать это с помощью определения временных имен называемых — переменными диапазона, переменными корреляции или просто -псевдонимамиВы определяете их в предложении FROM запроса. Это очень просто: вы набираете имя таблицы, оставляете пробел, и затем набираете псевдоним для нее.
Имеется пример который находит все пары заказчиков имеющих один и тот же рейтинг (вывод показывается в Рисунке 1):
SELECT first.cname, second.cname, first.rating
FROM Customers first, Customers second
WHERE first.rating = second.rating;
| Имя заказчика | Имя заказчика | Рэйтинг |
| Pereira | Hoffman | |
| Clemens | Hoffman | |
| Hoffman | Hoffman | |
| Liu | Giovanni | |
| Giovanni | Giovanni | |
| Liu | Liu | |
| Giovanni | Liu | |
| Cisneros | Grass | |
| Grass | Grass | |
| Pereira | Clemens | |
| Clemens | Clemens | |
| Hoffman | Clemens | |
| Cisneros | Cisneros | |
| Grass | Cisneros | |
| Pereira | Pereira | |
| Clemens | Pereira | |
| Hoffman | Pereira |
Рисунок 1 Объединение таблицы с собой
(обратите внимание что на Рисунке 1, как и в некоторых дальнейших примерах, полный запрос не может уместиться в окне вывода, и следовательно будет усекаться.)
В вышеупомянутой команде, SQL ведет себя так, как если бы он соединял две таблицы называемые ‘первая’ и ‘вторая’. Обе они — фактически, таблицы Заказчика, но псевдонимы разрешают им быть обработанными независимо. Псевдонимы первый и второй были установлены в предложении FROM запроса, сразу после имени копии таблицы. Обратите внимание, что псевдонимы могут использоваться в предложении SELECT, даже если они не определены в предложении FROM.
Это — очень хорошо. SQL будет сначала допускать любые такие псевдонимы на веру, но будет отклонять команду если они не определены далее в предложении FROM запроса.
Псевдоним существует — только пока команда выполняется!
Когда запрос заканчивается, псевдонимы используемые в нем больше не имеют никакого значения.
Теперь, когда имеются две копии таблицы Заказчиков, чтобы работать с ними, SQL может обрабатывать эту операцию точно также как и любое другое объединение — берет каждую строку из одного псевдонима и сравнивает ее с каждой строкой из другого псевдонима.
Устранение избыточности
Обратите внимание, что наш вывод имеет два значение для каждой комбинации, причем второй раз в обратном порядке. Это потому, что каждое значение показано первый раз в каждом псевдониме, и второй раз (симметрично) в предикате. Следовательно, значение A в псевдониме сначала выбирается в комбинации со значением B во втором псевдониме, а затем значение A во втором псевдониме выбирается в комбинации со значением B в первом псевдониме. В нашем примере, Hoffman выбрался вместе с Clemens, а затем Clemens выбрался вместе с Hoffman. Тот же самый случай с Cisneros и Grass, Liu и Giovanni, и так далее. Кроме того каждая строка была сравнена сама с собой, чтобы вывести строки такие как — Liu и Liu. Простой способ избежать этого состоит в том, чтобы налагать порядок на два значения, так чтобы один мог быть меньше чем другой или предшествовал ему в алфавитном порядке. Это делает предикат ассиметричным, поэтому те же самые значения в обратном порядке не будут выбраны снова, например:
SELECT first.cname, second.cname, first.rating
FROM Customers first, Customers second
WHERE first.rating = second.rating
AND first.cnamesecond.cname;
Вывод этого запроса показывается в Рисунке 2.
Hoffman предшествует Periera в алфавитном порядке, поэтому комбинация удовлетворяет обеим условиям предиката и появляется в выводе. Когда та же самая комбинация появляется в обратном порядке — когда Periera в псевдониме первой таблицы сравнивается с Hoffman во второй таблице псевдонима — второе условие не встречается. Аналогично Hoffman не выбирается при наличии того же рейтинга что и он сам потому что его имя не предшествует ему самому в алфавитном порядке. Если бы вы захотели включить сравнение строк с ними же в запросах подобно этому, вы могли бы просто использовать= вместо
| Имя заказчика | Имя заказчика | Рэйтинг |
| Clemens | Hoffman | |
| Giovanni | Liu | |
| Cisneros | Grass | |
| Clemens | Pereira | |
| Hoffman | Pereira |
Рисунок 2 Устранение избыточности вывода в объединении с собой.
Проверка ошибок
Таким образом мы можем использовать эту особенность SQL для проверки определенных видов ошибок. При просмотре таблицы Продажи, вы можете видеть что поля cnum и snum должны иметь постоянную связь. Так как каждый заказчик должен быть назначен к одному и только одному продавцу, каждый раз когда определенный номер заказчика появляется в таблице Продажи, он должен совпадать с таким же номером продавца. Следующая команда будет определять любые несогласованности в этой области:
SELECT first.onum,ftirst.cnum, first.snum, second.onum, second.cnum,second.snum
FROM Orders first, Orders second
WHERE first.cnum = second.cnum
AND first.snumsecond.snum;
| Номер операции | Номер заказчика | Номер продавца | Номер операции | Номер заказчика | Номер продавца |
Хотя это выглядит сложно, логика этой команды достаточно проста. Она будет брать первую строку таблицы Продажи, запоминать ее под первым псевдонимом, и проверять ее в комбинации с каждой строкой таблицы Продажи под вторым псевдонимом, одну за другой. Если комбинация строк удовлетворяет предикату, она выбирается для вывода. В этом случае предикат будет рассматривать эту строку, найдет строку где поле cnum=2008 а поле snum=1007, и затем рассмотрит каждую следующую строку с тем же самым значением поля cnum. Если он находит что какая -то из их имеет значение отличное от значения поля snum, предикат будет верен, и выведет выбранные поля из текущей комбинации строк. Если же значение snum с данным значением cnum в наш таблице совпадает, эта команда не произведет никакого вывода.
Больше псевдонимов
Хотя объединение таблицы с собой — это первая ситуация когда понятно что псевдонимы необходимы, вы не ограничены в их использовании что бы только отличать копию одной таблицы от ее оригинала. Вы можете использовать псевдонимы в любое время когда вы хотите создать альтернативные имена для ваших таблиц в команде. Например, если ваши таблицы имеют очень длинные и сложные имена, вы могли бы определить простые односимвольные псевдонимы, типа a и b, и использовать их вместо имен таблицы в предложении SELECT и предикате.