Материал: Graber_Martin_-_SQL_dlya_prostykh_smertnykh_-_2014

Глава 9. Операция соединения, операнды которой представлены од11ой таблицей

В главе 8 было показано, как соединить две или более таблицы. Аналогичную

технику можно применить для соединения двух копий одной таблицы. В настоящей главе эта процедура рассматривается подробно. Она не является простым "самонало­ жением", а весьма полезно для выявления определенных видов связей между элемен­ тами данных в конкретной таблице.

Как выполняется операция соединения двух копий одной таблицы

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

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

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

ем поля.

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

идентичны.

Алиасы

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

Приведем пример поиска всех пар продавцов, имеющих одинаковый рейтинг (вы­ ходные данные представлены на рис. 9.l ):

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

FROM Customers first, Customers second

WHERE first.rating = second. rating;

84

Как выполняется операция соединения двух ко1111й одной таблицы

Рис. 9.1. Соединение таблицы по принципу "сама с собой"

(заметим, что на рис. 9.1 , как и в последующих примерах, видна только часть вы­

ходных данных запроса, поскольку реально все они в пределах одного окна не уме­

щаются).

В приведенном примере команды SQL ведет себя так, как будто в операции со­ единения участвуют две таблицы, называемые "first" (первая) и "secoпd" (вторая). Обе они в действительности являются таблицей Customers, но алиасы позволяют рассматривать ее как две независимые таблицы. Алиасы first и second были опреде­ лены в предложении запроса FROM непосредственно за именем таблицы. Алиасы применяются также в предложении SELECT, несмотря на то, что они не определены вплоть до предложения FROM. Это совершенно оправдано. SQL сначала примет какой-либо из таких алиасов на веру, но затем отвергнет команду, если в предложе­ нии FROM запроса алиасы не определены. Время жизни алиаса зависит от времени

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

ют свои значения.

Получив две копии таблицы Customers для работы, SQL выпо.1няет операцию JOIN, как для двух разных таблиц: выбирает очередную строку из одного алиаса и со­

единяет ее с каждой строкой другого алиаса.

Исключение избыточности

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

85

Глава 9. Операция соединения, операнды которой представлены одной таблицей

А в алиасе second - в комбинации со значением В в алиасе first. В данном примере Hoffman был выбран с Clemens, а затем Clemens был выбран с Hoffman. Тоже самое произошло с Cisneros и Grass, Lie и Giovanni и т.д. Кроме того, каждая запись присое­ диняется сама к себе в выходных данных, например, Lie и Lie.

Простой способ исключить повторения - задать порядок для двух значений так, чтобы одно значение было меньше, чем другое, или предшествовало в алфавитном по­ рядке. Это делает предикат ассиметричным, и одни и те же значения не извлекаются снова в обратном порядке, например:

SELECT first.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 из таблицы с алиа­ сом first приписывается Hoffman из таблицы с алиасом second), второе условие не вы­ полняется. С другой стороны, Hoffman не выбирается сам по себе, как имеющий тот же рейтинг, потому •по его имя не предшествует ему же самому в алфавитном поряд-

Рис. 9.2. Исключение избыточных выходных данных при операции соединения с собственной

копией

86

Как выполняется операция соедине11ия двух копий од11ой таблицы

ке. Если нужно включить в запрос соединение строки с ее копией, достаточно исполь­

зовать <= вместо <.

Выявление ошибок

Рассмотренное свойство SQL можно использовать для выявления ошибок опре­ деленного рода. Если посмотреть на таблицу Orders, станет ясно, что поля cnum и snum используются для определения связи. Поскольку каждому покупателю (customer) может быть назначен один и только один продавец (salesperson), в любой момент времени определенному номеру покупателя для строки из таблицы Orders соответствует строка с таким же номером продавца. Следующая команда позволяет выявить любые несоответствия такого плана:

SELECT first.onum, first.cnum, first.snum, second.onum, second.cnum, second.snum

FROM Orders first, Orders second

WHERE first.cnum = second.cnum

AND first.snum <> second.snum;

Команда выглядит сложной, но ее логика весьма прозрачна. Она берет первую стро­ ку таблицы Orders и запоминает ее под именем алиаса first, затем проверяет ее в комби­ нации с каждой строкой таблицы Orders под именем алиаса second. Если комбинация строк удовлетворяет предикату, она включается в состав выходных данных. В нашем случае просматривается строка, в которой поле cnum равно 2008, а поле snum равно 1007; затем осуществляется выбор каждой строки, в поле cnum которой содержится та­ кое же значение. Если обнаруживается, что в поле snum любой из этих строк содержит­ ся другое (отличное от 1007) значение, то предикат принимает значение "истина", и в состав выходных данных включаются те поля из текущей комбинации строк, имена ко­ торых указаны в предложении SELECT. Если все значения поля snum для данного зна­ чения cnum в этой .таблице одинаковы, приведенная выше команда не генерирует

выходных данных.

Еще про ш~иасы

Хотя соединение таблиц со своими копиями - зто первый встретившийся случай, когда потребовалось понятие алиаса, его употребление не лимитировано использова­ нием для разграничения различных копий одной и той же таблицы. Алиасы можно применять при создании альтернативных имен таблиц в команде SELECТ. Например, если таблицы имеют очень длинные и сложные имена, то можно определить простые, состоящие из одной буквы алиасы, например, А или В, и использовать их вместо имен таблиц в предложении SELECT и в предикате. Их можно также применять со связан­ ными подзапросами (которые обсуждаются в главе 11).

87