Подзапросы в SQLite: вложенные SELECT в WHERE, FROM и SELECT

Подзапрос — это SELECT внутри SELECT

Подзапрос в SQLite — это обычный SELECT, вложенный внутрь другого запроса и заключённый в круглые скобки. SQLite сначала выполняет внутренний запрос, а его результат передаёт во внешний.

Подготовим небольшой пример, к которому будем возвращаться:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT * FROM orders;

Пять заказов, четыре клиента, двое из которых ничего не заказывали. Этот набор будем использовать дальше во всех примерах.

Подзапрос в WHERE: фильтрация по списку

Самый частый сценарий — это вложенный SELECT в SQLite: внутренний запрос возвращает список id, а внешний по нему фильтрует.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name
FROM customers
WHERE id IN (SELECT customer_id FROM orders);

Внутренний запрос возвращает все customer_id, которые встречаются в таблице orders. Внешний запрос оставляет только тех клиентов, чей id есть в этом списке. Клео, Борис и Ада попадают в результат, а Дмитрий (у него нет заказов) — нет.

IN (SELECT ...) — это рабочая лошадка для запросов вида «строки из A, у которых есть совпадение в B». Читайте это про себя так: «где значение этой колонки совпадает с одним из значений, которые вернул вложенный SELECT».

NOT IN в SQLite: осторожно с NULL

Обратный вопрос — «кто из клиентов ещё не делал заказов?» — решается буквально одной строкой:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

Здесь это срабатывает. Но у NOT IN есть подводный камень: если подзапрос вернёт хотя бы один NULL, то всё выражение NOT IN превратится в NULL (а это не TRUE), и вы получите ноль строк. Сюрприз — причём молчаливый.

Безопасная привычка при использовании NOT IN со столбцом, в котором могут быть NULL:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name
FROM customers
WHERE id NOT IN (
    SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
);

Либо просто используйте NOT EXISTS, у которого этой проблемы нет вообще. До него мы ещё доберёмся.

Скалярный подзапрос в SQLite: одна строка, одно значение

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

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name, total
FROM orders
JOIN customers ON customers.id = orders.customer_id
WHERE total = (SELECT MAX(total) FROM orders);

Внутренний SELECT MAX(total) FROM orders возвращает 200, а внешний запрос отбирает заказы с таким значением. Удобный приём, когда нужно сравнить строки с агрегатом.

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

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT
    name,
    (SELECT COUNT(*) FROM orders WHERE customer_id = customers.id) AS order_count
FROM customers;

Для каждой строки customers внутренний запрос выполняется заново — туда подставляется customers.id. Это и есть коррелированный подзапрос, ниже разберём подробнее. Когда нужно "одно число на строку", связка LEFT JOIN + GROUP BY обычно работает быстрее, но скалярный подзапрос читается куда приятнее.

EXISTS: просто проверить, есть ли совпадения

EXISTS — это спокойный родственник IN. Ему вообще не интересны значения: он лишь проверяет, вернул ли подзапрос хоть одну строку. Внутри обычно пишут SELECT 1 — какая разница, какой там столбец.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.id
      AND o.total > 100
);

Такой запрос находит клиентов, у которых есть хотя бы один заказ дороже 100. Внутренний запрос ссылается на c.id из внешнего — именно эта связь делает подзапрос коррелированным. SQLite прекращает сканировать внутреннюю таблицу сразу после первого совпадения, поэтому EXISTS обычно работает быстрее, чем IN, когда нужно ответить на вопрос «есть ли у этой строки связанная запись?».

Обратная форма, NOT EXISTS, — это безопасный относительно NULL способ спросить «нет связанной строки»:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

Подзапрос в FROM: производная таблица

Подзапрос можно поставить везде, где допустима таблица, — в том числе в FROM. Внутренний запрос превращается во временную именованную «производную таблицу», по которой дальше можно делать JOIN, фильтровать или агрегировать.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT country, AVG(customer_total) AS avg_per_customer
FROM (
    SELECT c.country, c.id, SUM(o.total) AS customer_total
    FROM customers c
    JOIN orders o ON o.customer_id = c.id
    GROUP BY c.id
) AS per_customer
GROUP BY country;

Внутренний запрос считает сумму по каждому клиенту, а внешний — усредняет эти суммы по странам. Двухэтапная агрегация вроде этой — как раз то, ради чего и нужны производные таблицы (derived tables): когда уложить всё в один GROUP BY не получается.

Алиас AS per_customer обязателен: у каждой производной таблицы должно быть имя.

Коррелированный подзапрос в SQLite: выполнение для каждой внешней строки

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

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name,
       (SELECT MAX(total)
        FROM orders
        WHERE customer_id = customers.id) AS biggest_order
FROM customers;

Для каждого клиента находим его самый крупный заказ. Внутренний запрос зависит от customers.id, поэтому выполняется по одному разу для каждого клиента. Клиенты без заказов получат NULL — как раз то, что нужно.

Коррелированный подзапрос в SQLite — естественный выбор для задач вида «для каждой строки из A посчитать что-то по B». Если таблица небольшая или поиск идёт по индексу — всё в порядке. А вот на больших таблицах без подходящих индексов перед выкаткой обязательно прогоните EXPLAIN: связка JOIN с GROUP BY нередко оказывается быстрее.

Подзапрос или JOIN: что выбрать?

Оба запроса ниже отвечают на один и тот же вопрос:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

-- Подзапрос
SELECT name
FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 100);

-- JOIN
SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.total > 100;

Оба варианта возвращают одни и те же строки. Оптимизатор SQLite вообще часто переписывает одну форму в другую под капотом. Так что выбирайте по читаемости:

• Берите подзапрос, когда нужна только фильтрация и колонки из внутренней таблицы в результате не нужны.
• Берите JOIN, когда в выборку должны попасть колонки из обеих таблиц.
• Берите EXISTS, когда вопрос звучит как «существует ли хотя бы одна связанная строка?» — так понятнее, и вы обходите грабли с NULL у IN/NOT IN.

Если сомневаетесь — пишите тот вариант, который понятен сам по себе при чтении вслух.

Типичная ловушка: подзапрос возвращает несколько строк

Подзапрос с оператором = обязан вернуть не больше одной строки. Если строк окажется больше, SQLite молча выберет одну (фактически случайную) — никакой ошибки не будет, зато результат тихо окажется неверным.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

-- Рискованно: что если из Великобритании больше одного клиента?
SELECT * FROM orders
WHERE customer_id = (SELECT id FROM customers WHERE country = 'UK');

Используйте IN, когда вложенный запрос может вернуть несколько строк:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE country = 'UK');

Если вы рассчитываете ровно на одну строку и хотите это гарантировать, добавьте LIMIT 1 вместе с ORDER BY — тогда хотя бы выбор будет детерминированным. А ещё лучше — построить запрос так, чтобы единственная строка была гарантирована самими данными (например, фильтр по уникальной колонке).

Дальше: обобщённые табличные выражения (CTE)

Подзапрос в FROM sqlite быстро превращается в кашу — особенно когда одну и ту же производную таблицу нужно использовать дважды или когда вложенность уходит на три уровня вглубь. Обобщённые табличные выражения (WITH ... AS (...)) позволяют заранее дать подзапросу имя и потом обращаться к нему по этому имени во всём остальном запросе. Об этом — на следующей странице.