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

На этом занятии поговорим о возможности создавать вложенные запросы к СУБД. Лучше всего это понять на конкретном примере. Предположим, что у нас имеются две таблицы:

Первая students содержит информацию о студентах, а вторая marks – их отметки по разным дисциплинам. Каждый из студентов (кроме четвертого) проходил язык Си. От нас требуется выбрать всех студентов, у которых оценка по языку Си выше, чем у Маши (студент с id = 2). По идее нам тут нужно реализовать два запроса: первый получает значение оценки для Маши по языку Си:

SELECT mark FROM marks
WHERE id = 2 AND subject LIKE 'Си'

А второй выбирает всех студентов, у которых оценка по этому предмету выше, чем у Маши:

SELECT name, subject, mark FROM marks
JOIN students ON students.rowid = marks.id
WHERE mark > 3 AND subject LIKE 'Си'

Так вот, в языке SQL эти два запроса можно объединить, используя идею вложенных запросов:

SELECT name, subject, mark FROM marks
JOIN students ON students.rowid = marks.id
WHERE mark > (SELECT mark FROM marks
WHERE id = 2 AND subject LIKE 'Си')
AND subject LIKE 'Си'

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

Но, что если вложенный запрос вернет несколько записей (оценок), например, если записать его вот так:

SELECT name, subject, mark FROM marks
JOIN students ON students.rowid = marks.id
WHERE mark > (SELECT mark FROM marks WHERE id = 2 )
AND subject LIKE 'Си'

В этом случае будет использован только первый полученный результат, другие попросту проигнорируются и результат будет тем же (так как первое значение – это оценка Маши по предмету Си).

Если же вложенный SELECT ничего не находит (возвращает значение NULL), то внешний запрос не будет возвращать никаких записей.

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

SELECT name, subject, mark FROM marks
JOIN students ON students.rowid = marks.id
WHERE mark > (SELECT avg(mark) FROM marks WHERE id = 2 )
AND subject LIKE 'Си'

Вложения в команде INSERT

Вложенные запросы можно объявлять и в команде INSERT. Предположим, что у нас имеется еще одна таблица female вот с такой структурой:

Она идентична по структуре таблице students со списком студентов. Наша задача добавить в female всех студентов женского пола.

Для начала запишем запрос выбора девушек из таблицы students:

SELECT * FROM students WHERE sex = 2

А, затем, укажем, что их нужно поместить в таблицу female:

INSERT INTO female SELECT * FROM students WHERE sex = 2

После выполнения этого запроса таблица female будет содержать следующие записи:

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

Чтобы поправить ситуацию, можно вложенный запрос написать так:

INSERT INTO female 
SELECT NULL, name, sex, old FROM students WHERE sex = 2

Мы здесь в качестве значения первого поля указали NULL и, соответственно, СУБД вместо него сгенерирует уникальный ключ для добавляемых записей. Теперь таблица female выглядит так:

Вложения в команде UPDATE

Похожим образом можно создавать вложенные запросы и для команды UPDATE. Допустим, мы хотим обнулить все оценки в таблице marks, которые меньше или равны минимальной оценки студента с id = 1. Такой запрос можно записать в виде:

UPDATE marks SET mark = 0
WHERE mark <= (SELECT min(mark) FROM marks WHERE id = 1)

И на выходе получим измененную таблицу:

Как видите, минимальная оценка у первого студента была равна 3 и все тройки обнулились.

Вложения в команде DELETE

Ну, и наконец, аналогичные действия можно выполнять и в команде DELETE. Допустим, требуется удалить из таблицы students всех студентов, возраст которых меньше, чем у Маши (студента с id = 2). Запрос будет выглядеть так:

DELETE FROM students
WHERE old < (SELECT old FROM students WHERE id = 2)

В результате, получим таблицу:

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

Конечно, на этом занятии мы лишь рассмотрели примеры, принцип создания вложенных запросов. На практике они могут разрастаться и становиться довольно объемными, включать в себя различные дополнительные операции для выполнения нетривиальных действий с таблицами БД.

На этом мы завершим обзор SQL-языка. Этого материала вам будет вполне достаточно для начальной работы с БД. По мере развития сможете дальше углубляться в эту тему и узнавать множество новых подходов к реализации различных задач с помощью SQL-запросов.

Видео по теме

Python SQLite #1: что такое СУБД и реляционные БД

Python SQLite #2: подключение к БД, создание и удаление таблиц

Python SQLite #3: команды SELECT и INSERT при работе с таблицами БД

Python SQLite #4: команды UPDATE и DELETE при работе с таблицами

Python SQLite #5: агрегирование и группировка GROUP BY

Python SQLite #6: оператор JOIN для формирования сводного отчета

Python SQLite #7: оператор UNION объединения нескольких таблиц

Python SQLite #8: вложенные SQL-запросы

Python SQLite #9: методы execute, executemany, executescript, commit, rollback и свойство lastrowid

Python SQLite #10: методы fetchall, fetchmany, fetchone, Binary, iterdump