Несколько связанных столбцов

ЗАПРОСЫ НА ЧТЕНИЕ К ТРЕМ И БОЛЕЕ ТАБЛИЦАМ

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

СОРТИРОВКА РЕЗУЛЬТАТОВ ЗАПРОСА
(ПРЕДЛОЖЕНИЕ ORDER BY)

Строки результатов запроса, как и строки таблицы базы данных, не имеют определенного порядка. Включив в оператор select предложение order by, можно отсортировать результаты запроса. Это предложение состоит из ключевых слов order by, за которыми следует список имен или порядковых номеров столбцов, разделенных запятыми. Первый столбец (region) является главным ключом сортировки; столбцы, следующие за ним (в данном примере city), являются все более второстепенными ключами. Можно сортировать результаты запроса по любому элементу списка возвращаемых столбцов запроса.

В предложении order by можно выбрать возрастающий или убывающий порядок сортировки. По умолчанию, данные сортируются в порядке возрастания. Чтобы сортировать их по убыванию, следует включить в предложение сортировки ключевое слово desc, как это сделано в следующем примере:

Чтобы определить порядок сортировки по возрастанию, необходимо использовать ключевое слово asc, однако из-за того, что такой порядок принят по умолчанию, это ключевое слово обычно не указывают.

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

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

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

ПРАВИЛА ВЫПОЛНЕНИЯ ОДНОТАБЛИЧНЫХ ЗАПРОСОВ

Результаты запроса, возвращенные оператором select, получаются в результате поочередного применения входящих в оператор предложений. Вначале применяется предложение from (оно выбирает таблицу, содержащую требуемые данные). Затем применяется предложение where (которое по определенному критерию отбирает из таблицы строки). Далее применяется предложение select (которое создает указанные столбцы результатов запроса и при необходимости удаляет повторяющиеся строки), и, наконец, для сортировки результатов запроса применяется предложение order by.

28. ОБЪЕДИНЕНИЕ РЕЗУЛЬТАТОВ НЕСКОЛЬКИХ ЗАПРОСОВ (UNION)*

Иногда появляется необходимость объединения результатов двух или более запросов в одну таблицу. SQL поддерживает такую возможность с помощью оператора Union.

Чтобы таблицы результатов запроса можно было объединить с помощью оператора union, они должны соответствовать следующим требованиям:

• две таблицы должны содержать одинаковое число столбцов;

• тип данных каждого столбца первой таблицы должен совпадать с типом данных соответствующего столбца во второй таблице;

• ни одна из двух таблиц не может быть отсортирована с помощью предложения order by, однако объединенные результаты запроса можно отсортировать.

Имена столбцов в двух запросах, объединенных с помощью оператора union, не обязательно должны быть одинаковыми. В предыдущем примере в первой таблице результатов запроса имеются столбцы с именами MFR_ID и product_ID, в то время как во второй таблице результатов запроса соответствующие им столбцы имеют имена MFR и product. Поскольку столбцы в двух таблицах могут иметь различные имена, столбцы результатов запроса, возвращенные оператором union, имен не имеют.

Стандарт ANSI/ISO накладывает дополнительные ограничения на операторы select, участвующие в операторе union. Он разрешает использовать в списке возвращаемых столбцов только имена столбцов или указатель на все столбцы (select *) и запрещает использовать выражения. В большинстве коммерческих реализации SQL, тем не менее, это ограничение ослаблено, и в списке возвращаемых столбцов разрешено использовать простые выражения. Однако во многих реализациях SQL не разрешается включать в операторы select предложения group by или having, а в некоторых не разрешается использовать в списке возвращаемых столбцов агрегатные функции (т.е. нельзя использовать итоговые запросы).

ЗАПРОС НА ОБЪЕДИНЕНИЕ И ПОВТОРЯЮЩИЕСЯ СТРОКИ *

