Многотабличные запросы на чтение (объединения)

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

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

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

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

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

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

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

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

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

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

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

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

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

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.

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