Использование SQLite для изучения связей между данными и сравнения таблиц

SQLite предоставляет множество способов просмотра и сравнения данных в нескольких таблицах. В этом блоге будут обсуждаться запросы SQLite, которые можно использовать для просмотра связей данных:

• INNER JOIN
  
• LEFT JOIN
  
• Использование нескольких операторов соединения
  

И запросы SQLite, которые можно использовать для сравнения таблиц:

• INTERSECT
• UNION
  
• UNION ALL
  
• EXCEPT
  

Простые запросы выбора

Чтобы проиллюстрировать, как использовать операторы SELECT, я создал базу данных для отслеживания садовых растений и грядок, на которые я ссылаюсь в примерах, которые использую. В базе данных есть таблица растений, содержащая информацию о различных садовых растениях, включая название растения, требует ли оно солнечного света и время года, когда растение начинает цвести. Данные в таблице растений можно получить с помощью запроса SELECT:

В столбце full_sun целые числа 1 и 0 используются для представления логических значений 'true' и 'false' соответственно.

Чтобы ограничить столбцы результатов включением только идентификационного номера и названия растения, столбцы можно указать в инструкции SELECT вместо использования *:

Выбор запросов с операторами соединения

Извлечение данных из одной таблицы имеет свои применения, но оно не позволяет мне легко увидеть связи между данными в нескольких таблицах. Вы можете использовать операторы соединения для одновременного просмотра данных в нескольких таблицах.

В нашей базе данных также есть таблица с информацией по грядкам:

beds

Один и тот же тип растения можно посадить на нескольких грядках, а на грядке может быть несколько видов растений. Чтобы отслеживать взаимоотношения между растениями и грядками, существует таблица соединений, называемая plant_beds:

plant_beds

Взаимосвязь между таблицами plants, beds и plant_beds можно визуализировать следующим образом:

INNER JOIN

INNER JOIN можно использовать для просмотра растений, соответствующих каждой строке таблицы соединения plant_bed. SQLite обрабатывает операторы «INNER JOIN», «JOIN» и «,» одинаково, поэтому их можно использовать как взаимозаменяемые.

Не забудьте включить в запрос оператор ON. Оператор ON сообщает SQLite, как таблицы связаны друг с другом. Без этой инструкции SQLite вернет каждую строку из таблицы plant_beds, совпадающую с каждой строкой из базы данных растений:

Этот результат включает в ответ петунии, хотя в настоящее время они не связаны ни с одной грядкой в ​​нашей соединительной таблице plant_beds. Таким образом, хотя SQLite вернул информацию как из таблицы Plants, так и из таблицы Plant_beds, ответ не отражает связь между двумя таблицами.

LEFT JOIN

Ответ на запрос INNER JOIN включает только те строки из каждой таблицы, которые соответствуют оператору ON. Итак, в приведенном выше примере Петуния не включается в ответ, поскольку она не включена в таблицу plant_beds. Чтобы просмотреть все строки из левой или правой таблицы, вместо INNER JOIN следует использовать LEFT JOIN или LEFT OUTER JOIN. Ответ на запрос LEFT JOIN будет включать в себя те же строки, что и запрос INNER JOIN, а также дополнительную строку из каждой строки в левой таблице (или первой таблице, указанной в запросе), которая не имеет соответствующей строки в правая таблица (вторая таблица, указанная в запросе). SQLite использует NULL в качестве значения по умолчанию для любого столбца, который не имеет значения в правой таблице.

Как видно из этого примера, результат запроса LEFT JOIN включает строку Petunia из таблицы Plants и использует значения NULL, которые отображаются в виде пробелов, для столбцов, где нет соответствия для Petunia в таблице plant_beds.

Использование оператора JOIN с более чем двумя таблицами

Операторы INNER JOIN и LEFT JOIN могут повторяться в запросе для получения результата, включающего информацию из более чем одной таблицы. Например, чтобы просмотреть информацию из таблицы plant_beds, а также сведения из таблиц Plant_beds, можно использовать два оператора INNER JOIN.

Составные операторы выбора

Что, если вместо того, чтобы перечислять все растения в одной таблице, растения были отсортированы по нескольким таблицам на основе характеристик каждого растения? Я составил еще три таблицы базы данных, чтобы отслеживать местные растения, растения, которые особенно привлекательны для пчел, и растения, которые могут привлечь колибри. Конечно, есть местные растения, которые хорошо подходят для пчел и колибри, поэтому в трех таблицах есть некоторые общие растения.

native_plants

bee_plants

hummingbird_plants

Эти таблицы также можно визуализировать следующим образом:

Теперь, когда растения организованы в несколько таблиц, было бы неплохо иметь возможность сравнивать таблицы друг с другом. Это можно сделать с помощью составных операторов SELECT. В частности, с операторами INTERSECT, UNION, UNION ALL и EXCEPT.

INTERSECT

INTERSECT используется для получения общих столбцов и строк, которые имеют таблицы.

Чтобы запрос INTERSECT работал, столбцы в каждом столбце SELECT должны совпадать. Операторы SELECT должны иметь одинаковое количество столбцов, и столбцы должны возвращать один и тот же тип информации, чтобы SQLite мог сравнивать значения каждого столбца.

Обратите внимание, что в этом примере инструкция SELECT не использует id или * для получения столбца id из таблиц. Хотя таблицы Native_plants, bee_plants и hummingbird_plants имеют некоторые общие растения, эти растения не имеют одинаковых идентификационных номеров в каждой таблице. В bee_plants идентификатор Bee Balm равен 1, а Coneflower — 3, тогда как в Native_plants Bee Balm имеет идентификатор 2, а Coneflower — идентификатор 4. Таким образом, если запрос включал столбец id, Bee Balm и Coneflower строки из каждой таблицы не будут совпадать, и оператор INTERSECT не вернет никаких результатов.

UNION или UNION ALL

Чтобы получить информацию из нескольких таблиц, независимо от того, одинаковы ли строки в каждой таблице, используйте оператор UNION.

Оператор UNION вернул строки из таблицы bee_plants и строки из таблицы hummingbird_plants и автоматически удалил повторяющиеся строки. Чтобы вернуть каждую строку из обеих таблиц, даже если строка присутствует в каждой таблице, можно использовать оператор UNION ALL.

Пчелиный бальзам появляется дважды в результате запроса UNION ALL, поскольку он существует как в таблице hummingbird_plants, так и в таблице bee_plants.

EXCEPT

Оператор EXCEPT можно использовать для извлечения строк из одной таблицы, которые не отображаются в другой таблице.

Заключение

Подробную документацию по операторам SELECT SQLite, включая описанные выше операторы и многие другие варианты построения оператора SELECT, можно найти на веб-сайте SQLite.