Поскольку оператор union объединяет строки из двух результатов запросов, вполне вероятно, что в результатах, возвращенных этим оператором, будут содержаться повторяющиеся строки. Например, в запросе стоимость товара REI-2A44L составляет $4500,00, поэтому он входит в результаты верхнего из числа представленных на рисунке запросов. Кроме того, в таблице orders для этого товара имеется заказ на сумму $31500,00, поэтому он входит и в результаты второго запроса. По умолчанию оператор union в процессе своего выполнения удаляет повторяющиеся строки. Таким образом, в объединенных результатах запроса содержится только одна строка для товара REI-2A44L.

Если в результатах оператора union необходимо сохранить повторяющиеся строки, сразу за ключевым словом union следует указать ключевое слово all. Эта форма запроса вернет результаты с повторяющимися строками:

Обработка повторяющихся строк для операторов union и select осуществляется по разному. Оператор select по умолчанию оставляет повторяющиеся строки (select all). Чтобы удалить повторяющиеся строки, необходимо явно указать ключевое слово distinct. Оператор union по умолчанию удаляет повторяющиеся строки. Чтобы оставить повторяющиеся строки, необходимо явно указать ключевое слово ALL.

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

• на практике большинство простых операторов select не возвращают повторяющиеся строки, поэтому по умолчанию принято не удалять их;

• на практике большинство операторов union возвращают повторяющиеся строки, что нежелательно, поэтому по умолчанию такие строки удаляются.

Удаление повторяющихся строк из таблицы результатов запроса занимает много времени, особенно если результаты запроса содержат большое количество строк. Если известно, что оператор union не возвратит повторяющиеся строки, необходимо явно указать ключевое слово all, тогда запрос будет выполняться быстрее.

ЗАПРОС НА ОБЪЕДИНЕНИЕ И СОРТИРОВКА *

Предложение order by нельзя использовать ни в одномиз операторов select, объединенных оператором union. Нет смысла выполнять сортировку результатов таких запросов, поскольку пользователь все равно не увидит их в чистом виде. Однако объединенные результаты запросов, возвращенные оператором union, можно отсортировать с помощью предложения order by, следующего за вторым оператором select. Поскольку столбцы таблицы результатов запроса на объединение не имеют имен, в этом предложении следует указывать номера столбцов.

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

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

A UNION (В UNION С)

(A UNION В) UNION С

(A UNION С) UNION В

и возвращают семь строк результатов запроса. Подобным образом три следующих выражения полностью эквивалентны и возвращают двенадцать строк результатов запроса, поскольку повторяющиеся строки сохраняются:

A UNION ALL (В UNION ALL С)

(A UNION ALL В) UNION ALL С

(A UNION ALL C) UNION ALL В

Однако если в запросы на объединения входят как операторы union, так и операторы union all, то порядок следования этих операторов имеет значение. Если выражение

A UNION ALL В UNION С

проинтерпретировать как

A UNION ALL (В UNION С)

то оно вернет десять строк (шесть из внутреннего оператора плюс четыре строки из таблицы А). Однако если его проинтерпретировать как

(A UNION ALL В) UNION С

то оно вернет только четыре строки, поскольку внешний оператор union удалит все повторяющиеся строки.

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

МНОГОТАБЛИЧНЫЕ ЗАПРОСЫ НА ЧТЕНИЕ (ОБЪЕДИНЕНИЯ)

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

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

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

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

Четыре запрашиваемых элемента данных ранятся в двух различных таблицах:

• В таблице orders содержится номер и стоимость каждого заказа, но в ней отсутствуют имена клиентов и лимиты предоставленных им кредитов.

• В таблице customers содержатся имена клиентов и данные о состоянии их счетов, но в ней нет информации о заказах.

Однако между двумя этими таблицами существует связь. В каждой строке столбца cust таблицы orders содержится идентификатор клиента, сделавшего заказ, соответствующий значению одной из строк столбца cust _ num таблицы customers. Очевидно, чтобы получить требуемые результаты, в операторе select, с помощью которого осуществляется запрос, необходимо как-то учесть эту связь между таблицами.

