пользуя только таблицу invoices? Нам не удастся ответить на этот вопрос, используя только один запрос и ранее полученные навыки. Таблица invoices не содержит имена клиентов. Однако таблица invoices содержит поле CustomerId. Чтобы отобразить список счетов с именами клиентов, которым выставлены данные счета, нам необходимы таблицы invoices и customers.
Мы видим, что таблица customers (рис. 63) содержит требуемую нам информацию — имена и фамилии всех клиентов компании sTunes. Данная таблица также содержит поле CustomerId. Если мы внимательно посмотрим на значки в таблице customers, то увидим, что рядом с полем CustomerId расположен небольшой значок ключа. В главе 1 мы говорили о том, что значок ключа — это символ первичного ключа, уникального поля идентификации для конкретной таблицы.
НАПОМИНАНИЕ
Каждая таблица должна содержать хотя бы одно поле, служащее первичным ключом. Первичный ключ одной таблицы, как правило, является внешним ключом для другой таблицы.
Рис. 63
Поскольку CustomerId — это первичный ключ таблицы customers, а таблица invoices содержит аналогичное поле с тем же именем, то эти два поля CustomerId — это связь, необходимая для одновременного доступа к обеим таблицам. Теперь у нас есть вся информация, необходимая для объединения этих двух таблиц и создания списка счетов с именами клиентов.
Рассмотрим пример использования операции соединения JOIN, объединяющей эти таблицы.
SELECT
*
FROM
invoices
INNER JOIN
customers
ON
invoices.CustomerId = customers.CustomerId
Примечание
В данном примере мы используем соединение, называемое INNER JOIN (внутреннее соединение). Далее мы рассмотрим несколько различных типов соединений. Каждое работает по своему правилу. На данный момент вам лишь необходимо знать, что соединения позволяют нам получать доступ к полям из разных таблиц.
Вам уже знакомы многие операторы данного запроса. Запрос начинается с SELECT, как и все наши предыдущие запросы. Мы используем символ *, чтобы получить все поля в таблице. Выберем все поля из таблицы invoices и объединим их со всеми полями в таблице customers. Ключевое слово ON используется в запросе для связи двух таблиц через поле CustomerId. Поскольку существует два варианта поля CustomerId (по одному в каждой таблице), нам нужно сообщить браузеру SQL, какой из вариантов использовать. Для этого существует специальная нотация (в форме tablename.FieldName). Устанавливаем равенство между полем CustomerId из таблицы invoices (записанное как invoices.CustomerId) и полем CustomerId из таблицы customers (customers.CustomerId). Результат показан на рис. 64.
Рис. 64
Примечание
Используя символ *, мы объединили девять полей таблицы invoices с тринадцатью полями таблицы customers. В результате получилось двадцать два поля. Для удобства печати мы убрали некоторые из этих полей, но мы можем просмотреть все двадцать два поля в браузере, используя горизонтальную полосу прокрутки на панели результатов на вкладке Execute SQL (Выполнить SQL-запрос).
Соединения и структура реляционной базы данных
Теперь проанализируем, что произойдет, если объединить таблицу invoices с таблицей customers. Рассмотрим поле InvoiceId из таблицы invoices результирующего набора данных (см. рис. 64). Мы увидим, что первые семь записей связаны с одним и тем же CustomerId. Это означает, что клиенту № 1 выставили все семь счетов. Если мы проанализируем часть результирующей таблицы customers, то увидим, что данного клиента зовут Lus Gonalves. Один клиент связан со многими счетами. На языке реляционной базы данных (глава 1) мы можем сказать, что таблица customers имеет связь «один-ко-многим» с таблицей invoices. Клиенту с одним CustomerId могут выставить множество счетов (если он заказал несколько песен), но в таблице invoices сохранится один и тот же номер CustomerId. Еще один вариант описания этой связи — схема базы данных в виде ER-диаграммы (ER от Entity — Relationship, сущность — связь).
На рис. 65 графически представлены взаимосвязи между таблицей customers и таблицей invoices. В остальной части нашей базы данных мы обнаружим множество других первичных и внешних ключей, устанавливающих связь между разными таблицами. Понимание этой взаимосвязи — это и есть часть создания и использования соединений. Чтобы объединить таблицы вместе, мы должны уметь идентифицировать первичные и внешние ключи и понимать, какие поля нам необходимы.
Если бы таблица invoices содержала поле, включающее все имена клиентов, то в соединении таблиц не было бы необходимости. Также вместо базы данных с тринадцатью таблицами можно было бы создать одну гигантскую таблицу, содержащую все поля.
Вопрос. Зачем в базах данных нужно иметь несколько таблиц?
Ответ. Нормализация — это процесс организации данных в реляционной базе данных. Нормализация включает создание таблиц и установку отношений между таблицами. Нормализация позволяет уменьшить размеры баз данных, так как не надо хранить повторяющиеся поля в одной таблице. По мере увеличения размера базы данных возрастает потребность в ее нормализации. Это целесообразно, даже если экономится всего нескольких секунд при обработке запроса. Учитывая гигантский размер некоторых баз данных, каждая секунда имеет значение. Представьте, что поиск в Google занимает пять минут, а не несколько секунд.
Рис. 65
Теперь, когда мы определили общую связь между двумя полями в таблице invoices и в таблице customers, стоит более внимательно разобрать, как писать запросы с соединениями.
Псевдонимы соединяемых таблиц
Из первого примера видно, что при обращении к полям соединения JOIN имеют особый синтаксис. Поскольку две таблицы в любой базе данных могут содержать поля с одинаковыми именами, при создании соединений необходимо для указания определенного поля задать имя таблицы, чтобы браузер SQL точно знал, какое поле имеется в виду. По правилам синтаксиса необходимо сначала указать имя таблицы, а затем через точку — имя поля. Чтобы уменьшить объем текста и повысить удобочитаемость, в соединениях часто используются псевдонимы. Следующие два соединения идентичны по функциям.
SELECT
*
FROM
invoices
INNER JOIN
customers
ON
invoices.CustomerId = customers.CustomerId
-----------------------
SELECT
*
FROM
invoices AS i
INNER JOIN
customers AS c
ON
i. CustomerId = c.CustomerId
Примечание
Псевдонимы для соединений должны быть краткими и удобочитаемыми. Они, как правило, состоят из одной буквы, причем используется первая буква соответствующей таблицы (tablename.FieldName будет записано как t.FieldName). Далее в этой главе для имен таблиц мы будем использовать однобуквенные псевдонимы.
Чтобы продемонстрировать необходимость использования псевдонимов при работе с соединениями, давайте рассмотрим наш первоначальный сценарий. Руководству sTunes необходим список с указанием имен клиентов и счетов, выставленных каждому клиенту. Когда в этой главе мы писали наше первое соединение, для выбора всех полей из каждой таблицы был использован символ *. Результатом данного запроса стал массивный набор результатов из двадцати двух полей. Однако нам требуются только имена клиентов и информация о счете. Также, когда мы используем символ *, мы не можем контролировать порядок отображения полей. Предположим, что руководству sTunes необходимо в списке клиентов сначала отображать фамилию. Чтобы определить порядок вывода, следует в операторе SELECT вместо символа * указать имена полей. Давайте создадим аналогичное приведенному выше соединение, но на этот раз в запросе укажем, что из таблицы customers необходимо отобразить поля LastName и FirstName, а из таблицы invoices — поля InvoiceId, CustomerId, InvoiceDate и Total. Поскольку мы работаем с двумя таблицами, содержащими отдельные поля с одинаковыми именами, в условии SELECT следует использовать обозначение tablename.FieldName, как и в условии ON наших соединений, но с одним изменением: вместо полного имени таблицы мы зададим псевдоним, состоящий из первой буквы таблицы, за которой следует точка, а затем имя поля. Наконец, необходимо отсортировать результаты по фамилии клиента. В результате запрос будет выглядеть следующим образом:
SELECT
c. LastName,
c. FirstName,
i. InvoiceId,
i. CustomerId,
i. InvoiceDate,
i. Total
FROM
invoices AS i
INNER JOIN
customers AS c
ON
i. CustomerId = c.CustomerId
ORDER BY
c. LastName
Рис. 66
Примечание
Кажется странным, что мы ссылаемся на псевдонимы (в условии SELECT), прежде чем определим их (в условиях FROM и INNER JOIN). Но следует помнить, что браузер SQL обрабатывает запросы не в той очередности, как их прочел бы человек.
Анализируя первые десять результатов данного запроса, мы видим, что перечисление определенных полей в заданном порядке намного проще, чем вывод всех полей с использованием символа *. Мы также можем представить, насколько сложнее было бы соединение, если бы нам пришлось вводить имя таблицы каждый раз, когда мы ссылаемся на имя поля.
Примечание
В большинстве случаев в условии SELECT рекомендуется указывать отдельные имена полей и избегать использования символа *. В этой главе мы будем использовать символ * только в демонстрационных целях для объяснения структуры JOIN.
Типы соединений
Существует несколько различных типов соединений. До сих пор мы использовали соединения, чтобы предоставить доступ к полям из нескольких таблиц. Мы определили первичный ключ таблицы customers, определили аналогичный внешний ключ в таблице invoices и использовали ключевое слово ON, чтобы связать две таблицы вместе.
Вопрос. Что произойдет, если данные из таблиц, которые мы объединяем, не полностью совпадают?
Например, что произойдет, если клиент — назовем его Customer 6 — удалил свою учетную запись в sTunes и впоследствии был удален из таблицы customers? Поскольку компания sTunes должна вести финансовую отчетность, в таблице invoices все еще содержится информация о том, что Customer 6 в какой-то момент совершил покупку. Нет ничего странного и необычного в обнаружении несовпадений в базах данных, и нам необходимо решить, хотим ли мы, чтобы наши запросы содержали несовпадающие данные или полностью их исключить. Для обработки несовпадений между таблицами используются разные типы соединений. Чтобы понять это, рассмотрим упрощенные версии наших таблиц invoices и customers.
Примечание
Следующие таблицы незначительно отличаются от таблиц в нашей базе данных sTunes. Мы сохранили базовую структуру обеих таблиц, как в invoices и customers. Но мы удалили некоторые поля, сократили число записей в каждой таблице до пяти, упростили имена записей и добавили в каждую таблицу несколько записей, которые отличаются от записей в другой таблице.
Рис. 67
Анализируя упрощенный вариант таблиц invoices и customers, мы можем выявить несколько несовпадений. Прежде всего, из таблицы invoices видно, что клиент с идентификатором Customer 6 совершил покупку 01.05.2017, но этот клиент в таблице customers не отображается. Кроме того, кажется, что Customer 1 и Customer 5 вообще никогда ничего не покупали, поскольку данные идентификаторы отсутствуют в таблице invoices. Customer 2 появляется дважды, поэтому мы можем сделать вывод, что этот клиент совершил две покупки. Поскольку записи для Customer 1 и Customer 5 существуют в таблице customers, но отсутствуют в таблице invoices, а Customer 6 существует только в таблице invoices, то каждая таблица содержит по крайней мере одну уникальную запись, которой нет в другой таблице. Теперь мы можем попытаться объединить эти две таблицы и проанализировать, как обрабатывается результат в зависимости от используемого типа соединения. Рассмотрим соединение INNER JOIN.
Внутреннее соединение (INNER JOIN)
При использовании внутреннего соединения возвращаются только совпадающие записи. Любые несовпадающие данные из любой таблицы игнорируются. Соединения часто описываются диаграммами Венна (рис. 68). Внутреннее соединение представляет собой только перекрывающуюся часть диаграммы Венна.
В данном примере соединение INNERJOIN игнорирует Invoice 5 из таблицы invoices, так как этот счет относится к клиенту Customer 6, которого нет в таблице customers. Точно так же клиентам Customer 1 и Customer 5 (из таблицы customers) не выставляли никаких счетов, поэтому эта запись также игнорируется. Как показано на диаграмме Венна, включены только перекрывающиеся данные. На рис. 69 наглядно продемонстрировано создание внутреннего соединения двух таблиц с разными данными.
Рис. 68
Рис. 69
НАПОМИНАНИЕ
Как мы уже говорили ранее, поле CustomerId имеет отношение «один-ко-многим» с таблицей invoices. Хотя эти данные могут показаться несоответствующими, в нашем случае в результате вернутся четыре записи. Это происходит потому, что клиенту Customer 2 было выставлено два отдельных счета.
Ниже представлен пример использования внутреннего соединения. Сначала в условии SELECT перечислены поля, которые необходимо отобразить, при этом использованы псевдонимы.
SELECT
i. InvoiceId,
c. CustomerId,
c. Name,
c. Address,
i. InvoiceDate,
i. BillingAddress,
i. Total
FROM
invoices AS i
INNER JOIN
customers AS c
ON
i. CustomerId = c.CustomerId
Примечание
Поскольку внутренние соединения возвращают только совпадающие данные, порядок перечисления таблиц не имеет значения. Порядок будет иметь значение для других типов соединений.
Рис. 70
В результате выполнения данного запроса мы видим, что возвращено только четыре записи. Invoice 5, Customer 1 и Customer 5 не указаны. Для Customer 2 отображаются две записи.
Внутреннее соединение — это наиболее распространенный тип соединения. Оно используется для соединения соответствующих данных из разных таблиц реляционной базы данных.
Примечание
Слово «внутреннее» необязательно. Под всеми соединениями понимается внутренние соединение, если не указано иное. Другими словами, по умолчанию все соединения — внутренние.
Левое внешнее соединение (LEFT OUTER JOIN)
Левое внешнее соединение LEFT OUTER JOIN соединяет все записи из левой таблицы с любыми совпадающими записями из правой таблицы. На рис. 71 показан эквивалент диаграммы Венна для данного типа соединения.
Рис. 71
Примечание
Понятия «левая таблица» и «правая таблица» полностью зависят от порядка перечисления данных таблиц в соединении JOIN. Изменение порядка перечисления приведет к другому результату. Это различие станет важным, когда вы узнаете, как преобразовывать левые соединения в правые.
При данном типе соединения будет отображаться все, что содержится в таблице invoices. Поскольку Customer 1 не заказывал песни, данная запись не указывается. Однако, как показано на рис. 72, мы объединяем все пять записей из таблицы invoices только с четырьмя записями из таблицы customers. Помните, что в таблице invoices записи для Customer 1 или Customer 5 отсутствуют, а клиенту Customer 2 было выставлено два счета. В отличие от внутреннего соединения, которое отображает равное количество записей из каждой таблицы, в результате использования левого внешнего соединения вернется больше записей из «левой» таблицы. Проанализируем результат данного запроса, чтобы понять работу браузера SQL.
Рис. 72
SQL-запрос для левого внешнего соединения аналогичен запросу, который мы использовали для внутреннего соединения. Отличие только в использовании условия LEFTOUTERJOIN.
SELECT
i. InvoiceId,
c. CustomerId,
c. Name,
c. Address,
i. InvoiceDate,
i. BillingAddress,
i. Total
FROM
invoices AS i
LEFT OUTER JOIN
customers AS c
ON
i. CustomerId = c.CustomerId
Рис. 73
Примечание
Слово «внешнее» (OUTER) необязательно.
Когда мы анализируем результаты, полученные при использовании левого соединения, мы видим, что браузер SQL добавил данные типа Null. Помните, что информация о Customer 6 в таблице customers отсутствует. Добавление данных типа Null показывает, как браузер SQL обрабатывает нашу попытку сопоставить пять записей из таблицы invoices только с четырьмя записями из таблицы customers. Использование левого соединения полезно, так как это позволяет нам видеть несовпадения в наших данных. Мы можем создавать списки клиентов, которым не выставляли счета, или выполнять поиск данных, которые были удалены в правой таблице, но все еще существуют в левой.
Правое внешнее соединение (RIGHT OUTER JOIN)
Внимание
В SQLite не поддерживается использование правого внешнего соединения. Но мы эту тему рассмотрим, поскольку правое внешнее соединение по-прежнему популярно в других реализациях РСУБД. Позже мы рассмотрим обходной путь для применения правых соединений в SQLite.
В результате использования правого внешнего соединения RIGHT OUTER JOIN возвращаются все данные из правой таблицы, а также соответствующая информация из левой таблицы. Правое соединение — зеркальное отображение левого соединения.
Рис. 74
При использовании правого соединения берутся все поля из правой таблицы (customers) и ее данные сопоставляются с любыми соответствующими данными из таблицы invoices. Поскольку Customer 6 в таблице customers отсутствует, эта запись игнорируется.
Рис. 75
Оператор SQL, необходимый для создания правого соединения, такой же, как и в двух других соединениях, которые мы рассмотрели ранее.
Примечание
Ключевое слово «внешнее» (OUTER) необязательно. Вариант RIGHT JOIN дает тот же результат.
SELECT
i. InvoiceId,
c. CustomerId,
c. Name,
c. Address,
i. InvoiceDate,
i. BillingAddress,
i. Total
FROM
invoices AS i
RIGHT OUTER JOIN
customers AS c
ON
i. CustomerId = c.CustomerId
Рис. 76
В результате использования данного соединения вернулось наибольшее количество записей из трех изученных ранее соединений. Записей Customer 1 и Customer 5 нет в таблице invoices, поэтому им присвоены значения Null. Две записи из таблицы invoices относятся к Customer 2, поэтому в результате объединения данные Customer 2 были указаны дважды.
Правые соединения используются реже, чем левые. Поскольку SQLite не распознает правое соединение, в запросе рекомендуется изменить порядок таблиц, что приведет к тому же набору результатов. Эту тему мы рассмотрим позже в этой главе.