4.4.2 Соединение таблиц в запросах

Запросы по одной таблице составляют малую долю всех запросов к базе данных. Например, запросы с группировкой, которыми завершалась предыдущая лекция, при использовании только одной таблицы оценок marks выдают числовые коды студентов или предметов, тогда как при формировании отчетов требуются фамилии студентов и названия предметов. Но эти данные хранятся в других таблицах (students и subjects).

Подобная ситуация является типичной для любой нормализованной базы данных, состоящей из большого числа таблиц. В связи с этим, одна из самых распространенных операций в запросах на выборку – операция соединения таблиц. Соединять таблицы в запросах можно различными способами, при этом могут получаться различные результаты, поэтому данная операция требует повышенного внимания.

Способы соединения таблиц в запросе SELECT

1. Декартово произведение

Это операция, при выполнении которой каждая строка одной таблицы соединяется (склеивается) с каждой строкой другой таблицы. Декартово произведение таблиц выполняется в том случае, если во фразе FROM присутствует список из нескольких таблиц, но не задается ни операция соединения JOIN, ни условие для соединения таблиц во фразе WHERE. Обычно таблицы, которые соединяются посредством декартова произведения, не имеют общих столбцов.

Так, запрос вида:

select … from таблица1, таблица2

возвратит количество строк, равное произведению количества строк первой таблицы на количество строк второй таблицы, поскольку склеит каждую строку таблицы1 с каждой строкой таблицы2. Если не выполнять операцию отбора столбцов, в результат войдут все столбцы таблицы1 и таблицы2, т.е. количество столбцов равно сумме количеств столбцов таблицы1 и таблицы2. В общем, размеры таблицы-результата оказываются весьма внушительными.

На практике декартово произведение таблиц используется крайне редко. Например, для нашей демонстрационной базы данных выполнять соединение всех студентов со всеми предметами (т.е. выполнять декартово произведение таблиц students и subjects) разумно только в том случае, если все учатся по единому плану и сдают экзамены по всем предметам, занесенным в таблицу subjects. Соединять всех студентов или все предметы со всеми оценками – вообще полная бессмыслица.

Иногда в базе данных встречаются таблицы, которые содержат всего одну строку для хранения каких-либо констант (название вуза, минимальный размер оплаты труда и т.д.). Такие таблицы можно подключать к любому запросу, используя операцию декартова произведения.

На практике декартовы произведения иногда возникают из-за ошибки в записи текста запроса. Основным способом соединения таблиц является операция внутреннего или естественного соединения.

2. Внутреннее (естественное) соединение таблиц

Таким способом можно соединять только таблицы, имеющие общие столбцы. При выполнении данной операции соединяются (склеиваются) только строки, имеющие общие значения в столбце связи. Как правило, таким способом соединяются таблицы, связанные отношением «один-ко-многим», а в качестве столбцов связи используются первичный ключ главной таблицы и внешний ключ подчиненной. Таким образом, те строки главной таблицы, для которых нет связанных строк в подчиненной таблице, при внутреннем соединении вообще не попадут в результат запроса.

В языке SQL имеются 2 способа реализации внутреннего соединения таблиц, оба этих способа являются равноценными и обычно приводят к одному и тому же плану исполнения запроса. Однако с точки зрения реляционной алгебры они используют различные операции, и тексты запросов несколько отличаются друг от друга:

а) выборка из декартового произведения

Начнем с примеров. Пусть требуется вывести фамилии всех студентов и их оценки. Текст запроса будет выглядеть так:

select students.name_st, marks.mark

from students, marks

where students.cod_st=marks.cod_st

б) операция соединения [inner] join

Тот же самый запрос, соединяющий студентов с их оценками, будет записан несколько по-другому:

select students .name_st, marks.mark

from students join marks

on students.cod_st=marks.cod_st

Результаты запросов (а, б) будут абсолютно аналогичны и могут выглядеть примерно так:

