Материал: Using_MySql,_MS_SQL_Server_and_Oracle

Пример 20: все разновидности запросов на объединение в трёх СУБД

Для демонстрации конкретных примеров создадим в БД «Исследование» таблицы rooms и computers, связанные связью «один ко многим»:

Сразу отметим, что слова INNER и OUTER в подавляющем большинстве слу-

чаев являются т.н. «синтаксическим сахаром» (т.е. добавлены для удобства человека, при этом никак не влияя на выполнения запроса) и потому являются необязательными: «просто JOIN» всегда внутренний, а LEFT JOIN, RIGHT JOIN и FULL

JOIN всегда внешние.

В этом примере очень много задач, и в них легко запутаться, потому сначала мы перечислим их все, а затем разместим условия, ожидаемые результаты и решения вместе.

Задачи на «классическое объединение»:

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 160/545

Пример 20: все разновидности запросов на объединение в трёх СУБД

•2.2.10.a{152}: показать информацию о том, как компьютеры распределены по комнатам;

•2.2.10.b{153}: показать все комнаты с поставленными в них компьютерами;

•2.2.10.c{154}: показать все пустые комнаты;

•2.2.10.d{155}: показать все компьютеры с информацией о том, в каких они

расположены комнатах;

•2.2.10.e{156}: показать все свободные компьютеры;

•2.2.10.f{158}: показать всю информацию о том, как компьютеры разме-

щены по комнатам (включая пустые комнаты и свободные компьютеры);

•2.2.10.g{160}: показать информацию по всем пустым комнатам и свободным компьютерам;

•2.2.10.h{162}: показать возможные варианты расстановки компьютеров по комнатам (не учитывать вместимость комнат);

•2.2.10.i{164}: показать возможные варианты перестановки компьютеров по комнатам (компьютер не должен оказаться в той комнате, в которой он сейчас стоит, не учитывать вместимость комнат).

Задачи на «неклассическое объединение»:

•2.2.10.j{166}: показать возможные варианты расстановки компьютеров по комнатам (учитывать вместимость комнат);

•2.2.10.k{168}: показать возможные варианты расстановки свободных компьютеров по пустым комнатам (не учитывать вместимость комнат);

•2.2.10.l{170}: показать возможные варианты расстановки свободных компьютеров по пустым комнатам (учитывать вместимость комнат);

•2.2.10.m{173}: показать возможные варианты расстановки свободных компьютеров по комнатам (учитывать остаточную вместимость комнат);

•2.2.10.n{175}: показать расстановку компьютеров по непустым комнатам так, чтобы в выборку не попало больше компьютеров, чем может поместиться в комнату;

•2.2.10.o{179}: показать расстановку компьютеров по всем комнатам так, чтобы в выборку не попало больше компьютеров, чем может поместиться в комнату.

Задачи на «классическое объединение» предполагают решение на основе прямого использования предоставляемого СУБД синтаксиса (без подзапросов и прочих ухищрений).

Задачи на «неклассическое объединение» предполагают решение на основе либо специфичного для той или иной СУБД синтаксиса, либо дополнительных действий (как правило — подзапросов).

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 161/545

Пример 20: все разновидности запросов на объединение в трёх СУБД

Задача 2.2.10.a: показать информацию о том, как компьютеры распределены по комнатам.

Ожидаемый результат 2.2.10.a.

Решение 2.2.10.a: используем внутреннее объединение.

Решение 2.2.10.a

1SELECT 'r_id' ,

2' r_name' ,

3' c_id' ,

4'c room',

5'c name'

6 FROM 'rooms'

7JOIN 'computers'

8ON 'r id' = 'c room

MS SQL і Решение 2.2.10.a

1SELECT [r id],

2[r name],

3[c id],

4[c room] ,

5[c name]

1SELECT "r id",

2"r name"

3"c id",

4"c room"

5"c name"

Логика внутреннего объединения состоит в том, чтобы подобрать из двух таблиц пары записей, у которых совпадает значение поля, по которому происходит объединение. Для наглядности ещё раз рассмотрим ожидаемый результат и поместим рядом поля, по которым происходит объединение:

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 162/545

Пример 20: все разновидности запросов на объединение в трёх СУБД

Задача 2.2.10.b: показать все комнаты с поставленными в них компьютерами.

Ожидаемый результат 2.2.10.b.

Решение 2.2.10.b: используем левое внешнее объединение. В этом решении нужно показать все записи из таблицы rooms — как те, для которых есть соответствие в таблице computers, так и те, для которых такого соответствия нет.

MySQL і Решение 2.2.10.b

MS SQL і Решение 2.2.10.b |

1SELECT [r_id],

2[r_name]

3[c_id],

4[c_room]

5[c_name]

6FROM [rooms]

1SELECT "r_id",

2"r_name"

3"c_id",

4"c_room"

5"c_name"

6FROM "rooms"

В случае левого внешнего объединения СУБД извлекает все записи из левой таблицы и пытается найти им пару из правой таблицы. Если пары не находится, соответствующая часть записи в итоговой таблице заполняется NULL-значениями.

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 163/545

Пример 20: все разновидности запросов на объединение в трёх СУБД

Модифицируем ожидаемый результат так, чтобы эта идея была более наглядной:

Ожидаемый результат 2.2.10.С.

Решение 2.2.10.С: используем левое внешнее объединение с исключением, т.е. выберем только те записи из таблицы rooms, для которых нет соответствия в таблице computers.

Задача 2.2.10.c: показать все пустые комнаты.

MySQL і Решение 2.2.10.С

1SELECT 'r id',

2'r name',

3'c id',

4'c room'

5'c name'

MS SQL I Решение 2.2.10.C |

1SELECT [r id],

2[r name]

3[c id],

4[c room]

5[c name]

Oracle I Решение 2.2.10.C |

1SELECT "r id",

2"r name"

3"c id",

4"c room"

5"c name"

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 164/545