Соединения: Выбор данных из нескольких таблиц

 

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

 

В этой главе рассматривается мультитабличная операция соединения (join). Подзапросы, которые обращаются к нескольким таблицам, будут рассмотрены в главе 5 “Подзапросы: Использование запросов внутри других запросов”. Часто cоединения могут выступать в качестве подзапросов.

 

В этой главе обсуждаются следующие темы:

 

·         Общий обзор операций соединения;

·         Как соединять таблицы в запросе;

·         Как SQL Сервер выполняет соединение;

·         Как влияют неопределенные значения на соединение;

·         Как указывать столбцы для соединения.

 

 

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

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

Имеется также несколько разновидностей соединений: соединения с равенством (эквисоединения), естественные (natural) соединения, внешние соединения и т.д.

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

 

select au_fname, au_lname, pub_name

from authors, publishers

where authors.city = publishers.city

 

au_fname      au_lname      pub_name

-------------      ------------     -----------------------------

Cheryl           Carson        Algodata Infosystems

Abraham       Bennet        Algodata Infosystems

 

(Выбрано 2 строки)

 

Поскольку требуемая информация находится в двух таблицах publishers и authors, то для ее выбора необходимо соединение этих таблиц.

 

 

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

В структурных СУБД, известных также как сетевые или иерархические системы, связи между данными должны быть заранее определены. В таких системах после создания базы данных уже трудно сделать запрос относительно связей между данными, которые не были заранее предусмотрены.

В реляционных СУБД, наоборот, при создании базы данных связи между данными не фиксируются. Они проявляются лишь при обработке данных, т.е. в момент запроса к базе данных, а не при ее создании. Можно обратиться с любым запросом, который приходит в голову, относительно хранящейся в базе информации, независимо от того с какой целью создавалась эта база.

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

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

 

 

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

Названия столбцов в этом примере pub_name, au_lname и au_fname не нужно уточнять названием таблицы, поскольку здесь нет неоднозначности относительно того, какой таблице они принадлежат. Но название столбца city, который используется в операции сравнения уже нуждается в уточнении, поскольку столбцы с таким названием имеются в обеих таблицах. Хотя в этом примере ни один из столбцов city не появляется в результатах запроса, SQL Серверу необходимо уточнение для выполнения операции сравнения.

Как и в операторе выбора, здесь можно включить все столбцы в результат запроса с помощью сокращения “*”. Например, для того чтобы включить все столбцы таблиц authors и publishers в результат предыдущего соединения, необходимо выполнить следующий запрос:

 

select *

from authors, publishers

where authors.city = publishers.city

 

au_id                 au_lname          au_fname         phone               address             city

state      postalcode         contract            pub_id              pub_name                     city       state

---------------      ----------            ----------            ------------------  ----------------

------     --------------       --------              ---------             -----------------------------  --------- 

238-95-7766       Carson              Cheryl              415 548-7723    589 Darwin Ln. Berkeley

CA        94705               1                      1389                 Algodata Infosystems    Berkeley  CA

 

409-56-7008       Bennet              Abraham          415 658-9932    223 Bateman St Berkeley

CA        94705               1                      1389                 Algodata Infosystems    Berkeley  CA

 

(Выбрано 2 строки)

 

Отсюда видно, что результирующая строка составлена из строк исходных таблиц и состоит из тринадцати столбцов каждая. Поскольку ширины печатной страницы не хватает, то каждая результирующая строка размещается на двух текстовых строках. Когда используется символ “*”, то столбцы выводятся в том порядке, в каком они расположены в таблицах.

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

 

select au_lname, au_fname

from authors, publishers

where authors.city = publishers.city

 

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

 

 

В предложении from оператора соединения указываются названия всех таблиц и вьюверов, участвующих в соединении. Именно это предложение указывает SQL Серверу, что необходимо выполнить соединение. Таблицы и вьюверы в этом предложении можно указывать в произвольном порядке. Порядок расположения названий таблиц влияет на результат только при использовании сокращения “*” в списке выбора.

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

 

·         Название таблицы (или вьювера), указанное в предложении from;

·         Каждая копия названия одной и той же таблицы (самосоединение);

·         Название таблицы, указанное в подзапросе;

·           Названия базовых таблиц, на которые ссылаются вьюверы, указанные в     предложении from.

 

