Подзапросы в основных командах SQL

Рассмотрим применение подзапросов в основных командах SQLite - SELECT, INSERT, UPDATE, DELETE. И для этого возьмем следующие таблицы:

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
);
INSERT INTO companies (name) VALUES  ('Microsoft'), ('Google'), ('JetBrains');
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='JetBrains')),
('Sam', 25, (SELECT id FROM companies WHERE name='Microsoft')),
('Alice', 33, (SELECT id FROM companies WHERE name='Google'));

Подзапросы в SELECT

В выражении SELECT мы можем вводить подзапросы четырьмя способами:

1. В условии в выражении WHERE

2. В условии в выражении HAVING

3. В качестве таблицы для выборки в выражении FROM

4. В качестве спецификации столбца в выражении SELECT

Рассмотрим некоторые из этих случаев. Например, получим всех пользователей, у которых возраст выше среднего:

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

Чтобы получить нужных пользователей, вначале надо выполнить подзапрос на получение среднего возраста: SELECT AVG(age) FROM users.

Операторы IN и BETWEEN

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

SELECT * FROM users
WHERE company_id IN (SELECT id FROM companies 
				WHERE name='Microsoft' OR name='Google');

Здесь оператору IN передается набор значений, который является результатом подзапроса

SELECT id FROM companies WHERE name='Microsoft' OR name='Google'

Другой пример - выберем всех пользователей из таблицы users, у которых возраст либо максимальный, либо минимальный:

SELECT * FROM users
WHERE age IN ((SELECT MIN(age) FROM users), 
			(SELECT MAX(age) FROM users));

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

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

SELECT * FROM users
WHERE age BETWEEN 
	(SELECT AVG(age) FROM users) AND 
	(SELECT MAX(age) FROM users);

Подзапрос как спецификация столбца

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

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

Подзапросы в команде INSERT

В команде INSERT подзапросы могут применяться для определения значения, которое вставляется в один из столбцов, что в принципе уже ранее рассматривалось:

INSERT INTO companies (name) VALUES  ('Microsoft'), ('Google'), ('JetBrains');

INSERT INTO users (name, age, company_id) VALUES 
('Tom', 37, (SELECT id FROM companies WHERE name='Microsoft'));

Подзапросы в команде UPDATE

В команде UPDATE подзапросы могут применяться:

1. В качестве устанавливаемого значения после оператора SET

2. Как часть условия в выражении WHERE

Так, увеличим в таблице users возраст пользователей, которые работают в компании Microsoft, на 3:

UPDATE users
SET age = age + 3
WHERE company_id =(SELECT id FROM companies WHERE name='Microsoft');

Или установим для пользователя с id=1 максимальный возраст:

UPDATE users
SET age = (SELECT MAX(age) FROM users)
WHERE id=1;

Подзапросы в команде DELETE

В команде DELETE подзапросы также применяются как часть условия. Так, удалим всех пользователей, которые работают в Microsoft:

DELETE FROM users
WHERE company_id=(SELECT id FROM companies WHERE name='Microsoft');