Правила выполнения многотабличных запросов

ПСЕВДОНИМЫ ТАБЛИЦ

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

Если вместо имен двух таблиц использовать псевдонимы S и В, то и вводить, и читать этот запрос будет легче:

Предложение from для многотабличного оператора select содержит псевдонимы таблиц и выполняет две важные функции:

• В предложении from указаны все таблицы, из которых считываются данные. Любой столбец, указанный в операторе select, должен принадлежать одной из таблиц, заданных в предложении from. (Имеется исключение для внешни х ссылок, содержащихся во вложенном запросе).

• Предложение from содержит метку таблицы, которая в операторе select используется для идентификации таблицы в полном имени столбца. Если в предложении from вводится псевдоним таблицы, то он становится ее меткой; в противном случае меткой становится имя таблицы в том виде, в каком оно присутствует в предложении from. Единственное требование, предъявляемое к меткам таблиц в предложении from, состоит в том, что все они должны отличаться друг от друга. Стандарт SQL2 допускает вставку ключевого слова as между именем и псевдонимом таблицы. Хотя это ключевое слово облегчает чтение предложения from, оно поддерживается не во всех версиях SQL.

ПРОИЗВОДИТЕЛЬНОСТЬ ПРИ ОБРАБОТКЕ МНОГОТАБЛИЧНЫХ ЗАПРОСОВ

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

В приложениях, предназначенных для оперативной обработки транзакций (OLTP), запрос обычно ссылается только на одну или две таблицы. В этих приложениях время ответа является критичной величиной — пользователь, как правило, вводит один или два элемента данных и ему требуется получить ответ от базы данных в течение одной или двух секунд. Вот некоторые типичные OLTP-запросы для учебной базы данных:

• Пользователь вводит в какую-нибудь форму идентификатор клиента, и СУБД выводит на экран лимит кредита, состояние счета и другие данные об этом клиенте (запрос к одной таблице)..

• Пользователь с помощью специального устройства сканирует с упаковки номер товара, и СУБД выводит на экран наименование и цену товара (запрос к одной таблице).

• Пользователь вводит имя служащего, и программа выдает список текущих заказов, принятых данным служащим (запрос к двум таблицам).

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

• Пользователь вводит название офиса, и программа выдает список двадцати пяти самых больших заказов, принятых служащими этого офиса (запрос к трем таблицам).

• Отчет суммирует продажи каждого служащего по типам товаров, показывая, какой служащий какие товары продал (запрос к трем таблицам).

• Руководитель рассматривает возможность открытия нового офиса в Сиэтле и выполняет запрос для анализа заказов, клиентов, товаров и служащих (запрос к четырем таблицам).

Структура объединения таблиц

Для простых объединений таблиц довольно легко написать правильный оператор select на основе запроса, выраженного на обычном языке. И наоборот, глядя на оператор select, можно легко определить, что он делает. Однако если в объединении участвует много таблиц или используется сложное условие поиска, бывает не так-то просто определить, какую функцию выполняет тот или иной оператор select. По этой причине необходимо дать более точное определение понятию "объединение".

Умножение таблиц

Объединение представляет собой частный случай более общей комбинации данных из двух таблиц, известной под названием декартово произведение (или просто произведение) двух таблиц. Произведение двух таблиц представляет собой таблицу (называемую таблицей произведения), состоящую из всех возможных пар строк двух таблиц. Столбцами таблицы произведения являются все столбцы первой таблицы, за которыми следуют все столбцы второй таблицы.

Если создать запрос к двум таблицам без предложения where, то таблица результатов запроса окажется произведением двух таблиц. Например, результатом запроса

будет произведение таблиц salesreps и offices, состоящее из всех возможных комбинаций служащий/город. Таблица результатов запроса будет иметь 50 строк (5 офисов * 10 служащих = 50 комбинаций). Обратите внимание, что для объединения двух упомянутых таблиц используется точно такой же оператор select, но только с предложением where, содержащим условие сравнения связанных столбцов:

