SQL: быстрое погружение

Обратите внимание, что до этого момента имена двух полей, связанных друг с другом оператором 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