Соединения, в которых участвует более двух таблиц, рассматриваются далее в главе “Соединение более двух таблиц”.

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

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

 

 

В предложении where (где) указываются отношения, которые устанавливаются между таблицами, перечисленными в предложении from, для выбора результирующих строк. В нем приводятся названия столбцов, по которым производится соединение, дополненные при необходимости названиями таблиц, и операция сравнения, которой обычно является равенство, но иногда здесь могут встречаться и отношения “больше чем” или “меньше чем”. Детальное описание синтаксиса предложения where приводится в главе 2 этого руководства и в главе “Предложение where” в Справочном руководстве SQL Сервера.

 

Замечание. Можно получить совершенно неожиданный результат, если опустить предложение where в операторе соединения. Без этого предложения все вышеприведенные запросы на соединение будут выдавать 27 строк вместо 2. В следующем разделе будет объяснено почему так происходит.

 

Соединения, в которых данные сравниваются на совпадение, называются эквисоединениями (equijoins). Более точное определение эквисоединения дается позже в этой главе, также как и примеры соединений, основанных не на равенстве.

Соединение может основываться на следующих операциях сравнения:

 

Таблица 4.1. Операции сравнения  

 

Операция	Значение
=	Равно
>	Больше чем
>=	Больше или равно
<	Меньше чем
<=	Меньше или равно
!=	Не равно
!>	Меньше или равно (не больше)
!<	Больше или рано (не меньше)

 

Соединения, основанные на операциях сравнения, в общем называются тетасоединениями (theta joins). Другой класс соединений образуют внешние соединения, которые рассматриваются позже в этой же главе. К числу внешних операций соединения относятся следующие операции.

 

Таблица 4.2. Операции внешнего соединения  

 

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

 

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

Однако, если типы данных не совпадают, то они должны быть совместимыми, чтобы SQL Сервер мог автоматически преобразовать их между собой. Например, SQL Сервер автоматически преобразует друг в друга любые числовые типы данных: int, smallint, tinyint, decimal, float, а также любые строковые типы и типы даты: char, varchar, nchar, nvarchar  и datetime.  Более детально преобразование типов рассматривается в главе 10 “Использование встроенных функций в запросах” и в главе “Функции преобразования типов данных” Справочного руководства SQL Сервера.

 

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

 

where datalength(textab_1.textcol) > datalength(textab_2.textcol)

 

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

 

 

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

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

Например, число строк в декартовом произведении таблиц author и publishers равно 69 ( 23 автора, умноженные на 3 издателя).

Декартово произведение строится в любом запросе, который содержит более одной таблицы в списке выбора, более одной таблицы в предложении from и не содержит предложения where. Например, если убрать предложение where из предыдущего запроса на соединение, то SQL Сервер скомбинирует 23 автора с 3 издателями и возвратит в результате 69 строк.

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

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

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

 

 

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

Запрос, который был рассмотрен ранее:

 

select *

from authors, publishers

where authors.city = publishers.city

 

является примером еквисоединения. В результате этого запроса столбец city появляется дважды. Из определения следует, что результат эквисоединения содержит два одинаковых столбца. Поскольку обычно нет необходимости повторять одну и ту же информацию, то один из этих столбцов можно удалить путем модификации запроса. Результат этой модификации, показанный далее, называется естественным соединением.

 

select publishers.pub_id, publishers.pub_name, publishers.state, authors.*

from publishers, authors

where publishers.city = authors.city

 

В этом примере столбец publishers.city уже не появится в результате запроса.

 

 

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

 

select title, pub_name, advance

from titles, publishers

where titles.pub_id = publishers.pub_id and advance > $7500

 

title                                                        pub_name                           advance

----------------------------------------------             ----------------------------           -------------

You Can Combat Computer Stress!          New Age Books                      10,125.00

The Gourmet   Microwave                            Binnet & Hardley                 15,000.00

Secrets of Silicon Valley                              Algodata Infosystems          8,000.00

Sushi, Anyone?                                              Binnet & Hardley             8,000.00

 

(Выбрано 4 строки)

 

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

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

 

 

Условие соединения таблиц не обязательно является равенством. Здесь можно использовать любую другую операцию сравнения: не равно (!=), больше чем (>), меньше чем (<), больше или равно (>=), меньше или равно (<=). Язык Transact-SQL также содержит операции !> и !< , которые эквивалентны операциям меньше или равно и больше или равно соответственно.

