Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 13: запросы на объединение и подзапросы с условием IN
В правильном ожидаемом результате было три записи (в т.ч. двое Сидоровых с разными идентификаторами). Сейчас же эта информация утеряна.
'IT Решение 2.2.3.b{82}.
Снова решение для всех трёх СУБД совершенно одинаково. Слово DISTINCT
в подзапросе (в 4-й строке всех трёх запросов) используется для того, чтобы СУБД приходилось анализировать меньший набор данных. Будет ли разница в производительности существенной, мы рассмотрим сразу после решения следующих
двух задач.А пока покажем графически, как работают эти два запроса. Начнём с варианта с JOIN. СУБД перебирает значения s_id из таблицы subscribers и проверяет, есть ли в таблице subscriptions записи, поле sb_subscriber
которых содержит то же самое значение: |
|
|
|
|||
|
Таблиц |
а subscribers |
Таблица subscriptions |
|||
|
s_id |
s_name |
|
|
sb_subscriber |
|
|
1 |
Иванов И.И. |
|
|
1 |
|
|
2 |
Петров П.П. |
|
|
1 |
|
|
3 |
Сидоров С.С. |
|
|
3 |
|
|
4 |
Сидоров С.С. |
|
|
1 |
|
|
|
|
|
|
4 |
|
|
|
|
|
|
1 |
|
|
|
|
|
|
3 |
|
|
|
|
|
|
3 |
|
|
|
|
|
|
4 |
|
|
|
|
|
|
1 |
|
|
|
|
|
|
|
|
|
|
|
|
|
4 |
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 90/545
Пример 13: запросы на объединение и подзапросы с условием IN
В результате поиска совпадений получается следующая картина:
s_id |
sb_subscriber |
|
s_name |
|
1 |
1 |
Иванов И.И. |
|
|
1 |
1 |
Иванов И.И. |
|
|
3 |
3 |
Сидоров С.С. |
|
|
1 |
1 |
Иванов И.И. |
|
|
4 |
4 |
Сидоров С.С. |
|
|
1 |
1 |
Иванов И.И. |
|
|
3 |
3 |
Сидоров С.С. |
|
|
3 |
3 |
Сидоров С.С. |
|
|
4 |
4 |
Сидоров С.С. |
|
|
1 |
1 |
Иванов И.И. |
|
|
4 |
4 |
Сидоров С.С. |
|
|
|
Поле sb_subscriber мы не указываем в SELECT, т.е. остаётся всего два |
|||
столбца: |
|
|
|
|
|
|
|
|
|
s_id |
s_name |
|
|
|
1 |
Иванов И.И. |
|
|
|
1 |
Иванов И.И. |
|
|
|
3Сидоров С.С.
1Иванов И.И.
4Сидоров С.С.
1Иванов И.И.
3Сидоров С.С.
3Сидоров С.С.
4Сидоров С.С.
1Иванов И.И.
4Сидоров С.С.
И, наконец, благодаря применению DISTINCT, СУБД устраняет дубликаты:
s_id s_name
1 Иванов И.И.
3Сидоров С.С.
4Сидоров С.С.
Вслучае с подзапросом и ключевым словом IN ситуация выглядит иначе. Сначала СУБД выбирает все значения sb_subscriber:
sb_subscriber
1
1
3
1
4
1
3
3
4
1
4
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 91/545
Пример 13: запросы на объединение и подзапросы с условием IN
Потом происходит устранение дубликатов:
sb_subscriber
1 ________
3 ___________
4
Теперь СУБД анализирует каждую строку таблицы subscribers на предмет того, входит ли её значение s_id в этот набор:
|
s_id |
s_name |
Набор значений |
Помещать ли запись в |
||
|
|
|
|
|
sb_subscriber |
выборку |
|
1 |
|
Иванов И.И. |
1, 3, 4 |
Да |
|
|
2 |
|
Петров П.П. |
1,3,4 |
Нет |
|
|
3 |
|
Сидоров С.С. |
1,3, 4 |
Да |
|
|
4 |
|
Сидоров С.С. |
1, 3, 4 |
Да |
|
|
|
Итого получается: |
|
|
||
s_id |
|
s_name |
|
|
|
|
1 |
Иванов И.И. |
|
|
|
||
3 |
Сидоров С.С. |
|
|
|
||
4 |
Сидоров С.С. |
|
|
|
||
Примечание: здесь, расписывая пошагово логику работы СУБД мы для простоты считаем, что устранение дубликатов происходит в самом конце. На самом деле, это не так. Внутренние алгоритмы обработки данных позволяют СУБД выполнять операции дедубликации как после завершения формирования выборки, так и прямо в процессе её формирования. Решение о применении того или иного варианта будет зависеть от конкретной СУБД, используемых методов доступа (storage engine), плана выполнения запроса и иных факторов.
xaAs/
Ч р Решение 2.2.3.С82.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 92/545
Пример 13: запросы на объединение и подзапросы с условием IN
При поиске читателей, никогда не бравших книги, логика работы СУБД выглядит так. Сначала выполняется т.н. «левое (внешнее) объединение», т.е. СУБД извлекает все записи из таблицы subscribers и пытается найти им «пару» из таблицы subscriptions. Если «пару» найти не получилось (её нет), вместо
значения поля sb_subscriber будет подставлено значение NULL:
s_id |
s_name sb_subscriber |
|
1 |
Иванов И.И. |
1 |
1 |
Иванов И.И. |
1 |
1 |
Иванов И.И. |
1 |
1 |
Иванов И.И. |
1 |
1 |
Иванов И.И. |
1 |
|
2 |
Петров П.П. |
NULL |
|
|
3 |
Сидоров С.С. |
3 |
|
||
3 |
Сидоров С.С. |
3 |
|
||
3 |
Сидоров С.С. |
3 |
|
||
4 |
Сидоров С.С. |
4 |
|
||
4 |
Сидоров С.С. |
4 |
|
||
4 |
Сидоров С.С. |
4 |
|
||
|
|
Благодаря условию WHERE "sb_subscriber" IS NULL, только информа |
|||
ция о Петрове попадёт в конечную выборку: |
|||||
|
|
|
|
|
|
|
s_id |
s_name |
|
|
|
|
2 |
Петров П.П. |
|
|
|
Решение 2.2.3.d{82}.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 93/545
Пример 13: запросы на объединение и подзапросы с условием IN
Здесь поведение СУБД аналогично решению 2.2.3.b за исключением того, что при переборе значений sb_subscriber нужно не обнаружить среди них значение s id:
|
s_id |
|
s_name |
Набор значений |
Помещать ли запись в |
|||
|
|
|
|
|
|
|
sb_subscriber |
выборку |
|
1 |
|
|
Иванов И.И. |
1, 3, 4 |
Нет |
||
|
2 |
|
|
Петров П.П. |
^3Н |
Да |
||
|
3 |
|
|
Сидоров С.С. |
1, 3, 4 |
Нет |
||
|
4 |
|
|
Сидоров С.С. |
1, 3, 4 |
Нет |
||
|
|
Получается: |
|
|
||||
|
|
|
|
|
|
|
||
s_id |
|
s_name |
|
|
|
|||
2 |
Петров П.П. |
|
|
|
|
|||
Исследование 2.2.3. EXP.A: оценка влияния DISTINCT в подзапросе на производительность операции.
Настало время поговорить о производительности. Нас будет интересовать два вопроса:
•Влияет ли на производительность наличие DISTINCT в подзапросе (для решений с IN)?
•Что работает быстрее — JOIN или IN (в обоих случаях: когда мы ищем как читателей, бравших книги, так и не бравших)?
Проводить исследование будем на базе данных «Большая библиотека». Выполним по сто раз следующие запросы:
MySQL I Исследование 2.2.3.EXP.A |
1-- Запрос 1: использование JOIN
2SELECT DISTINCT 's id',
3 |
|
's name' |
|
|
4 |
FROM |
'subscribers' |
|
|
5 |
|
JOIN 'subscriptions' |
|
|
6 |
|
ON 's_id' = 'sb_subscriber' |
|
|
1 |
-- Запрос 2: использование IN (... DISTINCT |
..) |
||
. SELECT 's id', |
|
|||
2 |
|
|
||
3 |
|
's name' |
|
|
4 |
FROM |
'subscribers' |
|
|
5 |
WHERE |
's id' IN (SELECT DISTINCT 'sb subscriber' |
||
6 |
|
FROM |
'subscriptions') |
|
1-- Запрос 3: использование IN
2SELECT 's id',
3's name'
4 |
FROM |
'subscribers' |
|
5 |
WHERE |
's id' IN (SELECT 'sb subscriber' |
|
6 |
|
FROM |
'subscriptions') |
1-- Запрос 4: использование LEFT JOIN
2SELECT 's id',
3's name'
4 |
FROM |
'subscribers' |
|
5 |
|
LEFT JOIN |
'subscriptions' |
6 |
|
ON |
's id' = 'sb subscriber' |
7 |
WHERE |
'sb subscriber' IS NULL |
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 94/545