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

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

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

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:

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

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

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

Как можно понять из текста запросов, приведенных выше, для каждой добавляемой в запрос новой таблицы необходимо добавить условие ее соединения 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 – одни пятерки)