В следующем примере используется операция “больше чем” для нахождения авторов, которые публиковались издательством New Age Books и которые живут в штатах, названия которых больше чем название штата Массачусетс (в алфавитном порядке):

 

select pub_name, publishers.state, au_lname, au_fname, authors.state

from publishers, authors

where authors.state > publishers.state and pub_name = "New Age Books"

 

pub_name              state          au_lname                 au_fname          state

---------------------     ------        -----------------         ----------------           -----

New Age Books    MA      Greene                      Morningstar          TN

New Age Books    MA      Blotchet-Halls         Reginald                OR

New Age Books    MA      del Castillo               Innes                      MI

New Age Books    MA      Panteley                   Sylvia                     MD

New Age Books    MA      Ringer                        Anne                     UT

New Age Books    MA      Ringer                       Albert                     UT

 

(Выбрано 6 строк)

 

В следующем примере в соединении используются операции >= и <  для правильного нахождения скидок (royalty) в таблице roysched, связанных с общим объемом продаж:

 

select t.title_id, t.total_sales, r.royalty

from titles t, roysched r

where t.title_id = r.title_id and t.total_sales >= r.lorange and t.total_sales < r.hirange

 

title_id                     total_sales             royalty

-----------                  ----------- -------       ----------

BU1032                   4095                       10 

BU1111                  3876                        10 

BU2075                  1872                        24 

BU7832                  4095                        10 

MC2222                 2032                        12 

MC3021                  22246                      24 

PC1035                   8780                        16 

PC8888                   4095                        10 

PS1372                    375                          10 

PS2091                   2045                        12 

PS2106                    111                         10 

PS3333                   4072                        10 

PS7777                   3336                        10 

TC3218                    375                          10 

TC4203                    15096                      14 

TC7777                    4095                       10

 

(Выбрано 16 строк)

 

 

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

Поскольку этот запрос включает столбцы одной таблицы authors, то эта таблица выступает в двух ролях. Чтобы различить эти роли, необходимо временно присвоить ей в предложении from различные коррелирующиеся (согласующиеся) названия, такие как au1 и au2. Эти согласующиеся названия будут использоваться для уточнения названий столбцов в следующем запросе. В этом случае самосоединение выглядит следующим образом:

 

select au1.au_fname, au1.au_lname, au2.au_fname, au2.au_lname

from authors au1, authors au2

where au1.city = "Oakland" and au2.city = "Oakland"

and au1.state = "CA" and au2.state = "CA"

and au1.postalcode = au2.postalcode

 

au_fname   au_lname  au_fname     au_lname

------------    -----------   -----------      ------------

Marjorie     Green           Marjorie    Green

Dick           Straight        Dick           Straight

Dick           Straight        Dirk           Stringer

Dick           Straight        Livia          Karsen

Dirk           Stringer        Dick           Straight

Dirk           Stringer        Dirk           Stringer

Dirk           Stringer        Livia          Karsen

Stearns       MacFeather  Stearns      MacFeather

Livia          Karsen          Dick          Straight

Livia          Karsen          Dirk          Stringer

Livia          Karsen          Livia         Karsen

 

(Выбрано 11 строк)

 

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

 

select au1.au_fname, au1.au_lname, au2.au_fname, au2.au_lname

from authors au1, authors au2

where au1.city = "Oakland" and au2.city = "Oakland"

and au1.state = "CA" and au2.state = "CA"

and au1.postalcode = au2.postalcode

and au1.au_id < au2.au_id

 

au_fname   au_lname    au_fname  au_lname

---------       -----------     -----------   ----------

Dick            Straight       Dirk           Stringer

Dick            Straight       Livia          Karsen

Dirk           Stringer       Livia          Karsen

                    

                    (Выбрано 3 строки)

 

Теперь понятно, что Дик Страйт, Дик Стрингер и Ливия Карсен живут в одном и том же почтовом округе.

 

         

 Условие “не равно” особенно полезно для отбора строк при

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

 

select distinct t1.type, t1.price

from titles t1, titles t2

where t1.price <$15 and t2.price <$15

and t1.type = t2.type

and t1.price != t2.price

 

type                        price