Объединение двух таблиц является произведением этих таблиц, из которого удалены некоторые строки. Удаляются именно те строки, которые не удовлетворяют условию сравнения связанных столбцов (условию поиска) для данного объединения.

Понятие произведения важно из-за того, что оно входит в формальное определение правил выполнения многотабличных запросов.

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

Таблица результатов запроса на чтение генерируется следующим образом:

1. Если запрос представляет собой запрос на объединение операторов select, для каждого из этих операторов выполнить действия 2—5 и получить отдельную таблицу результатов.

2. Сформировать произведение таблиц, перечисленных в предложении from. Если в предложении from указана только одна таблица, то произведением будет она сама.

3. Если имеется предложение where, применить заданное в нем условие поиска к каждой строке таблицы произведения и оставить в ней только те строки, для которых это условие выполняется, т.е. имеет значение true; строки, для которых условие поиска имеет значение false или NULL, —отбросить.

4. Для каждой из оставшихся строк вычислить значение каждого элемента в списке возвращаемых столбцов и создать одну строку таблицы результатов запроса. При каждой ссылке на столбец используется значение столбца для текущей строки.

5. Если указано ключевое слово distinct, удалить из таблицы результатов запроса все повторяющиеся строки.

31. 6. Если оператор является запросом на объединение операторов select, объединить результаты выполнения отдельных операторов в одну таблицу результатов запроса. Удалить из нее повторяющиеся строки, если не указано ключевое слово all.

7. Если имеется предложение order by, отсортировать результаты запроса.

Строки, полученные в результате выполнения перечисленных действий, составляют таблицу результатов запроса.

Выполним действия:

1. Из таблицы customers (21 строка) и таблицы orders (30 строк) сформируем таблицу произведения, состоящую из 630 строк (все возможные комбинации строк из двух таблиц).

2. Применяя условие поиска, определенное в предложении where, отберем только те строки таблицы произведения, в которых идентификаторы клиентов одинаковые (CUST_NUM = cust) и равны заданному идентификатору (cust == 2103). Останется только четыре строки, остальные 626 будут отброшены.

3. Из таблицы произведения выберем три столбца (company, order_Num и ord _ amount), указанных в предложении select, и получим таблицу результатов запроса, состоящую из четырех строк.

4. В соответствии с предложением order by отсортируем четыре строки по столбцу order _ num и получим окончательный результат. Очевидно, что ни одна реляционная СУБД не будет выполнять запрос подобным образом.

32. ВНЕШНЕЕ ОБЪЕДИНЕНИЕ ТАБЛИЦ *

Операция объединения в SQL соединяет информацию из двух таблиц, формируя пары связанных строк из этих двух таблиц. Объединенную таблицу образуют пары тех строк из различных таблиц, у которых в связанных столбцах содержатся одинаковые значения. Если строка одной из таблиц не имеет пары, то объединение может привести к неожиданным результатам. Это иллюстрируют следующие далее запросы.

На первый взгляд, эти два запроса должны давать одинаковое количество строк; но результаты первого запроса насчитывают десять строк, а второго — только девять. Почему? Потому что Том Снайдер (Torn Snyder) в настоящий момент еще не получил назначение ни в один офис и его строка имеет значение null в столбце rep _ office (это связанный столбец для данного объединения). Значение null не совпадает ни с одним идентификатором офиса в таблице offices, поэтому строка для Тома Снайдера в таблице salesreps остается без пары. В результате она "исчезает" из объединения. Таким образом, стандартное SQL-объединение может привести к потере информации, если объединяемые таблицы содержат несвязанные строки.

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

Такие результаты запроса получаются с помощью другой операции объединения, называемой внешним объединением таблиц (в предложении where оно обозначается символом "*=="). Внешнее объединение является расширением стандартного объединения, называемого внутренним объединением таблиц. В стандарте SQL1 дано определение только внутреннего объединения; понятие внешнего объединения в нем отсутствует. Ранние программные продукты фирмы IBM также поддерживают только внутреннее объединение. Однако внешнее объединение является понятной и полезной частью реляционной модели базы данных и реализовано во многих СУБД, включая SQL Server, Oracle и SQLBase. Внешнее объединение — это наиболее естественный способ для выражения запросов определенного типа.

