Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 13: запросы на объединение и подзапросы с условием IN
|
|
Исследование 2.2.3.EXP.A |
|
|
||
|
MySQL I Исследование 2.2.3.EXP.A (продолжение) | |
|||||
1 |
|
-- Запрос 1: использование JOIN |
..) |
|||
1 |
|
-- Запрос 5: использование NOT IN (... DISTINCT . |
||||
2 |
|
SELECT |
DISTINCT [s id], |
|
|
|
2 |
|
SELECT |
's id', |
|
|
|
3 |
|
|
|
[s name] |
|
|
3 |
|
|
's name' |
|
|
|
4 |
|
FROM |
[subscribers] |
|
|
|
4 |
|
FROM |
'subscribers' |
|
|
|
5 |
|
WHERE |
JOIN [subscriptions] |
|
|
|
5 |
|
's id' NOT IN (SELECT DISTINCT 'sb subscriber' |
||||
6 |
|
|
ON [s_id] = [sb_subscriber] |
|||
6 |
|
|
|
FROM |
'subscriptions') |
|
|
|
|
|
|
|
|
1
1
2
2
3
3
4
4
5
5
6
6
-- Запрос 2: использование IN (... DISTINCT ...) |
|
|||
-- Запрос 6: использование NOT IN |
|
|||
SELECT |
|
|
|
|
[s id], |
|
|
|
|
SELECT |
's id', |
|
|
|
|
[s name] |
|
|
|
|
's name' |
|
|
|
FROM |
[subscribers] |
|
|
|
FROM |
'subscribers' |
|
|
|
WHERE |
[s id] IN (SELECT DISTINCT [sb subscriber] |
|||
WHERE |
's id' NOT IN (SELECT 'sb subscriber' |
|||
|
FROM |
[subscriptions]) |
||
|
FROM |
'subscriptions') |
||
1MS SQL-- Запрос |
3: использование IN |
|||
2 |
SELECT [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 Стр: 95/545
Пример 13: запросы на объединение и подзапросы с условием IN
Oracle Исследование 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 ...)
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 |
JOIN |
91.065 |
8.574 |
1.136 |
IN (... DISTINCT ...) |
0.068 |
6.711 |
0.298 |
IN |
0.039 |
6.723 |
0.309 |
LEFT JOIN |
45.788 |
8.695 |
0.284 |
NOT IN (... DISTINCT ...) |
45.564 |
7.437 |
0.329 |
NOT IN |
46.020 |
7.384 |
0.311 |
Перед проведением исследования мы ставили два вопроса, и теперь у нас есть ответы:
• Влияет ли на производительность наличие DISTINCT в подзапросе (для решений с IN)?
оВ случае с IN наличие DISTINCT ощутимо замедляет работу MySQL и немного ускоряет работу MS SQL Server и Oracle.
оВ случае с NOT IN наличие DISTINCT немного ускоряет работу
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 96/545
Пример 13: запросы на объединение и подзапросы с условием IN
MySQL и немного замедляет работу MS SQL Server и Oracle.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 97/545
Пример 13: запросы на объединение и подзапросы с условием IN
• Что работает быстрее — JOIN или IN (в обоих случаях: когда мы ищем как читателей, бравших книги, так и не бравших)?
o JOIN работает медленнее IN.
oLEFT JOIN работает немного медленнее NOT IN в MySQL и MS SQL Server и немного быстрее NOT IN в Oracle.
Поскольку большинство результатов крайне близки по значениям, однозначный вывод получается только один: IN работает быстрее JOIN, в остальных случаях стоит проводить дополнительные исследования.
Задание 2.2.3.TSK.A: показать список книг, которые когда-либо были взяты читателями.
Задание 2.2.3.TSK.B: показать список книг, которые никто из читателей никогда не брал.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 98/545
Пример 14: нетривиальные случаи использования условия IN и запросов на объединение
2.2.4.Пример 14: нетривиальные случаи использования условия IN и запросов на объединение
Существуют задачи, которые на первый взгляд решаются очень просто. Однако оказывается, что простое и очевидное решение является неверным.
ОЗадача 2.2.4.a{92}: показать список читателей, у которых сейчас на руках нет книг (использовать JOIN).
ОЗадача 2.2.4. b{95}: показать список читателей, у которых сейчас на руках нет книг (не использовать JOIN).
Взадачах 2.2.3.* примера 13{82} всё было просто: если в таблице subscriptions есть информация о читателе, значит, он брал книги в библиотеке, а если нет
— не брал. Теперь же нас будут интересовать как читатели, никогда не бравшие книги (объективно у них на руках нет книг), так и читатели, бравшие книги (но ктото вернул всё, что брал, а кто-то ещё что-то читает).
Ожидаемый результат 2.2.4.а.
s id |
s name |
1Иванов И.И.
2Петров П.П.
Ожидаемый результат 2.2.4.b.
s id |
s name |
1Иванов И.И.
2Петров П.П.
■ЛУ Решение 2.2.4.а{92}.
MySQL і Решение 2.2.4.а
1SELECT 's_id',
2' s_name'
3 |
FROM |
'subscribers' |
4 |
|
LEFT OUTER JOIN 'subscriptions' |
5 |
|
ON 's_id' = 'sb_subscriber' |
6 |
GROUP |
BY 's_id' |
|
HAVING COUNT(IF('sb is active' = 'Y', 'sb is active', NULL)) = 0 |
|
MS SQL І Решение 2.2.4.а |
||
1 |
SELECT [s id], |
|
2 |
|
[s name] |
3 |
FROM |
[subscribers] |
4 |
|
LEFT OUTER JOIN [subscriptions] |
5 |
|
ON [s id] = [sb subscriber] |
6 |
GROUP |
BY [s id] , |
7 |
|
[s name] |
8 |
HAVING COUNT(CASE |
|
9 |
|
WHEN [sb is active] = 'Y' THEN [sb is active] |
10 |
|
ELSE NULL |
11 |
|
END) = 0 |
|
|
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 99/545