----------                    --------

business                 2.99

business                 11.95

psychology            7.00

psychology            7.99

psychology            10.95

trad_cook               11.95

trad_cook               14.99

 

                      (Выбрано 7 строк)

 

Замечание. Выражение “not название_столбца1 = название_столбца2” эквивалентно выражению “название_столбца1 != название_столбца2”.

 

В следующем примере соединение с условием “не равно” комбинируется с самосоединением. В этом запросе ищутся строки в таблице titleauthor, у которых одинаково значение поля title_id, но различно значение поля au_id, т.е. ищутся книги, у которых, по крайней мере, два автора.

 

select distinct t1.au_id, t1.title_id

from titleauthor t1, titleauthor t2

where t1.title_id = t2.title_id and t1.au_id != t2.au_id

order by t1.title_id

 

au_id               title_id 

----------------      ----------- 

213-46-8915      BU1032   

409-56-7008      BU1032   

267-41-2394      BU1111   

724-80-9391      BU1111   

722-51-5454      MC3021   

899-46-2035      MC3021   

427-17-2319      PC8888   

846-92-7186      PC8888   

724-80-9391      PS1372   

756-30-7391      PS1372   

899-46-2035      PS2091   

998-72-3567      PS2091   

267-41-2394      TC7777   

472-27-2349      TC7777   

672-71-3249      TC7777

 

(Выбрано 15 строк)

 

Иногда соединения с условием “не равно” бывает недостаточно и его необходимо заменить подзапросом. Например, предположим, что необходимо получить список авторов, которые живут в городах, где нет издательств. Для простоты ограничим этот список авторами, фамилии которых начинаются на буквы “А”, “В” и “С”. Запрос с условием “не равно” может иметь следующий вид:

 

select distinct au_lname, authors.city

from publishers, authors

where au_lname like "[ABC]%" and publishers.city != authors.city

 

Но получаемые на него результаты вовсе не являются ответом на этот вопрос!

au_lname             city                 

----------------         ------------ 

Bennet               Berkeley 

Carson               Berkeley 

Blotchet-Halls       Corvallis

 

Система интерпретирует этот SQL оператор следующим образом: “Найти фамилии авторов, которые живут в городе, в котором нет некоторого издательства”. Все авторы, имеющиеся в таблице, удовлетворяют этому условию, включая авторов, живущих в Беркли, в котором расположено издательство Algodata Inforsystems.

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

Правильный оператор будет иметь следующий вид:

 

select distinct au_lname, city

from authors

where au_lname like "[ABC]%" and city not in

(select city from publishers

where authors.city = publishers.city)

 

Теперь получается нужный результат:

 

au_lname             city                 

-------------------      ------------

Blotchet-Halls       Corvallis

 

                    (Выбрана 1 строка)

 

Подзапросы будут подробно рассмотрены в главе 6.

 

 

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

 

select au_lname, au_fname, title

from authors, titles, titleauthor

where authors.au_id = titleauthor.au_id

and titles.title_id = titleauthor.title_id

and titles.type = "trad_cook"

 

au_lname            au_fname       title

--------------        -----------        -----------------------------------------------

Panteley                Sylvia            Onions, Leeks, and Garlic: Сooking      Secrets of the Mediterranean

Blotchet-Halls      Reginald       Fifty Years in Buckingham Palace                                                       Kitchens

O'Leary                   Michael          Sushi, Anyone?

Gringlesby              Burt                Sushi, Anyone?

Yokomoto               Akiko             Sushi, Anyone?

 

(Выбрано 5 строк)

 

Заметим, что одна из таблиц в предложении from, а именно titleauthor, не передает в окончательный результат свои данные, поскольку ни данные из столбца au_id, ни данные из столбца title_id  не включены в результат. Однако само соединение стало возможным лишь при использовании этой таблицы как промежуточной.

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

 

select titles.title_id, total_sales, lorange, hirange, royalty

from titles, roysched

where titles.title_id = roysched.title_id

  and total_sales >= lorange and total_sales < hirange

 

title_id     total_sales           lorange            hirange               royalty

