Подзапросы

Подзапросы представляют выражения SELECT, которые встроены в другие запросы SQL. Рассмотрим простейший пример применения подзапросов.

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

DROP TABLE IF EXISTS companies;
DROP TABLE IF EXISTS users;
CREATE TABLE companies
(
	id INTEGER PRIMARY KEY AUTOINCREMENT, 
	name TEXT NOT NULL
);
CREATE TABLE users
(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
	name TEXT NOT NULL,
    age INTEGER,
	company_id INTEGER
);

Таблица users содержит поле company_id, которое будет представлять id компании из таблицы companies.

Добавим в таблицы некоторые данные:

INSERT INTO companies (name) VALUES  ('Microsoft'), ('Google');
INSERT INTO users (name, age, company_id) VALUES 
('Tom', 37, (SELECT id FROM companies WHERE name='Microsoft')),
('Bob', 41, (SELECT id FROM companies WHERE name='Google')),
('Sam', 25, (SELECT id FROM companies WHERE name='Microsoft')),
('Alice', 33, (SELECT id FROM companies WHERE name='Google'));

SELECT * FROM users;

При добавлении данных в таблицу users как раз используются подзапросы. Например, первый пользователь работает в компании "Microsoft". Соответственно в таблицу users нам надо сохранить id компании, у которой поле name хранит строку "Microsoft". Но на момент написания запроса нам может быть неизвестен ни id компании. В этом случае можно выполнить подзапрос в виде

(SELECT id FROM companies WHERE name='Microsoft')

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

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

SELECT * FROM users
WHERE age > (SELECT AVG(age) FROM users);

Коррелирующие и некоррелирующие подзапросы

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

Но также можно использовать и коррелирующие подзапросы (correlated subquery), результаты которых зависят от строк, которые выбираются в основном запросе.

Например, выберем всех пользователей из таблицы users, добавив к ним информацию о компании:

SELECT name, age, 
		(SELECT name FROM companies 
		WHERE companies.id = users.company_id) AS company
FROM users;

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

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

Коррелирующий подзапрос может выполняться и для той же таблицы, к которой выполняется основной запрос. Например, выберем из таблицы users тех пользователей, возраст которых выше среднего возраста пользователей той же компании:

SELECT name,
	age,
	(SELECT AVG(age) FROM users AS subuser 
	WHERE subuser.company_id=user.company_id) AS avg_age
FROM users AS user
WHERE age > 
	(SELECT AVG(age) FROM users AS subuser 
	WHERE subuser.company_id=user.company_id);

Здесь определено два коррелирующих подзапроса. Первый подзапрос определяет спецификацию столбца avg_age. Он будет выполняться для каждой строки, извлекаемой из таблицы users. В подзапрос передается id компании и на его основе выбирается средняя цена для пользователей именно этой компании. И так как компания у пользователей может отличаться, то и результат подзапроса в каждом случае также может отличаться.

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

Чтобы избежать двойственности при фильтрации в подзапросе при сравнении id компаний (subuser.company_id=user.company_id) для внешней выборки установлен псевдоним user, а для выборки из подзапросов определен псевдоним subuser.