Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 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_roo |
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
1 SELECT
2
3
4'r_id' , ' r_name' , ' c_id'
5, ' c_room' , ' c_name' '
6 |
FROM |
rooms' RIGHT JOIN |
7'computers' ON 'r id' = 'c
8room'
MS SQL I Решение 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 Стр: 165/545
Пример 20: все разновидности запросов на объединение в трёх СУБД
|
Oracl |
і |
Решение 2.2.10.d |
I |
|
e |
|
||||
|
|
|
|
|
|
1 |
|
SELECT "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.Є.
|
r_id |
|
r_name |
c_id c_room |
c_name |
|
NULL |
|
NULL |
6 |
NULL |
Свободный компьютер A |
|
NULL |
|
NULL |
7 |
NULL |
Свободный компьютер B |
|
|
MySQL і |
Решение 2.2.10.Є |
NULL |
Свободный компьютер C |
||
|
NULL |
|
NULL |
8 |
||
1 |
SELECT 'r_id' , |
|
|
|||
2
' r_name'
3' c_id' ,
4'c room',
5 |
|
'c name' Решение 2.2.10.e: используем правое внешнее объединение |
6 |
FROM |
' rooms' |
7 |
|
Задача 2.2.10.e: показать все свободные компьютеры. |
|
|
|
8 |
|
с исключением. Эта задача обратна задаче 2.2.10. с: здесь |
9 |
WHERE |
мы выберем только те записи из таблицы computers, для |
|
которых нет соответствия в таблице rooms. |
|
RIGHT JOIN 'computers' ON 'r_id' = 'c_room' 'r id' IS NULL
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 166/545
Пример 20: все разновидности запросов на объединение в трёх СУБД
MS SQL І Решение 2.2.10.e
1SELECT [r_id],
2[r_name]
3[c_id],
4[c_room]
5[c_name]
6FROM [rooms]
|
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"
6FROM "rooms"
|
RIGHT JOIN "computers" |
|
8 |
ON |
"r_id" = "c_room" |
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 I Решение 2.2.10.e (упрощённый вариант) |
1SELECT 'c_id',
2'c_room',
3'c_name'
4 |
FROM |
'computers' |
5 |
WHERE |
'c room' IS NULL |
MS SQL I Решение 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 Стр: 167/545
Пример 20: все разновидности запросов на объединение в трёх СУБД
Задача 2.2.10.f: показать всю информацию о том, как компьютеры размещены по комнатам (включая пустые комнаты и свободные компьютеры).
Ожидаемый результат 2.2.10.f.
|
r_id |
r_name |
c_id |
c_roo |
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 Стр: 168/545
Пример 20: все разновидности запросов на объединение в трёх СУБД
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 I Решение 2.2.10.f |
1SELECT [r_id],
2[r_name]
3[c_id],
4[c_room]
5[c_name]
6FROM [rooms]
|
FULL JOIN |
[computers] |
8 |
ON |
[r id] = [c room] |
|
|
|
Oracle I |
Решение 2.2.10.f |
| |
1SELECT "r_id",
2"r_name"
3"c_id",
4"c_room"
5"c_name"
6FROM "rooms"
|
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 Стр: 169/545