Чтобы понять смысл внешнего объединения, будет полезно отвлечься от учебной базы данных и рассмотреть две простые таблицы. В таблице girls находится список пяти девочек и городов, в которых они живут; в таблице boys содержится список пяти мальчиков и городов, в которых они живут. Чтобы найти пары девочек и мальчиков, живущих в одном и том же городе, можно использовать следующий запрос, формирующий внутреннее объединение двух таблиц:

Внутреннее объединение дает четыре строки в таблице результатов запроса. Обратите внимание, что две девочки (Анна и Нэнси) и два мальчика (Джеймс и Джордж) не представлены в таблице результатов запроса. Эти строки не имеют пары в другой таблице и поэтому отсутствуют в таблице результатов внутреннего объединения. Две несвязанные строки (Анна и Джеймс) имеют действительные значения в столбцах city, но они не совпадают ни с одним городом в противоположной таблице. Две другие несвязанные строки (Нэнси и Джордж) имеют в столбцах city значение null, а по правилам SQL значение null не равно никакому другому значению (даже другому значению null).

Предположим, что вы хотите вывести список пар девочка/мальчик живущих в одних и тех же городах, и включить в него девочек и мальчике без пары. Этот результат дает внешнее объединение таблиц girls и boy;

Оператор SQL, дающий в результате внешнее объединение:

Внешнее объединение двух таблиц содержит восемь строк. Четыре строки идентичны строкам внутреннего объединения этих таблиц. Две других строки, для Анны и Нэнси, являются несвязанными строками таблицы girls.

Эти строки были расширены значениями null, то есть приравнены к воображаемой строке таблицы boys, содержащей только значения null, и добавлены в результаты запроса. Две последние строки, для Джеймса и Джорджа, являются несвязанными строками таблицы boys. Эти строки также были расширены значениями null (приравнены к воображаемой строке таблицы girls, содержащей только значения null) и добавлены в результаты запроса.

Как видно из этого примера, внешнее объединение является объединением, "сохраняющим информацию". Каждая строка таблицы boys представлена в таблице результатов запроса (некоторые более одного раза). Аналогично, каждая строка таблицы girls представлена в таблице результатов (некоторые, опять таки, более одного раза).

1. Создать внутреннее объединение двух таблиц обычным способом.

2. Каждую строку первой таблицы, которая не имеет связи ни с одной строкой второй таблицы, добавить в результаты запроса, присваивая всем столбцам второй таблицы значение null.

3. Каждую строку второй таблицы, которая не имеет связи ни с одной строкой первой таблицы, добавить в результаты запроса, присваивая всем столбцам первой таблицы значение null.

4. Результирующая таблица является внешним объединением двух таблиц.

ЛЕВОЕ И ПРАВОЕ ВНЕШНИЕ ОБЪЕДИНЕНИЯ *

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

Левое внешнее объединение двух таблиц получается, если выполнить пункт 1 и пункт 2, а пункт 3 пропустить. Таким образом, левое внешнее объединение включает все несвязанные строки первой (левой) таблицы с расширением null, но не включает несвязанные строки второй (правой) таблицы. Вот левое внешнее объединение таблиц GIRLS и BOYS:

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

Правое внешнее объединение двух таблиц получается, если выполнить пункт 1 и пункт 3, а пункт 2 пропустить. Таким образом, правое внешнее объединение включает все несвязанные строки второй (правой) таблицы со значениями null, но не включает несвязанные строки первой (левой) таблицы. Вот правое внешнее объединение Таблиц GIRLS и BOYS:

Этот запрос дает таблицу результатов из шести строк: все пары девочка/мальчик из одних и тех же городов и мальчики без пары. На этот раз в таблицу не вошли девочки, не имеющие пары.