ПРОСТОЕ ОБЪЕДИНЕНИЕ ТАБЛИЦ
(ОБЪЕДИНЕНИЕ ПО РАВЕНСТВУ)

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

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

Приведенный запрос очень похож на запросы, рассмотренные раньше, однако между ними есть два отличия. Во-первых, предложение from содержит две таблицы, а не одну. Во-вторых, в условии поиска

CUST == CUST_NUM

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

ЗАПРОСЫ С ИСПОЛЬЗОВАНИЕМ ОТНОШЕНИЯ ПРЕДОК/ПОТОМОК

Среди многотабличных запросов наиболее распространены запросы к двум таблицам, связанным с помощью отношения предок/потомок. Запрос о заказах и клиентах является примером такого запроса. У каждого заказа (потомка) есть соответствующий ему клиент (предок), и каждый клиент (предок) может иметь много своих заказов (потомков). Пары строк, из которых формируются результаты запроса, связаны отношением предок/потомок.

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

Таблица salesreps (потомок) содержит столбец rep_office, который является внешним ключом для таблицы offices (предок). Здесь отношение предок/потомок используется с целью поиска в таблице office для каждого служащего соответствующей строки, содержащей город и регион, и включения ее в результаты запроса.

Таблица offices (потомок) содержит столбец mgr, представляющий собой внешний ключ для таблицы salesreps (предок). Это отношение используется здесь, чтобы для каждого офиса найти в таблице salesreps соответствующую строку, содержащую имя и должность руководителя, и включить ее в результаты запроса.

В SQL не требуется, чтобы связанные столбцы были включены в результаты многотабличного запроса. На практике они чаще всего и не включаются, как это было в двух предыдущих примерах. Это связано с тем, что первичные и внешние ключи, как правило, представляют собой идентификаторы (такие как идентификатор офиса или идентификатор служащего в приведенных примерах), которые человеку трудно запомнить, тогда как соответствующие названия (города, районы, имена, должности) запомнить гораздо легче. Поэтому вполне естественно, что в предложении where для объединения двух таблиц используются идентификаторы, а в предложении select для создания столбцов результатов запроса — более удобные для восприятия имена.

ОБЪЕДИНЕНИЯ С УСЛОВИЕМ ДЛЯ ОТБОРА СТРОК

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

В результате применения дополнительного условия поиска число строк в таблице результатов запроса уменьшилось. Согласно первому условию (MGR=EMPL_NUM),из таблиц OFFICES и SALESREPS отбираются пары строк, которые имеют соответствующее отношение предок/потомок; согласно второму условию, производится дальнейший отбор только тех пар строк, где плановый объем продаж превышает $600000.

Таблицы orders и products в учебной базе данных связаны парой составных ключей. Столбцы mfr и product в таблице orders вместе образуют внешний ключ для таблицы products и связаны с ее столбцами mfr _ id и product _ id соответственно. Чтобы объединить таблицы на основе такого отношения предок/потомок, необходимо задать обе пары связанных столбцов, как показано в данном примере:

Условие поиска показывает, что связанными парами строк таблиц orders и products являются те, в которых пары связанных столбцов содержат одни и те же значения. Объединения посредством нескольких столбцов распространены меньше, чем объединения посредством одного столбца, и обычно встречаются в запросах с составными внешними ключами, как в приведенном выше примере.

ЗАПРОСЫ НА ЧТЕНИЕ К ТРЕМ И БОЛЕЕ ТАБЛИЦАМ

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

В этом запросе используются два внешних ключа таблицы orders. Столбец cust является внешним ключом для таблицы customers; он связывает каждый заказ с клиентом, сделавшим его. Столбец rep является внешним ключом для таблицы salesreps, связывая каждый заказ со служащим, принявшим его. Проще говоря, запрос связывает каждый заказ с соответствующими клиентом и служащим.

