Можно соединять и более двух таблиц. Чтобы добавить дополнительные таблицы, нужно просто следовать тем же правилам, что мы рассмотрели ранее, когда рассказывали про внутренние соединения. Рассмотрим схему базы данных на рис. 77. Мы видим, что помимо связи между таблицами invoices и customers существует также связь между полем SupportRepId из таблицы customers и полем EmployeeId из таблицы employees.
Рис. 77
Обратите внимание, что до этого момента имена двух полей, связанных друг с другом оператором ON, были идентичны. В данном случае мы связываем два поля с разными именами, хотя из ER-диаграммы видно, что они являются связанными полями. Почему так происходит? Данное несоответствие дает нам важный урок о структуре реляционной базы данных. Два связанных поля необязательно должны иметь одно и то же имя. В компании sTunes каждому клиенту назначен SupportRepId, или персональный менеджер. Номер, присвоенный каждому менеджеру, совпадает с номером сотрудника (таблица employees). Разработчик этой базы данных мог бы назвать оба поля EmployeeId (ИН сотрудника), но это может привести к путанице. Хотя клиенту назначен персональный менеджер и логично, что данные SupportRepId (ИН менеджера) идентичны данным EmployeeId (ИН сотрудника), наличие поля EmployeeId в таблице customers может вызвать путаницу. Два поля, хранящие идентичные данные, имеют разный смысл в каждой таблице. Если поле названо SupportRepId в customers, то его назначение здесь не вызывает вопросов. Чтобы не придумывать другую систему нумерации для EmployeeId, мы можем связать эти две системы с помощью структуры реляционной базы данных (см. рис. 77).
Теперь, когда мы уже знаем, как связать таблицы invoices, customers и employees, необходимо понять, для чего это может понадобиться. Допустим, отдел обслуживания клиентов sTunes хочет поощрить сотрудников, которым удалось совершить десять самых лучших продаж. Служба поддержки клиентов хочет создать для каждого сотрудника табличку со списком их лучших клиентов. Теперь, когда у нас есть рабочий сценарий, мы можем проанализировать ER-диаграмму, чтобы определить, какие поля необходимы для написания запроса. При написании сложных запросов, в которых задействовано несколько таблиц, полезно продумать, какие поля требуются и из каких таблиц (рис. 78).
Рис. 78
Сейчас мы имеем представление о том, какие поля необходимо вывести, и можем приступить к составлению запроса. Начнем с таблицы invoices в условии FROM. Затем последовательно напишем две операции INNERJOIN: одна соединяет счета и клиентов, а другая соединяет и счета, и клиентов с сотрудниками. Затем упорядочим данные по сумме счета (в порядке убывания).
SELECT
e. FirstName,
e. LastName,
e. EmployeeId,
c. FirstName,
c. LastName,
c. SupportRepId,
i. CustomerId,
i. Total
FROM
invoices AS i
INNER JOIN
customers AS c
ON
i. CustomerId = c.CustomerId
INNER JOIN
employees AS e
ON
c. SupportRepId = e.EmployeeId
ORDER BY
i. Total DESC
LIMIT 10
Рис. 79
Теперь имеется список сотрудников sTunes, которые обеспечили самые высокие суммы в счетах. Проанализируем некоторые моменты. Как мы и предполагали, хотя поля, которые мы используем для связи таблиц customers и employees, содержат два разных имени, их данные совпадают. Числовые значения в SupportRepId идентичны значениям в поле EmployeeId.
Практическое задание
• Проанализируйте ER-диаграмму и выберите другую таблицу для добавления к этому запросу с помощью другого внутреннего соединения. Определите необходимые для вывода поля и добавьте их в оператор SELECT.
Использование левых внешних соединений с операторами NULL, IS и NOT
Как мы уже говорили ранее в этой главе, левое внешнее соединение извлекает все данные из левой таблицы и всю соответствующую информацию из правой таблицы. Это полезно для анализа базы данных и проверки неполноты информации. Допустим, компания sTunes проводит внутренний аудит, чтобы уточнить, сколько у нее в ассортименте альбомов и отдельных треков. Руководство sTunes просит создать перечень всех исполнителей, которые не имеют альбомов. Анализируя предыдущую ER-диаграмму, мы можем предположить, что необходимая информация будет храниться в таблицах artists и albums. Давайте рассмотрим взаимосвязь между этими таблицами.
Рис. 80
Таблица artists содержит поле ArtistId (первичный ключ) и поле для имени исполнителя (рис. 80). Из ER-диаграммы видно, что таблица artists связана с таблицей albums связью «один-ко-многим». Эта связь имеет смысл, так как исполнитель может записать несколько альбомов. В таблице albums имеется собственный первичный ключ AlbumId, а также поле ArtistId как внешний ключ.
Используя соединение LEFTOUTERJOIN для таблицы artists (левая таблица), мы вернем все данные из таблицы artists с соответствующими записями (если они есть) в таблице albums. С помощью левого соединения все поля, не имеющие названий альбомов, будут заполнены значениями null. Теперь мы можем создать запрос.
Примечание
В качестве псевдонима для большинства соединений в этой главе мы использовали первую букву имени таблицы. Поскольку мы имеем две таблицы, названия которых начинаются с одной и той же буквы, в данном запросе мы для псевдонимов возьмем по две буквы.
SELECT
ar.ArtistId AS [ArtistId From Artists Table],
al.ArtistId AS [ArtistId From Albums Table],
ar.Name AS [Artist Name],
al.Title AS [Album Title]
FROM
artists AS ar
LEFT OUTER JOIN
albums AS al
ON
ar.ArtistId = al.ArtistId
Запрос возвращает 418 записей, и вначале все результаты кажутся корректными. Поле ArtistId из таблицы artists соответствует полю ArtistId из таблицы albums. Большинство имен исполнителей связаны с названиями альбомов. Однако далее (рис. 81) мы обнаруживаем значения null.
Чтобы решить поставленную нам задачу и получить список исполнителей, у которых нет альбома, необходимо добавить условие WHERE, в котором следует указать только записи со значением NULL в таблице albums. Существуют определенные ключевые слова SQL, которые мы используем для работы со значениями NULL.
• ISNULL в условии WHERE вернет только нулевые значения.
• NOTNULL вернет только значения, которые не были нулевыми.
Рис. 81
Раздел WHEREal.ArtistIdISNULL вернет список исполнителей без названий альбомов.
Внимание
При работе со значениями NULL необходимо использовать операторы IS и NOT, а не оператор равенства =. Нулевые значения указывают на недостаток данных. Оператор = сравнивает значения двух элементов. Нулевые значения не содержат значений, поэтому их нельзя сравнивать, используя оператор =. Использование оператора = приведет к ошибке.
SELECT
ar.ArtistId AS [ArtistId From Artists Table],
al.ArtistId AS [ArtistId From Albums Table],
ar.Name AS [Artist Name],
al.Title AS [Album]
FROM
artists AS ar
LEFT OUTER JOIN
albums AS al
ON
ar.ArtistId = al.ArtistId
WHERE
al.ArtistId IS NULL
Полученные результаты (рис. 82) содержат 71 запись, в которой нет альбомов и имен исполнителей.
Рис. 82
Преобразование правого соединения в левое
Как мы уже говорили, правые соединения в SQLite не поддерживаются. Мы также узнали, что правые соединения — это зеркальное отображение левых соединений. Рассмотрим диаграмму Венна.
При использовании правого соединения берутся все записи с правой стороны и объединяются со всеми соответствующими записями с левой стороны. Если вы просто поменяете местами левую и правую таблицы, то для получения того же результата вы можете использовать левое внешнее соединение. Следующий запрос написан с использованием правого внешнего соединения. В данном запросе любая соответствующая информация об альбоме или названии из таблицы albums объединяется со всеми записями из таблицы tracks.
SELECT * FROM albums AS al RIGHT OUTER JOIN tracks AS t ON t.AlbumId = al.AlbumId
аналогично
SELECT * FROM tracks AS t LEFT OUTER JOIN albums AS al ON t.AlbumId = al.AlbumId
Рис. 83
SELECT
t. TrackId,
t. Composer,
t. Name,
al.AlbumId,
al.Title
FROM
albums AS al
RIGHT OUTER JOIN
tracks AS t
ON
t. AlbumId = al.AlbumId
При выполнении предыдущего запроса возникнет следующая ошибка: RIGHTandFULLOUTERJOINsarenotcurrentlysupported (Правое и полное внешние соединения в настоящее время не поддерживаются).
Однако для решения данной проблемы мы можем просто поменять местами таблицы. Рассмотрим следующий запрос, единственное отличие которого от предыдущего — порядок перечисления таблиц.
SELECT
t. TrackId,
t. Composer,
t. Name,
al.AlbumId,
al.Title
FROM
tracks AS t
LEFT OUTER JOIN
albums AS al
ON
t. AlbumId = al.AlbumId
Выполним его и проанализируем полученные результаты (рис. 84). Мы получим следующую информацию в одном наборе результатов: композитор, название песни и название альбома. Обратите внимание, что в поле Composer (Композитор) обнаружилось несколько нулевых значений. Теперь можно написать другой запрос, чтобы разобраться, в чем дело.
Главный вывод от использования левых/правых соединений: они могут «устранить неполадки» в нашей базе данных и выявить несоответствия в данных.
Если вам нужно найти соответствующие данные и вам не критично потерять несколько записей из-за ошибок в базе данных, то просто пользуйтесь соединением INNER JOINT.
Рис. 84
Практическое задание
• Измените приведенный выше запрос так, чтобы отображались только записи, в которых поле Composer содержит значение NULL.
Контрольные вопросы
1. Используя DB Browser и вкладку Browse Data (Просмотр данных) или ER-диаграмму (рис. 65), проанализируйте таблицу tracks. Определите, какие поля в этой таблице будут внешними ключами в другой таблице. На основании определенных вами внешних ключей определите, какие таблицы связаны с таблицей tracks.