На практике левое и правое внешние объединения более полезны, чем полное внешнее объединение, особенно если таблицы связаны через внешний и первичный ключи. В таком объединении столбец внешнего ключа может содержать значения null, в результате чего таблица-потомок (таблица, содержащая внешний ключ) будет иметь несвязанные строки. Асимметричное внешнее объединение включит эти несвязанные строки в результаты запроса, не включая, однако, несвязанные строки таблицы-предка.

СИСТЕМА ЗАПИСИ ВНЕШНЕГО ОБЪЕДИНЕНИЯ *

Так как понятие внешнего объединения не вошло в стандарт SQL1 и не было реализовано в ранних программных продуктах фирмы IBM, поставщики тех СУБД, в которых это понятие поддерживалось, использовали различные системы записи в своих диалектах SQL. Система записи "*=*", использованная в примерах, применяется в SQL Server

В этой системе записи внешнее объединение обозначается добавлением звездочки (*) к операции сравнения в предложении where, задающем условие объединения. Для обозначения полного внешнего объединения двух таблиц tbli и TBL2 через связанные столбцы coli и COL2 звездочка (*) ставится слева и справа от стандартного оператора объединения. В результате операция сравнения, задающая полное внешнее объединение, выглядит следующим образом:

WHERE COL1 *==* COL2

Для обозначения левого внешнего объединения звездочка ставится только слева, и операция сравнения выглядит так:

WHERE COL1 *= COL2

Для обозначения правого внешнего объединения звездочка ставится справа, и операция сравнения имеет следующий вид:

WHERE COL1 =* COL2

Та же самая нотация используется и с другими операторами сравнения. Например, операция сравнения с оператором "больше чем или равно" (>=), задающая левое внешнее объединение, имеет такой вид:

WHERE COL1 *>== COL2

В СУБД Oracle и SQLBase также поддерживается операция внешнего объединения, но используется другая нотация. Внешнее объединение в предложении where обозначается знаком плюс в скобках, стоящим справа от названия столбца той таблицы, в которую добавляются строки со значениями null. Условие поиска, определяющее левое внешнее объединение, выглядит таким образом:

WHERE COL1 = COL2 (+)

а определяющее правое внешнее объединение — так:

WHERE COL1 (+) = COL2

Обратите внимание на то, что знак плюс находится на стороне оператора, противоположной той, на какой находится звездочка, применяемая в нотации SQL Server. СУБД Oracle и SQLBase не поддерживают полное внешнее объединение.

Хотя обе описанные системы записи внешнего объединения относительно удобны, они, тем не менее, несколько обманчивы. Сначала формируется произведение двух таблиц, а затем из него исключаются строки, не удовлетворяющие условию поиска в предложении where. Но произведение не содержит строк со значением null, входящих во внешнее объединение! Как они попадают в результаты запроса? Ответ состоит в том, что при формировании произведения необходимо обращаться к предложению where, чтобы выяснить, следует ли включать в произведение строки со значением null. Кроме того, две таблицы могут объединяться с помощью более чем одной пары связанных столбцов» и не ясно, как система записи должна использоваться, если имеются две или три пары связанных столбцов.

Проблемы с записью внешних объединений возникают также при расширении объединения до трех и более таблиц.

Понятие внешнего объединения можно легко расширить на три таблицы:

TBLl OUTER -JOIN TBL2 OUTER-JOIN TBL3

Но результат зависит от порядка выполнения операций внешнего объединения. Результат объединения

(TBLl OUTER-JOIN TBL2) OUTER-JOIN TBL3

в общем случае отличается от объединения

TBLl OUTER-JOIN (TBL2 OUTER-JOIN TBL3)

С помощью систем записи SQL Server или Oracle/SQLBase невозможно определить порядок выполнения внешних объединений. Из-за этого результаты внешнего объединения трех или более таблиц непредсказуемы.

Дата добавления: 2014-01-11