Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 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 Стр: 85/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), плана выполнения запроса и иных факторов.
Решение 2.2.3.c{82}.
MySQL Решение 2.2.3.c
1SELECT `s_id`,
2`s_name`
3 |
|
FROM |
`subscribers` |
|
|
4 |
|
|
LEFT JOIN |
`subscriptions` |
|
5 |
|
|
ON |
`s_id` = |
`sb_subscriber` |
6 |
|
WHERE |
`sb_subscriber` IS |
NULL |
|
MS SQL Решение 2.2.3.c
1SELECT [s_id],
2[s_name]
3 |
|
FROM |
[subscribers] |
|
|
4 |
|
|
LEFT JOIN |
[subscriptions] |
|
5 |
|
|
ON |
[s_id] = |
[sb_subscriber] |
6 |
|
WHERE |
[sb_subscriber] IS |
NULL |
|
|
|
|
|
|
|
Oracle Решение 2.2.3.c
1SELECT "s_id",
2"s_name"
3 |
|
FROM |
"subscribers" |
|
|
4 |
|
|
LEFT JOIN |
"subscriptions" |
|
5 |
|
|
ON |
"s_id" = |
"sb_subscriber" |
6 |
|
WHERE |
"sb_subscriber" IS |
NULL |
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 86/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 Решение 2.2.3.d
1SELECT `s_id`,
2`s_name`
3 |
|
FROM |
`subscribers` |
|
|
4 |
|
WHERE |
`s_id` NOT IN |
(SELECT |
DISTINCT `sb_subscriber` |
5 |
|
|
|
FROM |
`subscriptions`) |
|
|
|
|
|
|
MS SQL Решение 2.2.3.d
1SELECT [s_id],
2[s_name]
3 |
|
FROM |
[subscribers] |
|
|
4 |
|
WHERE |
[s_id] NOT IN |
(SELECT |
DISTINCT [sb_subscriber] |
5 |
|
|
|
FROM |
[subscriptions]) |
|
|
|
|
|
|
Oracle Решение 2.2.3.d
1SELECT "s_id",
2"s_name"
3 |
|
FROM |
"subscribers" |
|
|
4 |
|
WHERE |
"s_id" NOT IN |
(SELECT |
DISTINCT "sb_subscriber" |
5 |
|
|
|
FROM |
"subscriptions") |
|
|
|
|
|
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 87/545
Пример 13: запросы на объединение и подзапросы с условием IN
Здесь поведение СУБД аналогично решению 2.2.3.b за исключением того, что при переборе значений sb_subscriber нужно не обнаружить среди них зна-
чение 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 |
|
|
|
2 |
|
Петров П.П. |
|
|
|
|
Исследование 2.2.3.EXP.A: оценка влияния DISTINCT в подзапросе на производительность операции.
Настало время поговорить о производительности. Нас будет интересовать два вопроса:
•Влияет ли на производительность наличие DISTINCT в подзапросе (для решений с IN)?
•Что работает быстрее — JOIN или IN (в обоих случаях: когда мы ищем как читателей, бравших книги, так и не бравших)?
Проводить исследование будем на базе данных «Большая библиотека». Выполним по сто раз следующие запросы:
MySQL Исследование 2.2.3.EXP.A
1-- Запрос 1: использование JOIN
2SELECT DISTINCT `s_id`,
3 |
|
`s_name` |
4 |
|
FROM `subscribers` |
5JOIN `subscriptions`
6ON `s_id` = `sb_subscriber`
1-- Запрос 2: использование IN (... DISTINCT ...)
2SELECT `s_id`,
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 Стр: 88/545
Пример 13: запросы на объединение и подзапросы с условием IN
MySQL Исследование 2.2.3.EXP.A (продолжение)
1-- Запрос 5: использование NOT IN (... DISTINCT ...)
2SELECT `s_id`,
3`s_name`
4 |
|
FROM |
`subscribers` |
|
|
5 |
|
WHERE |
`s_id` NOT IN |
(SELECT |
DISTINCT `sb_subscriber` |
6 |
|
|
|
FROM |
`subscriptions`) |
|
|
|
|
|
|
1-- Запрос 6: использование NOT IN
2SELECT `s_id`,
3`s_name`
4 |
|
FROM |
`subscribers` |
|
|
5 |
|
WHERE |
`s_id` NOT IN |
(SELECT |
`sb_subscriber` |
6 |
|
|
|
FROM |
`subscriptions`) |
MS SQL Исследование 2.2.3.EXP.A
1-- Запрос 1: использование JOIN
2SELECT DISTINCT [s_id],
3 |
|
[s_name] |
|
|
|
4 |
|
FROM [subscribers] |
5JOIN [subscriptions]
6ON [s_id] = [sb_subscriber]
1-- Запрос 2: использование IN (... DISTINCT ...)
2SELECT [s_id],
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 |
|
|
|
|
|
|
|
1-- Запрос 5: использование NOT IN (... DISTINCT ...)
2SELECT [s_id],
3[s_name]
4 |
|
FROM |
[subscribers] |
|
|
5 |
|
WHERE |
[s_id] NOT IN |
(SELECT |
DISTINCT [sb_subscriber] |
6 |
|
|
|
FROM |
[subscriptions]) |
1-- Запрос 6: использование NOT IN
2SELECT [s_id],
3[s_name]
4 |
|
FROM |
[subscribers] |
|
|
5 |
|
WHERE |
[s_id] NOT IN |
(SELECT |
[sb_subscriber] |
6 |
|
|
|
FROM |
[subscriptions]) |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 89/545