2.2.8 Чтение данных из нескольких таблиц с помощью опера­ции соединения

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

Основная идея здесь — создать новое отношение, связав между собой содержимое двух или более исходных отношений. Рассмотрим следующий пример:

SELECT Title, AskingPrice
FROM WORK, TRANSACTION
WHERE WORK.WorkID = TRANSACTION.WorkID;

Смысл этого оператора заключается в том, что создается новая таблица с двумя столбцами Title и AskingPrice. Эти столбцы берутся соответствен­но из таблиц WORK и TRANSACTION при условии, что столбец WorkID в таблице WORK равен одноименному столбцу в таблице TRANSACTION. Обозначения WORK.WorkID и TRANSACTION.WorkID необходимы для устранения конфликта имен столбцов.

Эту операцию можно представить себе следующим образом. Начнем с первой строки таблицы TRANSACTION. Возьмем значение столбца WorkID в данной строке (в табл. 1.3 это 505) и просмотрим строки табли­цы WORK (табл. 1.2). Найдя в таблице WORK строку, где WorkID так­же равняется 505, соединим столбец Title только что найденной первой строки таблицы WORK со столбцом AskingPrice первой строки таблицы TRANSACTION.

Первая строка таблицы WORK (см. табл. 1.2) имеет WorkID, равный 505, поэтому мы соединяем столбец Title первой строки таблицы WORK со столбцом AskingPrice первой строки таблицы TRANSACTION, и получаем тем самым первую строку соединения:

Mystic Fabric 20000

Теперь, по-прежнему используя WorkiD=505. найдем следующую строку в отношении WORK, которая имеет WorkID, равный 505. Та­ких строк больше нет, поэтому переходим ко второй строке таблицы TRANSACTION. Так как в этой строке WorkID так же равен 505, снова найдем первую строку в отношении WORK, которая имеет WorkID, равный 505. Соединив столбец Title первой строки таблицы WORK со столбцом AskingPrice второй строки таблицы TRANSACTION, мы получим вторую строку соединения:

Mystic Fabric 20000
Mystic Fabric 47000

Таким образом перебирая все строки отношения TRANSACTION получаем окончательный результат:

Mystic Fabric	20000
Mystic Fabric	47000
Mystic Fabric	10000
Mi Vida	17500
Mi Vida	75000
Slow Embers	15000
Mystic Fabric
Northwest by Night	72500

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

SELECT Title, SUM(AskingPrice)
FROM WORK, TRANSACTION
WHERE WORK.WorkID = TRANSACTION.WorkID
GROUP BY Title;

Результатом будет следующая таблица:

Slow Embers                       15000
Northwest by Night              72500
Mystic Fabric                       77000
Mi Vida                               92500

Предложение WHERE мы также можем применить в процессе создания соединения:

SELECT Title, AskingPrice
FROM WORK, TRANSACTION
WHERE WORK.WorkID = TRANSACTION.WorkID
AND AskingPrice > 20000;

Результат соединения будет таким:

Mystic Fabric 47000
Mi Vida 75000
Northwest by Night 72500

Это такая же таблица, поэтому все описанные выше SQL-операторы к ней применимы.

Теперь рассмотрим случай, когда требуется соединить три таблицы. Предположим, нам нужно знать по каждой картине вырученную сум­му и имя художника. Для этого нам нужно соединить три таблицы: TRANSACTION (поле AskingPrice), WORK (поле Title) и ARTIST (поле Name):

SELECT Title, AskingPrice, Name
FROM WORK, TRANSACTION, ARTIST
WHERE WORK.WorkID = TRANSACTION.WorkID AND WORK.ArtistID = ARTIST.Artist ID;

Результатом соединения будет следующая таблица:

Mystic Fabric	20000	Tobey
Mystic Fabric	47000	Tobey
Mystic Fabric	10000	Tobey
Mi Vida	17500	Miro
Mi Vida	75000	Miro
Slow Embers	15000	Tobey
Mystic Fabric		Tobey
Northwest by Night	72500	Chagall

В стандарте SQL-92 введен альтернативный синтаксис соединения, ставший весьма популярным, поскольку его легче интерпретировать. Здесь вместо WHERE используются ключевые слова JOIN и ON:

SELECT Title, AskingPrice
FROM WORK JOIN TRANSACTION
ON WORK.WorkID = TRANSACTION.WorkID;

Результат имеет следующий вид:

Mystic Fabric	20000
Mystic Fabric	47000
Mystic Fabric	10000
Mi Vida	17500
Mi Vida	75000
Slow Embers	15000
Mystic Fabric
Northwest by Night	72500

Сделать оператор соединения более удобным для чтения можно, ис­пользуя псевдонимы для имен таблиц. Выражение FROM WORK W при­сваивает таблице WORK псевдоним W. Это позволяет переписать преды­дущий оператор следующим образом:

SELECT Title, AskingPrice
FROM WORK W JOIN TRANSACTION T
ON W.WorkID = T.WorkID;

Соединение трех таблиц можно выполнить путем добавления предло­жения JOIN в конец первого оператора SELECT:

SELECT W.Title, AskingPrice, A.Name FROM WORK W JOIN TRANSACTION T
ON W.WorkID = T.WorkID
JOIN ARTIST A
ON W.ArtistID = A.ArtistID;

Следует обратить внимание, что когда требуется указать имя столбца в первой строке оператора SELECT, необходимо использовать псевдоним, а не имя таблицы. Так, в выше приведенном операторе SELECT исполь­зуется запись W.Title, а не WORK.Title.

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

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

SELECT DISTINCT Title
FROM WORK JOIN TRANSACTION
ON WORK.WorkID = TRANSACTION.WorkID AND AskingPrice > 20000;

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

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

SELECT DISTINCT Name
FROM ARTIST
WHERE ArtistID IN
(SELECT ArtistID
FROM WORK
WHERE WorkID IN
(SELECT WorkID
FROM TRANSACTION
WHERE AskingPrice > 20000));

Если нужно включить в результат столбцы WORK.Title, придется ис­пользовать соединение:

SELECT A.Name, W.Title
FROM ARTIST A JOIN WORK W
ON A.ArtistID = W.ArtistID
JOIN TRANSACTION
ON TRANSACTION.WorkID = W.WorkID
AND AskingPrice > 20000;