-----------------------  ----------                -------          ----

 BU1032    4095                            0                        5000                       10

 BU1111    3876                            0                        4000                       10

 BU2075  18722                      14001                      50000                      24

 BU7832    4095                            0                        5000                       10

 MC2222  2032                       2001                       4000      12

 MC3021  2224                       12001                      50000                    24

 PC1035     8780                      4001                       10000                      16

 PC8888     4095                            0                        5000                       10

 PS1372       375                            0                        10000                     10

 PS2091     2045                      1001                       5000                       12

 PS2106       111                            0                        2000                       10

 PS3333     4072                            0                        5000                       10

 PS7777     3336                            0                        5000                       10

 TC3218       375                            0                        2000                       10

 TC4203   15096                     8001                       6000                        14

 TC7777     4095                            0                        5000                       10

 

                       (Выбрано 16 строк)

 

Когда в одном операторе совмещаются несколько соединений или когда соединяются более двух таблиц, “соединительные выражения” обычно связываются логической операцией and (И), как это было в предыдущих примерах. Однако, их можно связывать и логической операции or (ИЛИ).

 

 

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

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

Операции внешнего соединения в языке Transact-SQL имеют следующий вид:

 

Таблица 4.3. Список операций внешнего соединения 

 

Операция	Действие
*=	В результат включаются все строки из первой таблицы.
=*	В результат включаются все строки из второй таблицы.

 

Напомним, что запрос, в котором искались авторы, проживающие в одном городе с издателем, возращал двух людей: Абрахама Беннета и Черил Карсон. Чтобы включить в результат всех авторов независимо от местонахождения издателя, необходимо использовать внешнее соединение. Соответствующий запрос и его результаты имеют следующий вид:

 

select au_fname, au_lname, pub_name

from authors, publishers

where authors.city *= publishers.city

 

au_fname      au_lname         pub_name

-----------        --------------      ----------------------------

Johnson         White             NULL

Marjorie        Green             NULL

Cheryl           Carson            Algodata Infosystems

Michael         O'Leary          NULL

Dick              Straight           NULL

Meander        Smith              NULL

Abraham       Bennet            Algodata Infosystems

Ann               Dull                 NULL

Burt              Gringlesby       NULL

Chastity        Locksley          NULL

Morningstar  Greene             NULL

Reginald       Blotche-Halls   NULL

Akiko           Yokomoto        NULL

Innes            del Castillo       NULL

Michel         DeFrance          NULL

Dirk             Stringer            NULL

Stearns         MacFeather      NULL

Livia            Karsen              NULL

Sylvia          Panteley            NULL

Sheryl          Hunter              NULL

Heather        McBadden       NULL

Anne           Ringer               NULL

Albert         Ringer               NULL

 

(Выбраны 23 строки)

 

Операция сравнения “*=” отличает внешнее соединение от обычного. Это “левое” внешнее соединение, которое сообщает SQL Серверу, что необходимо включить в результат все строки первой таблицы authors, независимо от результата сравнения их с полем city таблицы publishers. Заметим, что для большинства авторов результат сравнения отрицательный, поэтому в столбец pub_name в этом случае записывается неопределенное значение NULL. Заметим также, что правая таблица publishers  называется в этом случае внутренней таблицей внешнего соединения.

 

 

Замечание. Поскольку столбцы с данными типа “бит” не допускают неопределенных значений, то при их внешнем соединении в соответствующих позициях записывается “0”.

 

“Правое” внешнее соединение задается операцией сравнения “=*”, которая указывает, что в результат должны включаться все строки из второй таблицы независимо от выполнения условия сравнения с соответствующим полем первой таблицы. В этом случае первая таблица называется внутренней.

Если ввести эту операцию в предыдущий запрос, то получим следующий результат:

 

select au_fname, au_lname, pub_name

from authors, publishers

where authors.city =* publishers.city

 

au_fname     au_lname    pub_name

---------        ---------       --------------------

NULL         NULL        New Age Books

NULL         NULL        Binnet & Hardley

Cheryl         Carson      Algodata Infosystems

Abraham     Bennet      Algodata Infosystems

 

(Выбраны 4 строки)

 

Можно и дальше уточнять результаты внешнего соединения путем сравнения их с константой. Это означает, что точно будут указываться только те величины, которые действительно необходимы, а остальные как бы оказываются за чертой. Для примера рассмотрим сначала эквисоединение, а затем сравним его с внешним соединением. Предположим, что необходимо найти все книги, объем продаж которых в некотором магазине оказался больше 500 экземпляров:

 

