Объединение более двух таблиц

Вы можете также создавать запросы, объединяющие более двух таблиц. Предположим что мы хотим найти все Заказы заказчиков не находящихся в тех городах где находятся их продавцы. Для этого необходимо связать все три наши типовые таблицы (вывод показывается в Рисунке 8.4):

SELECT onum, cname, Orders.cnum, Orders.snum
FROM Salespeople, Customers,Orders
WHERE Customers.city <> Salespeople.city
AND Orders.cnum = Customers.cnum
AND Orders.snum = Salespeople.snum;

=============== SQL Execution Log ==============
| SELECT onum, cname, Orders.cnum, Orders.snum |
| FROM Salespeople, Customers, Orders |
| WHERE Customers.city <> Salespeople.city |
| AND Orders.cnum = Customers.cnum |
| AND Orders.snum = Salespeople.snum; |
| =============================================== |
| onum cname cnum snum |
| ------ ------- ----- ----- |
| 3001 Cisneros 2008 1007 |
| 3002 Pereira 2007 1004 |
| 3006 Cisneros 2008 1007 |
| 3009 Giovanni 2002 1003 |
| 3007 Grass 2004 1002 |
| 3010 Grass 2004 1002 |
=================================================

Рисунок 8.4. Объединение трех таблиц.

Хотя эта команда выглядит скорее как комплексная, вы можете следовать за логикой, просто проверяя, что заказчики не размещены в тех городах где размещены их продавцы (совпадение двух snum полей), и что перечисленные Заказы выполнены с помощью этих заказчиков (совпадение Заказов с полями cnum и snum в таблице Заказов).

Резюме

Теперь вы больше не ограничиваетесь просмотром одной таблицы в каждый момент времени. Кроме того, вы можете делать сложные сравнения между любыми полями любого числа таблиц и использовать полученные результаты, чтобы решать какую информацию вы бы хотели видеть. Фактически, эта методика настолько полезна для построения связей, что она часто используется для создания их внутри одиночной таблицы. Это будет правильным: вы сможете объединить таблицу с собой, а это очень удобная вещь. Это будет темой Главы 9.

Работа с SQL

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

2. Напишите запрос, который бы выдавал имена продавца и заказчика для каждого Заказа после номера Заказов.

3. Напишите запрос, который бы выводил всех заказчиков, обслуживаемых продавцом с комиссионными выше 12%. Выведите имя заказчика, имя продавца и ставку комиссионных продавца.

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

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

Объединение таблицы с собой

В ГЛАВЕ 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. Объединение таблицы с собой.

Примечание. В СУБД Interbase 'SECOND' является ключевым словом, пример должен быть модифицирован, например, так:

SELECT a.cname, b.cname, a.rating
FROM Customers a, Customers b
WHERE a.rating = b.rating;

Обратите внимание, что на Рисунке 9.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.cname < second.cname;

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

=============== 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 |
| Clemens Pereira 100 |
| Cisneros Grass 300 |
=================================================

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

Для СУБД Interbase 'second' нужно заменить чем-либо другим.

Hoffman предшествует Periera в алфавитном порядке, поэтому комбинация удовлетворяет обеим условиям предиката и появляется в выводе. Когда та же самая комбинация появляется в обратном Заказе — когда Periera в псевдониме первой таблицы сравнтвается с Hoffman во второй таблице псевдонима — второе условие не встречается. Аналогично, Hoffman не выбирается при наличии того же рейтинга что и он сам, потому что его имя не предшествует ему самому в алфавитном порядке. Если бы вы захотели включить сравнение строк с ними же в запросах подобно этому, вы могли бы просто использовать <= вместо <.

Проверка ошибок

Таким образом, мы можем использовать эту особенность SQL для проверки определенных видов ошибок. При просмотре таблицы Заказов, вы можете видеть, что поля cnum и snum должны иметь постоянную связь.

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

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;

Для Interbase см. Примечание выше.

Хотя это выглядит сложно, логика этой команды достаточно проста. Она будет брать первую строку таблицы Заказов, запоминать ее под первым псевдонимом, и проверять ее в комбинации с каждой строкой таблицы Заказов под вторым псевдонимом, одну за другой. Если комбинация строк удовлетворяет предикату, она выбирается для вывода. В этом случае предикат будет рассматривать эту строку, найдет строку, где поле cnum=2008 а поле snum=1007, и затем рассмотрит каждую следующую строку с тем же самым значением поля cnum. Если он находит, что какая-то из них имеет значение, отличное от значения поля snum, предикат будет верен, и выведет выбранные поля из текущей комбинации строк. Если же значение snum с данным значением cnum в нашей таблице совпадает, эта команда не произведет никакого вывода.

Больше псевдонимов

Хотя объединение таблицы с собой — это первая ситуация, когда понятно, что псевдонимы необходимы, вы не ограничены в их использовании, чтобы только отличать копию одной таблицы от ее оригинала. Вы можете использовать псевдонимы в любое время, когда вы хотите создать альтернативные имена для ваших таблиц в команде (см. Примечание по Interbase выше). Например, если ваши таблицы имеют очень длинные и сложные имена, вы могли бы определить простые односимвольные псевдонимы, типа a и b, и использовать их вместо имен таблицы в предложении SELECT и предикате. Они будут также использоваться с соотнесенными подзапросами (обсуждаемыми в Главе 11).