Часть 12.15: Сравнение результатов двух SQL запросов выборки: EXCEPT, INTERSECT и SELECT в SQLite

Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. Данная запись завершает тему, в которой мы так подробно рассматривали использование команды SELECT в SQL. И в этой записи мы поговорим о том, как и за счет чего происходит сравнение результатов двух запросов в SQL. Отметим, что для это операции используется SELECT и две уточняющие фразы EXCEPT и INTERSECT. Сразу отмечу, что ключевое слово EXCEPT даст в результате таблицу, в которой будут только пересекающиеся строки. А ключевое слово INTERSECT даст таблицу с разностью строк.

Сравнение результатов двух SQL запросов выборки: EXCEPT, INTERSECT и SELECT в SQLite

Сравнение результатов двух SQL запросов выборки: EXCEPT, INTERSECT и SELECT в SQLite

В этой записи мы с вами поговорим про использование ключевых слов EXCEPT и INTERSECT в запросах SELECT. И рассмотрим примеры, при помощи которых разберемся как происходит сравнение результатов выборки в SQL и базах данных SQLite: сначала разберемся с пересечением строк, а затем посмотрим на разность строк. 


Использование EXCEPT и INTERSECT в SQL запросах SELECT

Ранее мы разобрались с тем, как объединяются таблицы при помощи ключевого слова JOIN. Еще раньше мы говорили, что если вы поймете, как работает команда SELECT, то вы поймете, как работает язык запросов SQL. Теперь мы скажем, что если вы поняли работу JOIN, то в дальнейшем ничего более сложного для вас не будет.

Данная часть является последней в этой теме, и сейчас нам осталось рассмотреть, как сравнить результаты двух SQL запросов SELECT. Сравнение результатов двух запросов выборки выполняется при помощи ключевых слов INTERSECT и EXCEPT. Комбинируя SELECT и EXCEPT, INTERSECT мы можем сравнивать результаты двух выборок.

Условном можно сказать, что фразы INTERSECT и EXCEPT являются псевдонимами предложений SELECT, которые объединяют два и более запросов с помощью UNION. Но SELECT в комбинации с INTERSECT дает таблицу с пересекающимися строками. А SELECT вместе с EXCEPT в результате даст таблицу, состоящую из строк первого запроса, которые отсутствуют во втором.

Операции сравнения результатов выборки чем-то похожи не только на запросы по объединению UNION, но и на запросы объединения таблиц, которые выполняются при помощи JOIN. Как минимум потому, что работу SELECT, INTERSECT и EXEPT можно описать диаграммами.

Чтобы хорошо понимать, как работают SELECT и INTERSECT, EXCEPT вам нужно помнить, что SQLite – СУБД с динамической типизацией данных, что у столбцов есть только аффинированный тип данных, который используется для сравнения значений, а понятие тип данных в SQLite заменено на понятие класс данных.

Давайте сразу посмотрим ни диаграмму, которая демонстрирует работу INTERSECT и EXCEPT. Она представлена ниже и для некоторых людей она окажется намного понятней примеров и словесных объяснений того, как работает SELECT, INTERSECT и EXCEPT.

Диаграмма, демонстрирующая работу EXCEPT и INTERSECT в SQL запросах

Диаграмма, демонстрирующая работу EXCEPT и INTERSECT в SQL запросах

Сразу для себя отметим, что запрос SELECT INTERSECT является симметричным. Синий кружок – это набор строк из левого запроса SELECT, а красный кружок – это результирующая таблица правого запроса SELECT. Всё, что находится в пересечение – это строки, которые войдут в общую результирующую таблицу SELECT INTERSECT.

Запрос SELECT EXCEPT тоже симметричный и похож на одну из разновидностей FULL JOIN (IS NULL), к сожалению, в SQLite3 нет возможности объединения FULL JOIN. Результатом запроса SELECT будут только уникальные строки. Другими словами: SQLite начнет сравнивать строки из результирующей таблицы первого запроса SELECT со строками результирующей таблицы второго запроса SELECT и в итоге составит одну таблицу, в которой будут только уникальные строки.