select distinct salesdetail.stor_id, title

from titles, salesdetail

where qty > 500

and salesdetail.title_id = titles.title_id

 

stor_id      title                                                                            

-------       ------------------------------------------------------------------------

5023       Sushi, Anyone?

5023       Is Anger the Enemy?

5023       The Gourmet Microwave 

5023       But Is It User Friendly?

5023       Secrets of Silicon Valley 

5023       Straight Talk About Computers 

5023       You Can Combat Computer Stress! 

5023       Silicon Valley Gastronomic Treats 

5023       Emotional Security: A New Algorithm

5023       The Busy Executive's Database Guide

5023       Fifty Years in Buckingham Palace Kitchens

5023       Prolonged Data Deprivation: Four Case Studies

5023       Cooking with Computers: Surreptitious Balance Sheets

7067       Fifty Years in Buckingham Palace Kitchens

 

(Выбрано 14 строк)

 

Чтобы увидеть кроме того книги, объем продаж которых ни в одном магазине не был больше 500 экземпляров, можно использовать внешнее соединение:

 

select distinct salesdetail.stor_id, title

from titles, salesdetail

where qty > 500

and salesdetail.title_id =* titles.title_id

 

 stor_id     title                                                                            

 -------       ---------- ------------------------------------------------------------

 NULL      Net Etiquette 

 NULL      Life Without Fear 

 5023        Sushi, Anyone?

 5023        Is Anger the Enemy?

 5023        The Gourmet Microwave 

 5023        But Is It User Friendly? 

 5023        Secrets of Silicon Valley 

 5023        Straight Talk About Computers 

 5023        You Can Combat Computer Stress!

 5023        Silicon Valley Gastronomic Treats 

 5023       Emotional Security: A New Algorithm 

 5023       The Busy Executive's Database Guide 

 5023       Fifty Years in Buckingham Palace Kitchens

 7067       Fifty Years in Buckingham Palace Kitchens

 5023       Prolonged Data Deprivation: Four Case Studies 

 5023       Cooking with Computers: Surreptitious Balance Sheets 

 NULL     Computer Phobic and Non-Phobic Individuals: Behavior Variations 

 NULL      Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean

 

(Выбрано 18 строк)

 

 

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

 

select distinct sales.stor_id, stor_name, title

from sales, stores, titles, salesdetail

where qty > 500

and salesdetail.title_id =* titles.title_id

and sales.stor_id = salesdetail.stor_id

and sales.stor_id = stores.stor_id

 

Msg 303, Level 16, State 1:

Server 'RAW', Line 1:

The table 'salesdetail' is an inner member of an outer-join clause. This is not allowed if the table also participates in a regular join clause.

 

(Таблица ‘salesdetail’ является внутренним членом внешнего соединения. Это недопустимо, поскольку эта таблица также участвует в обычном соединении.)

 

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

 

 

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

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

 

Таблица 1:

 a                   b      

------------      -------- 

        1           one  

   NULL        three  

        4           join4    

 

Таблица 2:

c                   d      

---------         ------- 

   NULL       two    

        4          four   

 

Левое внешнее соединение:

select *

from t1, t2

where a *= c

 

 a          b           c             d      

 -------  -------     --------     -------- 

     1      one       NULL     NULL

NULL  three      NULL     NULL

     4     join4               4     four

 

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

 

 

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

 

sp_helpjoins таблица1, таблица2

 

Например, можно вызвать эту процедуру с таблицами titleauthor и titles в качестве аргументов:

 

sp_helpjoins titleauthor, titles

 

Процедура SP_HELPJOINS выбирает пары столбцов для соединения на основе двух условий. Во-первых, просматривается таблица syskeys (системные ключи) текущей базы даных для поиска ключей импорта (foreign keys) процедурой SP_FOREIGNKEY. Затем проверяется есть ли у этих таблиц общие ключи с помощью процедуры SP_COMMONKEY. Если общих ключей нет, то ищутся любые ключи, подходящие для соединения. Наконец, если таких ключей найти не удалось, то выбираются столбцы с одинаковым названием или одинаковым типом данных.

Более полная информация о системных процедурах дается в Справочном руководстве по SQL Серверу.

 

[ << Назад ] [ Содержание ] [ Далее >> ]