Лекция № 6
Тема: "Подзапросы. Использование подзапросов для выборки данных"
План
1. Введение
2. Подзапросы в разделе Where
3. Подзапросы в разделе From
4. Подзапросы в разделе Having
5. Запросы на существование
1. Введение
Подзапрос – это обычный запрос, который используется в разделах From, Where, Having другого запроса.
Использование подзапросов позволяет записать по-новому запросы по связанным таблицам, выполнить расчет по таблице и сразу полученный результат использовать как условие поиска, выполнить такую выборку, которую другим способом выполнить невозможно. Допускается множественное вложение запросов друг в друга.
Однако нужно учесть тот факт, что использование подзапросов оправданно только в том случае, если другое решение невозможно.
Использование подзапросов считается плохим решением, так как такие запросы выполняются во много раз медленнее. Это связано с тем, что для каждой записи в главном запросе отдельно выполняется подзапрос. Чем больше записей в родительском запросе и чем больше уровней вложенности подзапросов, тем дольше будет срабатывать команда поиска.
Если есть возможность заменить подзапрос запросом по связанным таблицам, то лучше выбрать именно связывание, тек как такая операция выполняется во много раз быстрее.
2. Подзапросы в разделах Where
Подзапрос, подсчитывающий одно значение
Такой запрос может подсчитать по таблице некоторое значение, а затем это значение можно использовать как условие отбора в другом запросе. При этом текст подзапроса берется в скобки.
Пример. Отобрать студентов с наибольшей датой рождения (самые молодые).
Определим максимальную дату рождения по таблице students.
Select max(datar) From students;
Выведем все данные о студентах, у которых дата рождения равна найденной максимальной дате.
Select * From students
Where datar= (Select max(datar) From students);
Подзапросы, отбирающие набор значений по одному полю
Подзапрос отбирает некоторый набор значений по одному поля, а затем в главном запросе с помощью функции "in" проверяется вхождение или не вхождение записей в этот набор.
Пример. Отобрать студентов, которые по Физике получили 5.
Вначале в отдельном запросе из таблицы ocenki отберем коды студентов, которые по физике получили 5. Так как название дисциплин находится в таблице predmety, то свяжем эту таблицу с таблицей ocenki.
Select id_stud
From predmety inner join ocenki on predmety.id_predmet=ocenki.id_predmet
Where nazv_predmet='Физика' and ocenka=5;
Подставим этот запрос в основной запрос.
Select *
From students
Where id_stud in (Select id_stud
From predmety inner join ocenki on predmety.id_predmet=ocenki.id_predmet
Where nazv_predmet='Физика' and ocenka=5)
Order by fam;
Если нужно было отобрать студентов, которые по физике не получили 5, тогда в условии добавьте отрицание: "not in".
Select *
From students
Where id_stud not in (Select id_stud
From predmety inner join ocenki on predmety.id_predmet=ocenki.id_predmet
Where nazv_predmet='Физика' and ocenka=5)
Order by fam;
Замечание. Некоторые подзапросы с использование операций in или not in можно заменить обычным связанным запросом, что значительно увеличивает скорость его выполнения.
Для рассмотренного выше примера, который выводит студентов с оценкой 5 по Физике, запрос можно переписать так:
Select *
From students inner join ocenki on students.id_stud=ocenki.id_stud
inner join predmety on predmety.id_predmet=ocenki.id_predmet
Where nazv_predmet='Физика' and ocenka=5
Order by fam;
Пример. Показать студентов, родившихся в один день с "Бахтиным".
Вначале определим даты рождения студента с фамилией "Бахтин"
Select datar
From students
Where fam='Бахтин';
Найдем студентов, родившихся в один день с "Бахтиным"
Select *
From students
Where datar in (Select datar
From students
Where fam='Бахтин');
Замечание. Обратите внимание, что в рассмотренном запрос не рекомендуется заменять операцию in на оператор "=". Так как "Бахтиных" может быть несколько, то и дат рождения может быть найдено несколько. Следовательно использование оператора "=" приведет к ошибке.
Пример. Вывести студентов, которые родились в те же дни, что и студенты группы 38.
Напишем запрос, который отберет даты рождения всех студентов из 38 группы. Поле datar отбирает из таблицы students, условие отбора задаем для поля nazv_grup из таблицы grupy. Свяжем эти две таблицы в запросе:
Select datar
From grupy inner join students on grupy.id_grup=students.id_grup
Where nazv_grup like '38%';
Теперь выведем информацию о студентах, у которых дата рождения равна одной из отобранных выше дат для группы 38.
Select *
From students
Where datar in (Select datar
From grupy inner join students on grupy.id_grup=students.id_grup
Where nazv_grup like '38%');
Подзапрос, который использует поля главного запроса
Такие подзапросы отбирают данные по условиям, в которых используются поля из главного запроса.
Пример. Отобрать всех хорошистов и отличников. Хорошисты – это студенты, у которых нет оценки 3 ни по одному предмету.
Напишем запрос, который выводит все оценки конкретного студента.
Select ocenka
From ocenki
Where id_stud=1;
В рассмотренном примере отобрали оценки для студента в id_stud=1. В реальном запросе будем вместо 1 последовательно подставлять номера каждого студента для получения его оценок.
Напишем запрос, который отберет студентов, у которых нет 3 среди всех их оценок.
Select *
From students
Where 3 not in (Select ocenka
From ocenki
Where ocenki.id_stud=students.id_stud);
Обратите внимание, что в подзапросе в разделе Where условие для поля id_stud записано с указанием имен таблиц, так как это поле имеется в двух таблицах.
При этом поле students.id_stud принадлежит таблице students из главного запроса. Оно будет последовательно передаваться из главного запроса в подзапрос для отбора оценок для очередного студента.
3. Подзапросы в разделе From
Подзапросы можно использовать как отдельные таблицы в списке From. При этом таким таблицам ОБЯЗАТЕЛЬНО ПРИСВАИВАЕТСЯ ПСЕВДОНИМ, их поля доступны как поля обычных таблиц, в том числе их можно связывать с таблицами БД.
Пример. Отобрать фамилию, имя и средний балл каждого студента.
Вначале по таблице ocenki для каждого id_stud найдем его средний балл.
Select id_stud, avg(ocenka) as srbal
From ocenki
Group by id_stud;
Свяжем этот подзапрос с таблицей students по полю id_stud и получим требуемое решение задачи. Для задания связывания необходимо, чтобы подзапрос имел имя, поэтому мы ему присваиваем псевдоним:
Select fam, imya, srbal
From students inner join (Select id_stud, avg(ocenka) as srbal
From ocenki Group by id_stud) as a
on students.id_stud=a.id_stud;
Пример. Для каждого студента вместо среднего балла выведем размер стипендии. Сумму стипендии рассчитывается по формуле:
- 1475 – средний балл 5;
- 1300 – средний балл 4 и выше;
- 0 – в остальных случаях.
Вначале по таблице ocenki для каждого id_stud найдем его средний балл.
Select id_stud, avg(ocenka) as srbal
From ocenki
Group by id_stud;
Свяжем этот подзапрос с таблицей students по полю id_stud и получим требуемое решение задачи.
Select fam, imya, case when srbal=5 then 1475
when srbal>=4 then 1300
else end 0 as stip
From students inner join (Select id_stud, avg(ocenka) as srbal
From ocenki
Group by id_stud) as a
on students.id_stud=a.id_stud;
4. Подзапросы в разделе Having
Если в основном запросе выполняется расчет показателей с группировкой, то в разделе Having такого запроса также можно использовать подзапрос.
Пример. Отобрать студентов, у которых их средний балл выше среднего балла по учебному заведению.
Вначале напишем запрос, который по таблице ocenki определит средний балл по учебному заведению:
Select avg(ocenka) as sredbal
From ocenki;
Теперь из таблицы ocenki отберем коды студентов, у которых средний балл выше среднего.
Select id_stud
From ocenki
Group by id_stud
Having avg(ocenka)>(Select avg(ocenka) as sredbal
From ocenki);
Теперь напишем главный запрос, в котором выведем данные о студентах, коды которых имеются в ранее отобранном списке.
Select *
From students
Where id_stud in (Select id_stud
From ocenki
Group by id_stud
Having avg(ocenka)>(Select avg(ocenka) as sredbal
From ocenki));
Замечание. Такой запрос выполняется очень долго, так как для каждой записи родительского запроса выполняется подзапрос. Поэтому, если есть возможность обойтись без подзапросов, то лучше это сделать.
Например, предыдущий запрос можно написать по-другому.
Вначале напишем запрос, который по таблице ocenki определит средний балл по учебному заведению:
Select avg(ocenka) as sredbal
From ocenki;
Затем по таблице ocenki определим средний балл каждого студента.
Select id_stud, avg(ocenka) as srbal
From ocenki
Group by id_stud;
Напишем главный запрос. При этом свяжет таблицу "students" со вторым подзапросом, а в условии Where используем первый подзапрос.
Каждой таблице и подзапросу в разделах From присвоим псевдонимы.
Select students.*
From students inner join (Select id_stud, avg(ocenka) as srbal
From ocenki
Group by id_stud) as a
on students.id_stud=a.id_stud
where srbal>(Select avg(ocenka) as sredbal
From ocenki);
Такой запрос выполнится намного быстрее.
5. Запросы на существование
В некоторых задачах требуется отобрать данные, у которых имеется (существует) некоторый признак. При этом нас не интересует, какие записи попадают под этот признак, сколько их. Достаточно только знать, что они есть и все.
Проверка существования выполняется специальной функцией:
Exists (подзапрос) - данные существуют
Not Exists (подзапрос) – данные не существуют
Если подзапрос имеет хотя бы одну запись, то функция считается истинной. Если подзапрос пустой, то ложной.
Пример. Вывести список групп, в которых есть отличники (студентов имеют средний балл 5).
Вначале выведем данные о студентах с их средними баллами.
Select students.*
From students inner join (Select id_stud, avg(ocenka) as srbal
From ocenki
Group by id_stud) as a
on students.id_stud=a.id_stud;
Добавим в запрос ограничение, чтобы остались только студенты со средним баллом 5.
Select students.*
From students inner join (Select id_stud, avg(ocenka) as srbal
From ocenki
Group by id_stud) as a
on students.id_stud=a.id_stud
where srbal=5;
Теперь напишем главный запрос, который отберет группы, у которых существуют студенты-отличники. В подзапрос добавим еще одно условие, которое будет отбирать студентов-отличников для каждой группы.
Select nazv_grup
From grupy
Where exists (Select students.*
From students inner join (Select id_stud, avg(ocenka) as srbal
From ocenki
Group by id_stud) as a
on students.id_stud=a.id_stud
Where srbal=5 and students.id_grup=grupy.id_grup);
Если нужно отобрать группы, в которых нет отличников, то в условии вместо функции "exists" укажите функцию "not exists".
Select nazv_grup
From grupy
Where not exists (Select students.*
From students inner join (Select id_stud, avg(ocenka) as srbal
From ocenki
Group by id_stud) as a
on students.id_stud=a.id_stud
Where srbal=5 and students.id_grup=grupy.id_grup);
Функция Exists может быть заменена операцией inner join. Перепишем рассмотренный выше запрос с помощью связывания таблиц.
Select nazv_grup
From grupy inner join (Select students.*
From students inner join (Select id_stud, avg(ocenka) as srbal
From ocenki
Group by id_stud) as a
on students.id_stud=a.id_stud
Where srbal=5) as b
on grupy.id_grup = b.id_grup;
Функцию Not Exists может быть реализована операцией left outer join и проверкой ключевого поля подчиненного подзапроса функцией is null.
Select nazv_grup
From grupy left outer join (Select students.*
From students inner join (Select id_stud, avg(ocenka) as srbal
From ocenki
Group by id_stud) as a
on students.id_stud=a.id_stud
Where srbal=5) as b
on grupy.id_grup = b.id_grup
Where id_stud is null
Вопросы для самоконтроля.
1. Что такое подзапрос? В чем его преимущества и недостатки?
2. В каких разделах основного запроса можно использовать подзапросы?
3. Какие типы подзапросов могут использоваться в разделе Where основного запроса?
4. Какие правила используются при использовании подзапросов в разделе From основного запроса?
5. Какие функции позволяют создать запросы на существование данных?