name_st	mark
Иванов Иванов … Петров Петров Петров…	5 3 4 5 5

Каждая фамилия студента повторяется в результирующей таблице столько раз, сколько оценок получил данный студент. Если в таблице students есть, например, строка с фамилией Сидоров, который пока еще не получил ни одной оценки, в результирующей таблице этой фамилии вообще не будет. Так работает операция внутреннего соединения.

Обратим внимание на некоторые особенности приведенных выше примеров.

Во-первых, в тексте запросов используются составные имена столбцов, записанные с использованием точечной нотации:

имя_таблицы.имя_столбца

Использование составных имен позволяет избежать неоднозначности в записи имени столбца, поскольку разные таблицы могут содержать одноименные столбцы. В принципе, если имя какого-либо столбца уникально в пределах тех таблиц, которые указаны во фразе FROM, можно ограничиться и простым именем, но использование составных имен везде является более грамотным. Такие запросы и компилируются быстрее.

Во-вторых, в обоих приведенных выше запросах явно задано условие соединения в виде равенства столбцов связи (students.cod_st=marks.cod_st). Казалось бы, можно сократить текст запроса, ведь в таблицах students и marks только один общий столбец cod_st. Однако соединение двух таблиц вовсе не обязательно должно выполняться только по первичному и внешнему ключу. Любые два столбца, совпадающие по типу, могут быть использованы в условии соединения таблиц.

Разумеется, связывать строки, используя неключевые столбцы для связи, нужно с предельной осторожностью. Допустим, запрос, в котором устанавливается связь с помощью условия students.cod_st=marks.cod_sub, будет синтаксически правильным (он даже исполнится и возвратит результаты), но абсолютно бессмысленным. Будьте предельно внимательны при записи условий соединения!

Использование псевдонимов таблиц в запросах с соединением

Можно несколько сократить тексты приведенных выше запросов за счет использования коротких псевдонимов вместо довольно длинных имен таблиц.

Например, текст запроса, соединяющего студентов с оценками, может выглядеть так:

select st.name_st, m.mark

from students st, marks m

where st.cod_st=m.cod_st

или так:

select st .name_st, m.mark

from students st join marks m

on st.cod_st=m.cod_st

В дальнейших примерах мы будем использовать псевдонимы таблиц.

Приведенные запросы не возвращают важной информации, по каким именно предметам студент Иванов или Петров получил те или иные оценки. Для того, чтобы в результатах запроса появилось название предмета, необходимо в тексте запроса добавить соединение еще с одной таблицей subjects:

1. Вариант с выборкой из декартова произведения:

select st.cod_st, st.name_st, s.name_sub, m.mark

from students st, marks m, subjects s where st.cod_st=m.cod_st and s.cod_sub=m.cod_sub

b) Вариант с использованием операции соединения:

select st.cod_st, st.name_st, s.name_sub, m.mark

from students st join marks m on st.cod_st=m.cod_st

join subjects s on s.cod_sub=m.cod_sub

Результаты этих запросов опять одинаковы и предоставляют исчерпывающую информацию об успеваемости студентов в виде одной большой ненормализованной таблицы:

cod_st	name_st	name_sub	mark
1 1 … 2 2 2 … 3 3 …	Иванов Иванов … Петров Петров Петров … Иванов Иванов …	Математика Физика … Физика Информатика История … Математика Информатика …	5 4 4 5 4 … 4 3 …

Как можно понять из текста запросов, приведенных выше, для каждой добавляемой в запрос новой таблицы необходимо добавить условие ее соединения c другой таблицей, в противном случае будет выполнена операция декартова произведения. В общем случае, если в запросе используется n таблиц, нужно записать n-1 условий их соединения.

Следующий пример запроса выводит ФИО, код студента и средний балл (обратим внимание на то, что группировку придется выполнять сразу по двум столбцам, чтобы выполнилось правило для запросов с группировкой, которое было рассмотрено в предыдущей лекции).

