SQL: быстрое погружение - [24]
Рис. 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.
