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

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

 

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

 

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

 

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

 

 

 

Соединение двух и более таблиц можно рассматривать как процесс сравнения данных в указанных столбцах этих таблиц и формирования новой таблицы из строк исходных таблиц, которые дают положительный результат при сравнении. Оператор 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 отдельных названий для таблиц или выюверов. При подсчете максимально допустимого числа нужно учитывать, что отдельным членом этого предложения считаются следующие названия:

 

 

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

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

Вьюверы можно использовать точно также, как и таблицы. В главе 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

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

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 Серверу.