select st.cod_st, st.name_st, avg(m.mark) avg_mark

From students st, marks m where st.cod_st=m.cod_st

Group by st.cod_st, st.name_st

Есть и еще один вариант обхода правила для запросов с группировкой:

select st.cod_st, MAX(st.name_st) name_st, avg(m.mark) avg_mark

From students st, marks m where st.cod_st=m.cod_st

Group by st.cod_st

В приведенном примере использование агрегатной функции MAX(st.name_st) выглядит искусственным, с таким же успехом можно использовать и функцию MIN, однако любая из этих функций позволит выполнить группировку только по одному столбцу cod_st.

3. Внешнее соединение таблиц (операция outer join)

При внешнем соединении, в отличие от внутреннего, в результат выборки попадают не только все связанные строки обеих таблиц, но и строки одной из таблиц (или обеих), для которых нет связанных в другой таблице. Недостающим значениям столбцов другой таблицы при этом присваивается значение NULL.

Возможны три варианта внешнего соединения двух таблиц (выборка дополняется строками таблицы, стоящей слева от слова JOIN, или таблицы, стоящей справа, или обеих сразу), поэтому различают три вида внешних соединений:

LEFT [OUTER] JOIN – левое внешнее соединение

RIGHT [OUTER] JOIN – правое внешнее соединение

FULL [OUTER] JOIN – полное внешнее соединение.

Например,

перепишем предыдущий запрос (код, фамилия и средний балл студента), используя внешнее соединение таким образом, чтобы студенты, у которых вообще нет оценок, попали бы в список вывода с NULL-значениями среднего балла.

select st.cod_st, st.name_st, avg (m.mark) avg_mark

from students st left join marks m on st.cod_st=m.cod_st group by st.cod_st, st.name_st

Мы уже знаем, что внутреннее соединение таблиц часто оформляется в тексте запроса, как выборка из декартова произведения. Можно ли использовать этот способ для внешнего соединения?

В некоторых СУБД можно, но, как и все нестандартные конструкции, это плохо влияет на переносимость запроса. Все же приведем вариант записи внешнего соединения таблиц, используя синтаксис СУБД Oracle (на примере того же самого запроса - код, фамилия и средний балл студента).

select st.cod_st, st.name_st, avg (m/mark) avg_mark

from students st, marks m

where st.cod_st=m.cod_st (+) group by st.cod_st, st.name_st

Используемая здесь синтаксическая конструкция (+) добавляет фиктивные строки в таблицу marks для тех студентов, у которых нет оценок, при этом в столбец mark помещается значение NULL.

Рассмотрим некоторые особенности использования функции COUNT в запросах с внешим соединением таблиц. Пусть требуется вывести количество оценок для каждого студента из таблицы students. Если студент еще не имеет ни одной оценки, должно быть выведено количество 0. Текст запроса, использующий операцию LEFT JOIN:

select st.cod_st, st.name_st, COUNT(m.mark) count_mark

from students st left join marks m on st.cod_st=m.cod_st group by st.cod_st, st.name_st

Использование конструкции COUNT(m.mark) позволит получить правильные результаты и вывести значение 0 для студентов, не имеющих оценок. Однако, если использовать в этом же запросе COUNT(*), то для студентов, не имеющих оценок, будет выведено количество 1 (!) и их нельзя будет отличить от студентов, которые имеют одну оценку.

Такие результаты вполне согласуются с правилами стандарта SQL, поскольку операция внешнего соединения добавляет в таблицу marks фиктивную строку, а функция COUNT(*) ее добросовестно подсчитывает. Функция COUNT(m.mark) учитывает, что в фиктивных строках, а также в реальных строках, где преподаватель не выставил оценку, значение столбца m.mark равно NULL.

Совсем интересные результаты получатся при использовании конструкции COUNT(DICTINCT m.mark). В этом случае будет подсчитано количество различных оценок каждого студента (например, для круглых отличников это значение равно 1 – одни пятерки)