Название: Базы данных. Концепция баз данных, реляционная модель данных, языки SQL и XML (Токмаков Г. П.)

Жанр: Информационные системы и технологии

Просмотров: 1410


4.5. многотабличные запросы

 

До сих пор мы рассматривали однотабличные запросы, однако на практике довольно часто приходится иметь дело с многотабличными запросами. Напри- мер, предположим, что требуется выполнить запрос, реализующий вывод спи- ска служащих и офисов, в которых они работают (таблицы SLUZHASCHIE и OFFI‐ SY) или вывод списка заказов, выполненных за заданный период, включая сле- дующую информацию: наименование заказанного товара, стоимость заказа и имя клиента (таблицы ZAKAZY, CLIENTY и TOVARY).

Для ответа на эти вопросы SQL обеспечивает возможность выполнения мно-

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

 

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

 

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

– Номер (ID_ORDER) и Стоимость (PRICE) заказа содержатся в таблице ZAKAZY;

– Имя клиента (COMPANY) и лимит кредита (LIMIT_CREDIT) содержатся в табли-

це CLIENTY .

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

1. Сначала построим результирующую таблицу, содержащую четыре пере-

численные выше колонки (ID_ORDER, COMPANY, PRICE, LIMIT_CREDIT).

2. Найдите в таблице ZAKAZY Номер и Стоимость первого заказа (первой за- писи) и перепишите полученные значения в поля ID_ORDER и PRICE результи- рующей таблицы.

3. Запомните Номер клиента (ID_CLN) для первой записи таблицы ZAKAZY.

4. Перейдите к таблице CLIENTY и в столбце ID_CLN найдите значение, соот-

ветствующее запомненному значению поля ID_CLN для первой записи таблицы

ZAKAZY.

5. Отыщите Имя клиента и Лимит кредита для найденной записи таблицы

CLIENTY        и перепишите их в поля COMPANY и LIMIT_CREDIT результирующей таб-

лицы.

6. Таким образом, мы создали одну строку результирующей таблицы. Далее

повторите шаги 2  5 до тех пор, пока не будут перечислены все заказы.

В данном алгоритме можно зафиксировать два важных момента:

– каждая строка результирующей таблицы формируется из пары строк: одна

строка находится в таблице ZAKAZY, а другая  в таблице CLIENTY .

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

 

4.5.2.  В НУТРЕННЕЕ  ОБЪ Е ДИНЕН И Е  ТАБЛИЦ

 

Описанный выше процесс формирования пар строк для записи в результи-

рующую таблицу называется объединением таблиц.

 

Таблица CLIENTY

 

ID_CLN          COMPANY    ID_SLZH         LIMIT_CREDIT

 

2102    «Гранит»        179      $65 000.00

 

42111    «Заря» 189      $50 000.00

 

2103    «Базальт»        178      $40 000.00

 

5Таблица ZAKAZY

 

ID_ORDER     …        ID_CLN          …        PRICE

 

112987            …        2111    …        $3 675

 

112961            …        2102    …        $31 500

5

2112989            …        2103    …        $4 567

 

Результирующая таблица

3

2ID_ORDER     COMPANY    PRICE LIMIT_CREDIT

 

112987            «Заря» 3 675   $50 000.00

 

1          112961            «Гранит»        31 500 $65 000.00

 

112989            «Базальт»        4 567   $40 000.00

 

Рис. 4.3.          Алгоритм выполнения многотабличного запроса

 

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

 

Объединение на основе точного совпадения значений двух и более столб- цов называется объединением по равенству. Объединения могут быть сформи- рованы и на других видах сравнения значений столбцов (например, «больше» или «меньше»).

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

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

ма: вывести список всех заказов, включающий номер и стоимость заказа, на-

именование компании-клиента и лимит кредита клиента.

 

SELECT ID_ORDER, COMPANY, PRICE, CREDIT_LIMIT FROM ZAKAZY, CLIENTY