Симметричность EXCEPT и INTERSECT стоит понимать, как то, что не имеет значение какой из запросов SELECT будет находиться слева от ключевой фразы, а какой справа. Но давайте не будем откладывать примеры SELECT, INTERSECT и EXCEPT в долгий ящик и перейдем к рассмотрению.

Пересечение строк в запросах выборки данных: SELECT и INTERSECT в SQLite

Сперва мы рассмотрим пересечение строк в запросах выборки данных и посмотрим, что нам даст комбинация ключевых слов SELECT и INTERSECT. Давайте подготовим всё для примера. Создадим таблицу в базе данных при помощи команды CREATE TABLE:

Таблица для нас уже стандартная. Мы, как ответственные разработчики, обеспечили целостность данных при помощи ограничений уровня столбца и ограничения уровня таблицы. У таблицы есть внутренний индекс ROWID, но мы создали еще один при помощи ограничения первичного ключа PRIMARY KEY. Теперь давайте добавим данные в таблицу, для этого есть команда INSERT:

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

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

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

Обратим внимание на то, что SELECT, INTERSECT и EXCEPT работают, как и любые другие запросы на выборку данных. Мы можем делать выборку с условиями, используя WHERE, мы можем группировать данные выборки, используя GROUP BY, мы можем проводить дополнительную фильтрацию при помощи HAVING.

Мы даже может сортировать итоговую выборку при помощи ORDER BY и искусственно ограничить количество строк в выборке, воспользовавшись LIMIT. Так же никто не запрещает нам использовать SQL операторы, которые нужны для сравнения строк в запросах (LIKE) или для усложнения логики запроса (AND и OR).

С пересечение строк в таблицах и запросах базы данных SQLite мы разобрались. Предлагаем перейти к другому способу сравнения результатов выборки.

Разность строк в запросах выборки данных: SELECT и EXCEPT в SQLite3

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

А теперь мы получили всех мужчин, зарплата которых больше 30000 рублей. Давайте посмотрим, как SQLite работает с EXCEPT:

  1. Сначала была выполнена первая команда SELECT и получена первая промежуточная таблица.
  2. Затем была выполнена вторая команда SELECT.
  3. Затем СУБД SQLite3 начала сравнение результатов двух выборок.
  4. В процессе сравнения SQLite отбрасывала строки, присутствующие в обеих промежуточных таблицах, параллельно записывая в результирующую таблицу уникальные строки.

Понять, как происходит сравнение результатов выборки данных в SQL в целом и в SQLite в частности не так уж и сложно. Нужно просто понимать принцип работы EXCEPT и INTERSECT, а так же знать, как происходит сравнение значений в СУБД, которую вы используете.

Подведем итоги:

  1. Запрос SELECT с использованием EXCEPT делает сравнение результатов выборки данных так, что в итоговой таблице остаются лишь уникальные строки. Поэтому запросы EXCEPT называют разностью строк.
  2. Запрос SELECT с использованием INTERSECT делает сравнение результатов выборки данных так, что в результирующей таблице оказываются только повторяющиеся строки, поэтому запросы INTERSECT называют пересечением строк.

2 комментария к записи Часть 12.15: Сравнение результатов двух SQL запросов выборки: EXCEPT, INTERSECT и SELECT в SQLite

Руслан

Следить за публикациями на твоем блоге начал с появления рубрики SQLite. Хочу отметить, что вопросов обычно после ознакомления со статьей и примерами не остается, за что очень благодарен? Видео уроки по SQL и SQLite не планируешь выпускать, мне, как читателю было бы интересно.

Кирилл

Руслан, спасибо за отзыв!

Да, планирую. В принципе, видео уроки по SQLite и языку SQL начну размещать на YouTube сразу же, как только закончу с их записью. Сейчас отснято где-то четверть. Но сразу скажу, что они частично дублируют ту информацию, что доступна в виде текста на сайте.

Текст комментария: