Главная | Обратная связь | Поможем написать вашу работу!

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

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

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

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

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

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

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

Самосоединения

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

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

SELECT DISTINCT s1.name_st FROM students s1, students s2

WHERE s1.name_st=s2.name_st AND s1.cod_st<>s2.cod_st

Ключевое слово DISTINCT здесь добавлено на тот случай, если в таблице встречаются фамилии, повторяющиеся более двух раз (такие фамилии несколько раз попадут в результат запроса).

Следует отметить, что ту же задачу можно решить более эффективно, используя запрос:

SELECT name_st FROM students

GROUP BY name_st

HAVING COUNT(name_st) > 1

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

Вложенные запросы

Это запросы, которые содержатся в теле другого запроса. Они могут использоваться во фразах from, where и having, а также в списке после слова SELECT, создавая, таким образом, вычисляемый столбец.

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

Перечислим несколько основных правил, которые должны соблюдаться при использовании в запросе вложенных запросов:

· тело вложенного запроса всегда заключается в скобки

· вложенные запросы могут содержать другие вложенные запросы, при этом выполнение запроса всегда начинается с самого «глубокого» вложенного запроса и заканчивается внешним запросом

· во вложенном запросе не следует использовать фразу ORDER BY, поскольку сортировка результатов должна быть выполнена один раз после выполнения всего запроса целиком

Другие особенности использования вложенных запросов рассмотрим немного позже. Перейдем к примерам.

⇐ Предыдущая 25 26 27 28 293031 32 33 34 Следующая ⇒