Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 20: все разновидности запросов на объединение в трёх СУБД
Благодаря условию в 9-й строке каждого запроса из набора данных, эквивалентного получаемому в предыдущей задаче (2.2.10.b) в конечную выборку проходят только строки со значением NULL в поле c_room:
r_name |
r_id |
c_room |
c_id |
c_name |
Комната с двумя компьютерами |
1 |
1 |
1 |
Компьютер A в комнате 1 |
Комната с двумя компьютерами |
1 |
1 |
2 |
Компьютер B в комнате 1 |
Комната с тремя компьютерами |
2 |
2 |
3 |
Компьютер A в комнате 2 |
Комната с тремя компьютерами |
2 |
2 |
4 |
Компьютер B в комнате 2 |
Комната с тремя компьютерами |
2 |
2 |
5 |
Компьютер C в комнате 2 |
Пустая комната 1 |
3 |
NULL |
NULL |
NULL |
Пустая комната 2 |
4 |
NULL |
NULL |
NULL |
Пустая комната 3 |
5 |
NULL |
NULL |
NULL |
Задача 2.2.10.d: показать все компьютеры с информацией о том, в каких они расположены комнатах.
Ожидаемый результат 2.2.10.d.
r_id |
r_name |
c_id |
c_room |
c_name |
1 |
Комната с двумя компьютерами |
1 |
1 |
Компьютер A в комнате 1 |
1 |
Комната с двумя компьютерами |
2 |
1 |
Компьютер B в комнате 1 |
2 |
Комната с тремя компьютерами |
3 |
2 |
Компьютер A в комнате 2 |
2 |
Комната с тремя компьютерами |
4 |
2 |
Компьютер B в комнате 2 |
2 |
Комната с тремя компьютерами |
5 |
2 |
Компьютер C в комнате 2 |
NULL |
NULL |
6 |
NULL |
Свободный компьютер A |
NULL |
NULL |
7 |
NULL |
Свободный компьютер B |
NULL |
NULL |
8 |
NULL |
Свободный компьютер C |
Решение 2.2.10.d: используем правое внешнее объединение. Эта задача обратна задаче 2.2.10.b{153}: здесь нужно показать все записи из таблицы computers вне зависимости от того, есть ли им соответствие из таблицы
rooms.
MySQL Решение 2.2.10.d
1SELECT `r_id`,
2`r_name`,
3`c_id`,
4`c_room`,
5`c_name`
6 |
|
FROM `rooms` |
|
7 |
|
RIGHT JOIN |
`computers` |
8 |
|
ON |
`r_id` = `c_room` |
MS SQL Решение 2.2.10.d
1SELECT [r_id],
2[r_name],
3[c_id],
4[c_room],
5[c_name]
6 |
|
FROM [rooms] |
|
7 |
|
RIGHT JOIN |
[computers] |
8 |
|
ON |
[r_id] = [c_room] |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 155/545
Пример 20: все разновидности запросов на объединение в трёх СУБД
Oracle Решение 2.2.10.d
1SELECT "r_id",
2"r_name",
3"c_id",
4"c_room",
5"c_name"
6 |
|
FROM "rooms" |
|
7 |
|
RIGHT JOIN |
"computers" |
8 |
|
ON |
"r_id" = "c_room" |
|
|
|
|
В случае правого внешнего объединения СУБД извлекает все записи из правой таблицы и пытается найти им пару из левой таблицы. Если пары не находится, соответствующая часть записи в итоговой таблице заполняется NULL-значениями.
Модифицируем ожидаемый результат так, чтобы эта идея была более наглядной:
r_name |
r_id |
c_room |
c_id |
c_name |
Комната с двумя компьютерами |
1 |
1 |
1 |
Компьютер A в комнате 1 |
Комната с двумя компьютерами |
1 |
1 |
2 |
Компьютер B в комнате 1 |
Комната с тремя компьютерами |
2 |
2 |
3 |
Компьютер A в комнате 2 |
Комната с тремя компьютерами |
2 |
2 |
4 |
Компьютер B в комнате 2 |
Комната с тремя компьютерами |
2 |
2 |
5 |
Компьютер C в комнате 2 |
NULL |
NULL |
NULL |
6 |
Свободный компьютер A |
NULL |
NULL |
NULL |
7 |
Свободный компьютер B |
NULL |
NULL |
NULL |
8 |
Свободный компьютер C |
Задача 2.2.10.e: показать все свободные компьютеры.
Ожидаемый результат 2.2.10.e.
r_id |
r_name |
c_id |
c_room |
c_name |
NULL |
NULL |
6 |
NULL |
Свободный компьютер A |
NULL |
NULL |
7 |
NULL |
Свободный компьютер B |
NULL |
NULL |
8 |
NULL |
Свободный компьютер C |
Решение 2.2.10.e: используем правое внешнее объединение с исключением. Эта задача обратна задаче 2.2.10.c: здесь мы выберем только те записи из таблицы computers, для которых нет соответствия в таблице
rooms.
MySQL Решение 2.2.10.e
1SELECT `r_id`,
2`r_name`,
3`c_id`,
4`c_room`,
5`c_name`
6 |
|
FROM |
`rooms` |
|
7 |
|
|
RIGHT JOIN |
`computers` |
8 |
|
|
ON |
`r_id` = `c_room` |
9 |
|
WHERE |
`r_id` IS NULL |
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 156/545
Пример 20: все разновидности запросов на объединение в трёх СУБД
MS SQL Решение 2.2.10.e
1SELECT [r_id],
2[r_name],
3[c_id],
4[c_room],
5[c_name]
6 |
|
FROM |
[rooms] |
|
7 |
|
|
RIGHT JOIN |
[computers] |
8 |
|
|
ON |
[r_id] = [c_room] |
9 |
|
WHERE |
[r_id] IS NULL |
|
Oracle Решение 2.2.10.e
1SELECT "r_id",
2"r_name",
3"c_id",
4"c_room",
5"c_name"
6 |
|
FROM |
"rooms" |
|
7 |
|
|
RIGHT JOIN |
"computers" |
|
|
|
|
|
8 |
|
|
ON |
"r_id" = "c_room" |
9 |
|
WHERE |
"r_id" IS NULL |
|
Аналогичный же результат (как правило, в таких задачах нас не интересуют поля из родительской таблицы, т.к. там по определению будет NULL) можно получить и без JOIN. Такой способ срабатывает, когда источником информации является дочерняя таблица, но задачу 2.2.10.c{154} таким тривиальным способом решить не получится (там понадобилось выполнять подзапрос с конструкцией NOT IN):
c_id |
c_room |
c_name |
6 |
NULL |
Свободный компьютер A |
7 |
NULL |
Свободный компьютер B |
8 |
NULL |
Свободный компьютер C |
MySQL Решение 2.2.10.e (упрощённый вариант)
1SELECT `c_id`,
2`c_room`,
3`c_name`
4 |
|
FROM |
`computers` |
|
5 |
|
WHERE |
`c_room` IS |
NULL |
MS SQL Решение 2.2.10.e (упрощённый вариант)
1SELECT [c_id],
2[c_room],
3[c_name]
4 |
|
FROM |
[computers] |
|
5 |
|
WHERE |
[c_room] IS |
NULL |
Oracle Решение 2.2.10.e (упрощённый вариант)
1SELECT "c_id",
2"c_room",
3"c_name"
4 |
|
FROM |
"computers" |
|
5 |
|
WHERE |
"c_room" IS |
NULL |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 157/545
Пример 20: все разновидности запросов на объединение в трёх СУБД
Задача 2.2.10.f: показать всю информацию о том, как компьютеры размещены по комнатам (включая пустые комнаты и свободные компьютеры).
Ожидаемый результат 2.2.10.f.
r_id |
r_name |
c_id |
c_room |
c_name |
1 |
Комната с двумя компьютерами |
1 |
1 |
Компьютер A в комнате 1 |
1 |
Комната с двумя компьютерами |
2 |
1 |
Компьютер B в комнате 1 |
2 |
Комната с тремя компьютерами |
3 |
2 |
Компьютер A в комнате 2 |
2 |
Комната с тремя компьютерами |
4 |
2 |
Компьютер B в комнате 2 |
2 |
Комната с тремя компьютерами |
5 |
2 |
Компьютер C в комнате 2 |
3 |
Пустая комната 1 |
NULL |
NULL |
NULL |
4 |
Пустая комната 2 |
NULL |
NULL |
NULL |
5 |
Пустая комната 3 |
NULL |
NULL |
NULL |
NULL |
NULL |
6 |
NULL |
Свободный компьютер A |
NULL |
NULL |
7 |
NULL |
Свободный компьютер B |
NULL |
NULL |
8 |
NULL |
Свободный компьютер C |
Решение 2.2.10.f: используем полное внешнее объединение. Эта задача является комбинацией задач 2.2.10.b{153} и 2.2.10.d{155}: нужно показать все записи из таблицы rooms вне зависимости от наличия соответствия в таблице computers, а также все записи из таблицы computers вне зависимости от наличия соответствия в таблице rooms.
Важно! MySQL не поддерживает полное внешнее объединение, потому использование там FULL JOIN даёт неверный результат.
MySQL Решение 2.2.10.f (ошибочный запрос)
1SELECT `r_id`,
2`r_name`,
3`c_id`,
4`c_room`,
5`c_name`
6 |
|
FROM `rooms` |
|
7 |
|
FULL JOIN |
`computers` |
8 |
|
ON |
`r_id` = `c_room` |
В результате выполнения такого запроса получается тот же набор данных, что и в задаче 2.2.10.a{152}:
r_id |
r_name |
c_id |
c_room |
c_name |
1 |
Комната с двумя компьютерами |
1 |
1 |
Компьютер A в комнате 1 |
1 |
Комната с двумя компьютерами |
2 |
1 |
Компьютер B в комнате 1 |
2 |
Комната с тремя компьютерами |
3 |
2 |
Компьютер A в комнате 2 |
2 |
Комната с тремя компьютерами |
4 |
2 |
Компьютер B в комнате 2 |
2 |
Комната с тремя компьютерами |
5 |
2 |
Компьютер C в комнате 2 |
Самым простым4 решением этой задачи для MySQL является объединение решений задач 2.2.10.b{153} и 2.2.10.d{155} с помощью конструкции UNION.
4Несколько альтернативных решений рассмотрено в этой статье: http://www.xaprb.com/blog/2006/05/26/how-to-write-full- outer-join-in-mysql/
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 158/545
Пример 20: все разновидности запросов на объединение в трёх СУБД
MySQL Решение 2.2.10.f
1SELECT `r_id`,
2`r_name`,
3`c_id`,
4`c_room`,
5`c_name`
6 |
|
FROM `rooms` |
|
7 |
|
LEFT JOIN |
`computers` |
8 |
|
ON |
`r_id` = `c_room` |
|
|
|
|
9UNION
10SELECT `r_id`,
11`r_name`,
12`c_id`,
13`c_room`,
14`c_name`
15 |
|
FROM |
`rooms` |
|
16 |
|
|
RIGHT JOIN |
`computers` |
17 |
|
|
ON |
`r_id` = `c_room` |
|
|
|
|
|
MS SQL Server и Oracle поддерживают полное внешнее объединение, и там эта задача решается намного проще:
MS SQL Решение 2.2.10.f
1SELECT [r_id],
2[r_name],
3[c_id],
4[c_room],
5[c_name]
6 |
|
FROM [rooms] |
|
7 |
|
FULL JOIN |
[computers] |
8 |
|
ON |
[r_id] = [c_room] |
Oracle Решение 2.2.10.f
1SELECT "r_id",
2"r_name",
3"c_id",
4"c_room",
5"c_name"
6 |
|
FROM "rooms" |
|
7 |
|
FULL JOIN |
"computers" |
8 |
|
ON |
"r_id" = "c_room" |
|
|
|
|
При выполнении полного внешнего объединения СУБД извлекает все записи из обеих таблиц и ищет их пары. Там, где пары находятся, в итоговой выборке получается строка с данными из обеих таблиц. Там, где пары нет, недостающие данные заполняются NULL-значениями. Покажем это графически:
r_name |
r_id |
c_room |
c_id |
c_name |
Комната с двумя компьютерами |
1 |
1 |
1 |
Компьютер A в комнате 1 |
Комната с двумя компьютерами |
1 |
1 |
2 |
Компьютер B в комнате 1 |
Комната с тремя компьютерами |
2 |
2 |
3 |
Компьютер A в комнате 2 |
Комната с тремя компьютерами |
2 |
2 |
4 |
Компьютер B в комнате 2 |
Комната с тремя компьютерами |
2 |
2 |
5 |
Компьютер C в комнате 2 |
Пустая комната 1 |
3 |
NULL |
NULL |
NULL |
Пустая комната 2 |
4 |
NULL |
NULL |
NULL |
Пустая комната 3 |
5 |
NULL |
NULL |
NULL |
NULL |
NULL |
NULL |
6 |
Свободный компьютер A |
NULL |
NULL |
NULL |
7 |
Свободный компьютер B |
NULL |
NULL |
NULL |
8 |
Свободный компьютер C |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 159/545