Внешнее соединение таблиц (операция 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 – одни пятерки)