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

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

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

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-запроса с целью выбора наиболее эффективного.

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

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

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

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

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

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

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

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

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

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

SELECT …. FROM (SELECT … )

полностью равнозначно

SELECT …. FROM имя_таблицы

Например, пусть требуется найти значения минимального и максимального среднего балла студентов. Для решения этой задачи сначала необходимо подсчитать средние баллы по каждому студенту, что можно с успехом выполнить во вложенном запросе. Результаты вложенного запроса затем используются во внешнем запросе как обычная таблица из одного столбца avg_mark (задание псевдонима во вложенном запросе обязательно), над которой производится вычисление агрегатных функций MIN и MAX.

Select min (avg_mark) min_avg_mark, MAX(avg_mark) max_avg_mark

from (select avg (mark) avg_mark from marks group by cod_st)

Если во вложенном запросе выполнить группировку таблицы оценок по предмету (cod_sub), будут получены минимальный и максимальный средний балл по предметам.

Вложенные запросы как альтернатива соединению таблиц в запросах

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

Например, пусть требуется выяснить оценку студента Иванова по математике (если таких студентов несколько – все оценки). Можно легко решить эту задачу, соединив таблицы students, subjects и marks и выполнив выборку из полученной в результате соединения таблицы. Однако, логичным представляется и такой вариант: найти коды всех Ивановых из таблицы students, найти код математики из таблицы subjects, на последнем этапе найти оценку (или несколько оценок) в таблице marks. Получаем такой текст запроса с двумя вложенными запросами:

Select mark from marks

where cod_st in

(select cod_st from students Where name_st=’Иванов’)

and cod_sub IN

(select cod_sub from subjects Where name_sub=’Математика’)

Обратим внимание на использование операции IN (принадлежность значения множеству). Вложенные запросы, которые используются для извлечения кода студента и кода предмета, в общем случае, возвращают множество значений. Хотя в таблице предметов название предмета является уникальным столбцом, второй вложенный запрос может возвратить пустое множество при отсутствии предмета с названием ’Математика’. Поэтому применение операции IN в данной ситуации является правильным.

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

Select cod_st, avg (mark) avg_mark from marks

where cod_st in

(select cod_st from students where name_st=’Иванов’)

group by cod_st

Вложенные запросы в условиях отбора (WHERE и HAVING)

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

Пусть требуется вывести фамилии самых молодых студентов (т.е. тех, у которых самая поздняя дата рождения). Этот запрос состоит из двух этапов: сначала нужно найти самую позднюю дату рождения (эту задачу решит вложенный запрос), а затем всех студентов, которые имеют найденную дату рождения.

SELECT name_st FROM students WHERE born=

(SELECT MAX(born) FROM students)

Здесь знак равенства в условии WHERE уместен, поскольку вложенный запрос гарантированно возвратит одно непустое значение, если в таблице students имеется хотя бы одна строка (на пустой таблице этот запрос также корректно работает, возвращая сообщение «строки не выбраны»).

Следующий пример несколько объемнее, но использует ту же логику разбиения задачи на несколько этапов.

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

В результате получили «многоэтажный», но довольно простой по логике, запрос:

SELECT name_st FROM students WHERE cod_st IN

(SELECT cod_st FROM marks GROUP BY cod_st HAVING AVG(mark)=

(Select MAX(avg_mark) max_avg_mark from

(select avg (mark) avg_mark from marks group by cod_st

)

)

)

Вложенные запросы в качестве вычисляемых столбцов

Такое использование вложенных запросов приводит к очень интересной конструкции

SELECT …(SELECT …) FROM …

Она поддерживается большинством СУБД.

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

SELECT cod_st, name_st,

(SELECT AVG(mark) FROM marks WHERE cod_st=students.cod_st) avg_mark

FROM students

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

В последнем приведенном примере можно обратить внимание на одно важное обстоятельство. Вложенный запрос, использующий только одну таблицу marks, тем не менее, содержит во фразе WHERE столбец из другой таблицы students.mark.

Дело в том, что таблица students используется во внешнем запросе, поэтому ее данные разрешено использовать и во всех вложенных запросах. Обсудим эту проблему подробнее.

Кореллированные и некореллированные вложенные запросы

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

Некореллированные вложенные запросы в большинстве случаев не приводят к снижению производительности, если их использовать аккуратно.

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

Тем не менее, раз такая возможность в языке SQL имеется, пользоваться ею можно, но с предельной осторожностью.

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

Использование ключевых слов ALL и ANY с вложенными запросами

Конструкции ALL(SELECT …) и ANY(SELECT…) применяются к вложенным запросам, возвращающим множество строк, употребляются, как правило, во фразах WHERE и HAVING, и означают буквально следующее:

all (Select…) – все строки вложенного SELECT

any (Select…) – хотя бы одна из строк вложенного SELECT

В некоторых случаях употребление этих слов позволяет сформулировать запрос просто и точно.

Проиллюстрируем это на примере. Пусть требуется вывести студентов, у которых все оценки – только четверки (никаких других оценок нет). Использование ключевого слова ALL позволяет решить эту задачу «в лоб»:

select cod_st,name_st from students

where 4=all(select mark from marks where cod_st=students.cod_st)

Если же требуется найти студентов, у которых имеется хотя бы одна четверка, можно прибегнуть к помощи ключевого слова ANY:

select cod_st,name_st from students

where 4=aNY(select mark from marks where cod_st=students.cod_st)

К сожалению, в данных примерах получились кореллированные вложенные запросы, так что прозрачность формулировок запроса идет в ущерб производительности.

Можно привести примеры более эффективных запросов, решающих те же самые задачи.

Например, чтобы найти студентов, у которых все оценки – четверки, достаточно сообразить, что у таких студентов и минимальная, и максимальная оценки равны 4:

select st.cod_st, st.name_st from students st, marks m

where st.cod_st= m.cod_st

group by m.cod_st

having min(m.marks)=4 and max(m.marks)=4

А найти студентов, у которых есть хотя бы одна четверка, можно, например, таким запросом:

select DISTINCT st.cod_st, st.name_st from students st, marks m

where st.cod_st= m.cod_st AND m.mark=4

Функция EXISTS

Эта функция служит для проверки результатов вложенного запроса на пустоту.

exists(Select…) возвращает значение «истина», если вложенный SELECT возвращает хотя бы одну строку, и «ложь» в противном случае.

Функция NOT EXISTS (SELECT …) противоположна функции EXISTS.

Например, пусть требуется вывести всех студентов, у которых нет оценок.

selecT cod_st, name_st from students

where not exists

(select cod_st from marks where cod_st=students.cod_st )

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

select cod_st, name_st from students

where cod_st not in

(select distinct cod_st from marks)

или внешнее соединение таблиц:

select st.cod_st, st.name_st from students st

left join marks m on st.cod_st=m.cod_st

where m.mark is null