WHERE ZAKAZY.ID_CLN = CLIENTY.ID_CLN

 

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

ся в результирующей таблице. Порядок выполнения запроса определяет СУБД.

Этим и отличается язык SQL от алгоритмических языков типа C++ и Pascal.

Приведенный пример многотабличного запроса похож на однотабличные за-

просы за исключением следующих различий:

– Предложение FROM содержит две таблицы, а не одну.

– В предложении WHERE сравниваются связанные столбцы разных таблиц.

 

Запро с ы  с  использованием отношенияпредо к - пото мо к

 

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

Чтобы использовать в многотабличном запросе отношение предок-потомок,

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

который выводит список всех служащих с указанием городов и регионов, в  кото-

рых они работают.

 

SELECT FAMILY, NAME, CITY, REGION FROM SLUZHASCHIE, OFFISY

WHERE SLUZHASCHIE.ID_OFC = OFFISY.ID_OFC

 

Результат выполнения этого запроса будет иметь вид

 

FAMILY

NAME

CITY

REGION

Иванов

Иван

Буинск

Татарстан

Полев

Андрей

Буинск

Татарстан

Уткин

Денис

Буинск

Татарстан

Петров

Петр

Инза

Ульяновская

Филатов

Петр

Инза

Ульяновская

Пронин

Игорь

Тверь

Московская

Федоров

Федор

Тверь

Московская

 

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

Рассмотрим еще один запрос, использующий отношение предок-потомок, но здесь роли предка и потомка меняются. Таблица OFFISY (потомок) содержит стол- бец MNGR, представляющий собой вторичный ключ для таблицы SLUZHASCHIE (пре- док).

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

Создадим запрос, который выводит список офисов с указанием города, в ко-

тором он расположен, а также фамилию и имя руководителя офиса.

 

SELECT CITY, FAMILY, NAME FROM OFFISY, SLUZHASCHIE

WHERE OFFISY.MNGR = SLUZHASCHIE.MNGR

 

Результат выполнения этого запроса приведен в следующей таблице.

 

CITY

FAMILY

NAME

Буинск

Полев

Андрей

Инза

Филатов

Петр

Тверь

Пронин

Игорь

 

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

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

 

Запро с ы  на  основе  со ст авны х  ключе й

 

Таблицы ZAKAZY и TOVARY в учебной базе данных связаны с помощью состав- ных ключей ID_MFR и ID_PRD. Поля ID_MFR и ID_PRD в таблице ZAKAZY составляют вторичный ключ для таблицы TOVARY и связаны с ее полями ID_MFR и ID_PRD соот- ветственно. Чтобы объединить таблицы на основе составных ключей, в условии отбора необходимо задать все пары связанных полей, как показано в нижеприве- денном примере.

Создадим запрос, который выводит список всех заказов с указанием их стои-

мости и наименования товаров.

 

SELECT ID_ORDER, DESCRIPTION, PRICE FROM ZAKAZY, TOVARY

WHERE ZAKAZY.ID_MFR = TOVARY.ID_MFR AND ZAKAZY.ID_PRD = TOVARY.ID_PRD

 

ID_ORDER

DESCRIPTION

PRICE

112961

Деталь кузова

31 500

112987

Деталь двигателя

3 675

112989

Сопло

4 567

 

Условие  отбора  в  данном  запросе  показывает,  что  связанными  парами строк таблиц ZAKAZY и TOVARY являются те, в которых пары связанных столбцов содержат одни и те же значения. В SQL количество связанных столбцов не огра-

ничивается, но, как правило, отношения между таблицами создаются с помо-

щью одной пары столбцов, реже с помощью двух или трех.

 

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

 

Написать правильную инструкцию SELECT для простых многотабличных за- просов не сложно. Но если многотабличный запрос составлен из многих таблиц с использованием сложных условий отбора, то инструкция многотабличного запро- са становится трудной для понимания. Поэтому сначала приведем более точное определение понятия «объединения».

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

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

