Подзапросы в SQL: примеры для SQLite

Подзапрос (subquery) — это оператор SELECT, вложенный в другой оператор, который используется для извлечения данных, необходимых внешнему запросу, на основе результатов другого запроса

Как устроен подзапрос

Рассмотрим следующую конструкцию:

SELECT column_1
FROM table_1
WHERE column_1 = ( SELECT column_1 FROM table_2
);

Здесь внешним запросом является:

SELECT column_1
FROM table_1
WHERE column_1 =

А подзапросом — вложенная часть:

(SELECT column_1 FROM table_2)

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

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

Подзапрос допустимо использовать в предложениях SELECT, FROM, WHERE и JOIN

По этой теме полезно отдельно посмотреть EXPLAIN QUERY PLAN: план выполнения SQL-запроса в SQLite, чтобы расширить контекст и сравнить подходы

По этой теме полезно отдельно посмотреть Создание Flutter-приложения с SQLite, BLoC и Streams, чтобы расширить контекст и сравнить подходы

Подзапрос в WHERE: примеры

Для демонстрации будем использовать таблицы tracks и albums из учебной базы данных

Пример простого подзапроса — это условие поиска внутри оператора WHERE. Возможно, вы захотите получить все треки из альбома с названием Let There Be Rock, вот как это реализуется:

SELECT trackid, name, albumid
FROM tracks
WHERE albumid = ( SELECT albumid FROM albums WHERE title = 'Let There Be Rock'
);

Этот подзапрос получает идентификатор альбома с названием 'Let There Be Rock', и внешний запрос использует его для нахождения всех треков, ассоциированных с этим albumid

Если подзапрос возвращает несколько значений, можно использовать оператор IN, чтобы проверить наличие одного значения в наборе значений

Рассмотрим пример с таблицами employees и customers, где мы можем использовать подзапрос для нахождения клиентов с торговыми представителями, находящимися в Канаде:

SELECT customerid, firstname, lastname
FROM customers
WHERE supportrepid IN ( SELECT employeeid FROM employees WHERE country = 'Canada'
);

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

Подзапрос в предложении FROM

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

SELECT AVG(SUM(bytes))
FROM tracks
GROUP BY albumid;

Этот запрос недопустим — SQLite не позволяет вкладывать агрегатные функции напрямую

Чтобы исправить это, используем подзапрос в предложении FROM:

SELECT AVG(album.size)
FROM ( SELECT SUM(bytes) SIZE FROM tracks GROUP BY albumid
) AS album;

AVG(album.size)
---------------
338288920.317

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

Коррелированные подзапросы в SQL

Все подзапросы, рассмотренные выше, могут выполняться независимо от внешнего запроса. Коррелированный подзапрос (correlated subquery) — это подзапрос, который зависит от значений внешнего запроса и, следовательно, не может быть вычислен самостоятельно

Коррелированный подзапрос может быть неэффективен, поэтому его следует использовать как последний вариант, когда другие методы не дают желаемого результата

Следующий запрос использует коррелированный подзапрос для возврата альбомов, размер которых меньше 10 МБ:

SELECT albumid, title
FROM albums
WHERE 10000000 > ( SELECT sum(bytes) FROM tracks WHERE tracks.AlbumId = albums.AlbumId
)
ORDER BY title;

Как работает этот запрос: для каждой строки, обрабатываемой во внешнем запросе, коррелированный подзапрос вычисляет размер альбома по трекам, принадлежащим текущему альбому, с помощью функции SUM. Предикат в предложении WHERE фильтрует альбомы, размер которых больше или равен 10 МБ (10 000 000 байт)

Коррелированный подзапрос в предложении SELECT

Следующий запрос использует коррелированный подзапрос в предложении SELECT для возврата количества треков в каждом альбоме:

SELECT albumid, title, ( SELECT count(trackid) FROM tracks WHERE tracks.AlbumId = albums.AlbumId ) tracks_count
FROM albums
ORDER BY tracks_count DESC;

На мой взгляд, это один из самых наглядных примеров коррелированного подзапроса: он вычисляется отдельно для каждой строки таблицы albums и возвращает количество треков, связанных с конкретным альбомом

Типичные ошибки при работе с подзапросами

Работая с подзапросами, легко допустить несколько распространённых ошибок. Разберу каждую из них подробнее

Отсутствие скобок. Подзапрос всегда должен быть заключён в круглые скобки. Без них SQLite вернёт синтаксическую ошибку

Подзапрос возвращает несколько строк при использовании =. Если подзапрос возвращает более одной строки, а внешний запрос использует оператор =, SQLite выдаст ошибку. В таких случаях следует использовать IN вместо =

Вложенные агрегатные функции без подзапроса. Конструкция вида AVG(SUM(bytes)) недопустима напрямую. Решение — вынести внутреннюю агрегацию в подзапрос в предложении FROM, как показано в примере выше

Избыточное использование коррелированных подзапросов. Поскольку коррелированный подзапрос выполняется для каждой строки внешнего запроса, на больших таблицах это может существенно замедлить выполнение. Там, где возможно, стоит заменять их на JOIN

Неправильный псевдоним производной таблицы. При использовании подзапроса в предложении FROM ему необходимо присвоить псевдоним (alias) с помощью AS. Без псевдонима SQLite не сможет обратиться к результирующему набору во внешнем запросе

Ответы на эти вопросы могут быть для вас полезными

Можно ли использовать подзапрос в предложении JOIN? Да, подзапрос можно использовать в предложении JOIN в качестве производной таблицы. При этом ему необходимо присвоить псевдоним через AS

Чем коррелированный подзапрос отличается от обычного? Обычный подзапрос выполняется один раз и не зависит от внешнего запроса. Коррелированный подзапрос ссылается на значения из внешнего запроса и выполняется заново для каждой обрабатываемой строки

Когда использовать IN вместо = в подзапросе? Оператор = подходит только тогда, когда подзапрос гарантированно возвращает одну строку. Если подзапрос может вернуть несколько строк, необходимо использовать IN

Можно ли вкладывать подзапросы друг в друга? Да, SQLite поддерживает вложенные подзапросы. Глубина вложенности ограничена, однако на практике многоуровневые подзапросы встречаются редко и, как правило, их можно заменить более читаемыми конструкциями с JOIN

Почему подзапрос в предложении FROM требует псевдонима? SQLite трактует результат подзапроса в FROM как временную таблицу. Чтобы обратиться к её столбцам во внешнем запросе, таблица должна иметь имя — именно для этого и служит псевдоним через AS