А вот еще один запрос к трем таблицам, в котором используется другая комбинация отношений предок/потомок:

В первом отношении снова используется столбец cust из таблицы orders в качестве внешнего ключа для таблицы customers. Во втором отношении используется столбец custjrep из таблицы customers в качестве внешнего ключа для таблицы salesreps. Проще говоря, данный запрос связывает каждый заказ с клиентом, а каждого клиента — с закрепленным за ним служащим.

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

Фактически, он на один шаг расширяет последовательность объединения в предыдущем запросе, связывая заказ с клиентом, клиента — с закрепленным за ним служащим, а служащего — с его офисом.

Прочие объединения таблиц по равенству

Огромное множество многотабличных запросов основано на отношения предок/потомок, но в SQL не требуется, чтобы связанные столбцы представляли собой пару "внешний ключ — первичный ключ". Любые два столбца из двух таблиц могут быть связанными столбцами, если только они имею сравнимые типы данных. Вот пример запроса, в котором связанными столбцами являются столбцы с датами:

Результатами запроса являются пары строк из таблиц orders и salesreps, имеющие одинаковые значения в столбцах ORDER _ DATE И HIRE _ DATE. Эти столбцы не являются нивнешним ключом,ни первичным ключом, да и вообще отношение между строками этих пар, надо признать, довольно странное: общее у заказов и служащих только то, что они появились в компании в один день. Тем не менее, SQL с готовностью объединяет таблицы в соответствии с запросом.

Связанные столбцы, подобные приведенным в данном примере, создают между двумя таблицами отношение "многие-ко-многим". Может быть получено много заказов в день приема на работу какого-нибудь служащего, и в день получения какого-нибудь заказа на работу может быть принято несколько служащих. В нашем примере 12 октября 1989 года было получено три заказа (112968, 112975 и 112979), и в тот же день на работу было принято двое служащих (Ларри Фитч и Мэри Джонс). Три заказа и двое служащих дают шесть строк в таблице результатов запроса.

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

• В объединении, созданном путем связи первичного ключа с внешним ключом, всеща существует отношение "один-ко-многим" (предок/потомок).

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

• В общем случае в объединениях, созданных на основе произвольных связанных столбцов, существуют отношения "многие-ко-многим". Обратите внимание на то, что отличия трех этих ситуаций не влияют на форму записи оператора select, выражающего объединение. Объединения всех трех типов записываются одним и тем же способом: в предложение where включается операция сравнения связанных столбцов. Тем не менее, сделанные выводы полезны для понимания того, как запрос, сформулированный на естественном языке, превратить в правильный оператор select.

ОБЪЕДИНЕНИЕ ТАБЛИЦ ПО НЕРАВЕНСТВУ

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

Как и во всех запросах к двум таблицам, каждая строка результатов запроса получается из пары строк, в данном случае содержащихся в таблицах salesreps и offices. Условие поиска

QUOTA > TARGET

отбирает пары строк, в которых значение столбца quota из таблицы salesreps превышает значение столбца target из таблицы offices. Обратите внимание: выбранные из таблиц salesreps и offices пары строк связаны только таким образом; в частности, не требуется, чтобы строка таблицы salesreps представляла служащего, который работает в офисе, представленном строкой таблицы offices. Следует признать, что данный пример имеет несколько искусственный характер и является иллюстрацией того, почему столь мало распространены объединения по неравенству. Однако они могут оказаться полезными в приложениях, предназначенных для поддержки принятия решений, и в других приложениях, исследующих более сложные взаимосвязи в базе данных.

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

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

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

• В многотабличных запросах особый смысл имеет выбор всех столбцов

(SELECT *).

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

• В предложении from используются псевдонимы таблиц, чтобы упростить полные имена столбцов и обеспечить однозначность ссылок на столбцы в самообъединении.