1. Сформировать произведение таблиц, перечисленных в предложении FROM.

2. Если имеется предложение WHERE, применить заданное в нем условие отбора к каждой строке таблицы произведения и оставить в ней те строки, для которых это условие выполняется.

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

4. Если в предложении SELECT указано ключевое слово DISTINCT, то повто-

ряющиеся строки из результирующей таблицы удаляются.

5. Если в запросе имеется предложение ORDER BY, результирующая таблица сортируется.

 

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

 

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

Рассмотрим следующий пример. Вывести список служащих и городов, где они работают:

 

SELECT FAMILY, NAME, CITY

FROM zakazy.sluzhaschie s, zakazy.offisy o

WHERE o.id_ofc = s.id_ofc

 

Результат выполнения этого запроса приведен на Рис. 4.4. .

 

 

Рис. 4.4.          Результат выполнения запроса внутреннего объединения

 

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

Если мы хотим вывести сведения обо всех офисах, независимо от того, на- браны в него служащие или еще нет, то правильный результат можно получить, выполнив запрос, приведенный ниже:

 

SELECT *

FROM zakazy.offisy o

LEFT JOIN zakazy.sluzhaschie s on o.id_ofc = s.id_ofc

 

Запрос, приведенный в этом примере, называется внешним (в данном случае левым) объединением таблиц. Результат выполнения запроса показан на Рис. 4.5. .

Как видно из приведенного примера, внешнее объединение может сохранить

записи, для которых не находится соответствия в других наборах. При этом не-

достающие поля заполняются значениями NULL.

 

Пр ави л а  выпо лнени я  внешних  объединений

 

Можно привести следующее правило построения внешнего объединения.

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

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

 

 

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

 

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

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

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

и правыми внешними объединениями.

На практике левые и правые объединения более полезны, чем полное объе-

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

 

Лево е  внеш нее  объединение

 

Левое внешнее объединение, результат выполнения которого приведен на Рис. 4.5. , получается при выполнении п.п. 1 и 2 из приведенного выше правила. Всегда содержит как минимум один экземпляр каждой записи из набора, ука- занного слева от ключевого слова JOIN. Отсутствующие поля из правого набора заполняются значениями NULL.

В приведенном примере столбец ID_OFC таблицы является внешним клю- чом таблицы  offisy; он содержит номера офисов, в которых работают служа- щие и допускает наличие значений NULL, если для нового офиса еще не набраны

служащие. В нашей таблице offisy такой офис есть  это офис, расположенный в г. Омск.

Пр аво е  вн ешн е е  объединение

 

Правое внешнее объединение получается при выполнении п.п. 1 и 3 из приве- денного выше правила. Всегда содержит как минимум один экземпляр каждой за- писи из набора, указанного справа от ключевого слова JOIN. Допускает наличие значения NULL. Если новому служащему еще не был назначен офис. В нашей таб- лице sluzhaschie такой служащий есть  это Уткин Денис. Запрос для выполнения правого внешнего объединения, целью которого является отображение сведений о служащих, которым еще не назначены офисы, имеет вид, приведенный на Рис.

4.6. .

 

SELECT *

FROM zakazy.offisy o

RIGHT JOIN zakazy.sluzhaschie s on o.id_ofc = s.id_ofc

 

 

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

 

По лно е  вн ешн е е  объединение

 

Пример  запроса,  выполняющего  полное  внешнее  объединение  таблиц

offisy и sluzhaschie, приведен ниже, а результат его выполнения  на Рис. 4.7.

. Полное внешнее объединение всегда содержит как минимум один экземпляр каждой записи каждого объединяемого набора. Отсутствующие поля в записях нового набора заполняются значениями NULL.

 

SELECT *

FROM zakazy.offisy o

FULL JOIN zakazy.sluzhaschie s on o.id_ofc = s.id_ofc

 

 

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