Подзапросы SQL: виды, IN и EXISTS, коррелированные подзапросы и примеры

Подзапросом в SQL называют запрос SELECT в круглых скобках внутри другого запроса. Внешний запрос использует его результат как одно значение, как список для IN, как проверку EXISTS или как таблицу в FROM, и от этого места зависит, сколько строк подзапрос вправе вернуть.

Примеры выполнены в Python 3.14.3 с модулем sqlite3 (SQLite 3.50.4). Каждый блок создаёт таблицы заново и запускается отдельно. Различия PostgreSQL, MySQL 8.0 и SQL Server даны по их документации.

Учебная база данных

Таблиц две: departments (id, name) и employees (id, name, department_id, salary). Столбец employees.department_id ссылается на departments.id, это обычная пара первичного и внешнего ключа. В данных две ловушки: в отделе «Поддержка» нет ни одного сотрудника, а у стажёра Захара department_id равен NULL. Средняя зарплата по компании равна 119375: 955000 на 8 сотрудников.

Типы подзапросов

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

Скалярный подзапрос в WHERE, SELECT и HAVING

Скалярный подзапрос подставляет одно значение туда, где SQL ждёт число или строку. Классический пример: сотрудники с зарплатой выше средней.

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE departments (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT,
                        department_id INTEGER, salary INTEGER);
INSERT INTO departments VALUES
  (1, 'Разработка'), (2, 'Аналитика'), (3, 'Продажи'), (4, 'Поддержка');
INSERT INTO employees VALUES
  (1, 'Анна', 1, 180000), (2, 'Борис', 1, 150000), (3, 'Вера', 1, 120000),
  (4, 'Глеб', 2, 140000), (5, 'Дина', 2, 110000),
  (6, 'Егор', 3, 90000), (7, 'Жанна', 3, 95000),
  (8, 'Захар', NULL, 70000);
""")


def show(title, sql):
    print("--", title)
    for row in db.execute(sql):
        print(*row)

show("выше средней", """
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC
""")

show("HAVING", """
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees)
""")

show("подзапрос вернул две строки", """
SELECT name, salary
FROM employees
WHERE salary = (SELECT salary FROM employees WHERE department_id = 2)
""")

show("подзапрос не вернул строк", """
SELECT (SELECT name FROM employees WHERE department_id = 4)
""")

Вывод:

-- выше средней
Анна 180000
Борис 150000
Глеб 140000
Вера 120000
-- HAVING
1 150000.0
2 125000.0
-- подзапрос вернул две строки
Глеб 140000
-- подзапрос не вернул строк
None

В WHERE подзапрос вернул 119375, и прошли четверо. В HAVING он сравнивается уже с группами, и остались отделы 1 и 2 со средними 150000.0 и 125000.0. Дробная часть появилась потому, что агрегатная функция AVG в SQLite возвращает число с плавающей точкой, даже если все зарплаты целые. В SELECT скалярный подзапрос работает как вычисляемый столбец, такой пример есть в разделе о коррелированных подзапросах.

Третий запрос показывает главную проблему скалярного подзапроса. В отделе 2 две зарплаты, 140000 и 110000, и подзапрос вернул две строки. SQLite ошибку не выдал: он взял первую строку результата, и Дина из выборки пропала. Какая строка окажется первой без ORDER BY, не определено. PostgreSQL, MySQL и SQL Server на таком запросе падают с ошибкой (таблица в разделе «Типичные ошибки»). Если подходит любое из значений, пишите IN. Если нужно ровно одно, сведите подзапрос к одному значению с помощью MAX, MIN или ORDER BY ... LIMIT 1.

По пустому отделу подзапрос не нашёл строк и вернул NULL, в Python это None.

Подзапросы с IN, NOT IN, EXISTS, ANY и ALL

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE departments (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT,
                        department_id INTEGER, salary INTEGER);
INSERT INTO departments VALUES
  (1, 'Разработка'), (2, 'Аналитика'), (3, 'Продажи'), (4, 'Поддержка');
INSERT INTO employees VALUES
  (1, 'Анна', 1, 180000), (2, 'Борис', 1, 150000), (3, 'Вера', 1, 120000),
  (4, 'Глеб', 2, 140000), (5, 'Дина', 2, 110000),
  (6, 'Егор', 3, 90000), (7, 'Жанна', 3, 95000),
  (8, 'Захар', NULL, 70000);
""")


def show(title, sql):
    print("--", title)
    for row in db.execute(sql):
        print(*row)

show("IN", """
SELECT name
FROM employees
WHERE department_id IN (SELECT id FROM departments
                        WHERE name IN ('Аналитика', 'Продажи'))
ORDER BY name
""")

show("NOT IN", """
SELECT name
FROM departments
WHERE id NOT IN (SELECT department_id FROM employees)
""")

show("NOT IN без NULL", """
SELECT name
FROM departments
WHERE id NOT IN (SELECT department_id FROM employees
                 WHERE department_id IS NOT NULL)
""")

show("NOT EXISTS", """
SELECT d.name
FROM departments AS d
WHERE NOT EXISTS (SELECT 1 FROM employees AS e
                  WHERE e.department_id = d.id)
""")

show("EXISTS", """
SELECT d.name
FROM departments AS d
WHERE EXISTS (SELECT 1 FROM employees AS e
              WHERE e.department_id = d.id AND e.salary >= 140000)
""")

show("4 NOT IN (1, 2, 3, NULL)", "SELECT 4 NOT IN (1, 2, 3, NULL)")

try:
    show("ALL", """
    SELECT name FROM employees
    WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = 2)
    """)
except sqlite3.OperationalError as error:
    print(error)

show("MAX вместо ALL", """
SELECT name, salary
FROM employees
WHERE salary > (SELECT MAX(salary) FROM employees WHERE department_id = 2)
""")

Вывод:

-- IN
Глеб
Дина
Егор
Жанна
-- NOT IN
-- NOT IN без NULL
Поддержка
-- NOT EXISTS
Поддержка
-- EXISTS
Разработка
Аналитика
-- 4 NOT IN (1, 2, 3, NULL)
None
-- ALL
near "ALL": syntax error
-- MAX вместо ALL
Анна 180000
Борис 150000

IN

Здесь подзапрос в WHERE нашёл id отделов 2 и 3, затем внешний запрос выбрал их сотрудников.

NOT IN и NULL

Запрос «отделы без сотрудников» вернул пустой результат, хотя «Поддержка» пуста. Подзапрос отдал список department_id, в котором есть NULL от Захара. Совпадения для id 4 в списке нет, но из-за NULL результат NOT IN равен NULL, а не истине, и строка отбрасывается. Строка 4 NOT IN (1, 2, 3, NULL) показывает то же без таблиц.

Исправить можно фильтрацией NULL через WHERE department_id IS NOT NULL в подзапросе или через NOT EXISTS. Мы бы советовали NOT EXISTS: фильтр IS NOT NULL легко потерять при следующей правке подзапроса, а строка с NULL в NOT EXISTS просто не совпадает с id отдела.

EXISTS и NOT EXISTS

EXISTS возвращает TRUE, если подзапрос нашёл хотя бы одну строку, и FALSE, если не нашёл ни одной. Что стоит в списке SELECT подзапроса, не важно, поэтому по соглашению пишут SELECT 1. В выборке два отдела с зарплатой от 140000, и каждый встречается один раз, сколько бы подходящих сотрудников в нём ни было.

ANY, SOME и ALL

salary > ALL (SELECT ...) истинно, если сравнение истинно для каждой строки подзапроса, а > ANY хватает одной. SOME означает то же, что ANY, а = ANY равносильно IN. PostgreSQL, MySQL и SQL Server эти операторы понимают, SQLite нет: на ALL он ответил syntax error.

В SQLite > ALL заменяют сравнением с MAX: последний запрос нашёл Анну и Бориса, которые получают больше максимальной зарплаты отдела 2. Замена точная, пока подзапрос не пустой и не содержит NULL. На пустом подзапросе ALL даёт истину, а MAX возвращает NULL, и строка отбрасывается.

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

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

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE departments (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT,
                        department_id INTEGER, salary INTEGER);
INSERT INTO departments VALUES
  (1, 'Разработка'), (2, 'Аналитика'), (3, 'Продажи'), (4, 'Поддержка');
INSERT INTO employees VALUES
  (1, 'Анна', 1, 180000), (2, 'Борис', 1, 150000), (3, 'Вера', 1, 120000),
  (4, 'Глеб', 2, 140000), (5, 'Дина', 2, 110000),
  (6, 'Егор', 3, 90000), (7, 'Жанна', 3, 95000),
  (8, 'Захар', NULL, 70000);
""")


def show(title, sql):
    print("--", title)
    for row in db.execute(sql):
        print(*row)

show("подзапрос в FROM", """
SELECT d.name, s.staff, s.avg_salary
FROM departments AS d
INNER JOIN (SELECT department_id, COUNT(*) AS staff, AVG(salary) AS avg_salary
            FROM employees
            GROUP BY department_id) AS s
  ON s.department_id = d.id
WHERE s.avg_salary > 100000
""")

show("без алиаса", """
SELECT MAX(avg_salary)
FROM (SELECT AVG(salary) AS avg_salary FROM employees GROUP BY department_id)
""")

show("WITH", """
WITH dept_stats AS (
  SELECT department_id, COUNT(*) AS staff, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department_id
)
SELECT d.name, s.staff, s.avg_salary
FROM departments AS d
INNER JOIN dept_stats AS s ON s.department_id = d.id
WHERE s.avg_salary > 100000
""")

show("JOIN", """
SELECT d.name
FROM departments AS d
INNER JOIN employees AS e ON e.department_id = d.id
WHERE e.salary > 100000
""")

show("IN", """
SELECT name
FROM departments
WHERE id IN (SELECT department_id FROM employees WHERE salary > 100000)
""")

Вывод:

-- подзапрос в FROM
Разработка 3 150000.0
Аналитика 2 125000.0
-- без алиаса
150000.0
-- WITH
Разработка 3 150000.0
Аналитика 2 125000.0
-- JOIN
Разработка
Разработка
Разработка
Аналитика
Аналитика
-- IN
Разработка
Аналитика

Подзапрос s посчитал по отделам количество сотрудников и среднюю зарплату, INNER JOIN подставил название отдела, WHERE отсёк «Продажи» со средней 92500.

Запрос «без алиаса» SQLite выполнил. В других СУБД правила другие:

Пишите алиас всегда: s.avg_salary читается однозначно, а голое avg_salary в запросе с JOIN заставляет искать, откуда оно.

Подзапрос, JOIN или CTE

Запрос WITH в блоке выше повторяет производную таблицу, только подзапрос вынесен наверх после ключевого слова WITH и получил имя dept_stats. Результат тот же. Обычный CTE работает как представление, которое существует только на время одного запроса, и нужен в первую очередь для читаемости сложных запросов. В PostgreSQL запрос из WITH обычно вычисляется один раз за выполнение, даже если на него ссылаются несколько раз.

Два последних запроса блока отвечают на вопрос «в каких отделах есть зарплаты выше 100000». JOIN вернул строку на каждого подходящего сотрудника: «Разработка» три раза, «Аналитика» два. IN вернул каждый отдел один раз. IN и EXISTS строки не размножают, а DISTINCT поверх JOIN только прячет размножение, поэтому для отбора по наличию пары берите подзапрос. JOIN нужен, когда в результате нужны столбцы обеих таблиц, например имя сотрудника рядом с названием отдела.

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

Коррелированный подзапрос ссылается на столбец внешнего запроса. Логически он выполняется для каждой строки внешнего запроса заново, с её значениями.

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE departments (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT,
                        department_id INTEGER, salary INTEGER);
INSERT INTO departments VALUES
  (1, 'Разработка'), (2, 'Аналитика'), (3, 'Продажи'), (4, 'Поддержка');
INSERT INTO employees VALUES
  (1, 'Анна', 1, 180000), (2, 'Борис', 1, 150000), (3, 'Вера', 1, 120000),
  (4, 'Глеб', 2, 140000), (5, 'Дина', 2, 110000),
  (6, 'Егор', 3, 90000), (7, 'Жанна', 3, 95000),
  (8, 'Захар', NULL, 70000);
""")


def show(title, sql):
    print("--", title)
    for row in db.execute(sql):
        print(*row)

show("выше средней по отделу", """
SELECT e.name, e.department_id, e.salary
FROM employees AS e
WHERE e.salary > (SELECT AVG(e2.salary)
                  FROM employees AS e2
                  WHERE e2.department_id = e.department_id)
""")

show("без алиасов", """
SELECT name, department_id, salary
FROM employees
WHERE salary > (SELECT AVG(salary)
                FROM employees
                WHERE department_id = department_id)
""")

count_sql = """
SELECT d.name,
       (SELECT COUNT(*) FROM employees AS e
        WHERE e.department_id = d.id) AS staff
FROM departments AS d
"""
show("подзапрос в SELECT", count_sql)

show("план без индекса", "EXPLAIN QUERY PLAN" + count_sql)
db.execute("CREATE INDEX idx_employees_department ON employees (department_id)")
show("план с индексом", "EXPLAIN QUERY PLAN" + count_sql)

Вывод:

-- выше средней по отделу
Анна 1 180000
Глеб 2 140000
Жанна 3 95000
-- без алиасов
Анна 1 180000
Борис 1 150000
Глеб 2 140000
-- подзапрос в SELECT
Разработка 3
Аналитика 2
Продажи 2
Поддержка 0
-- план без индекса
2 0 216 SCAN d
6 0 0 CORRELATED SCALAR SUBQUERY 1
11 6 216 SCAN e
-- план с индексом
2 0 216 SCAN d
6 0 0 CORRELATED SCALAR SUBQUERY 1
11 6 53 SEARCH e USING COVERING INDEX idx_employees_department (department_id=?)

Возьмём строку Анны. Внешний запрос подставляет её department_id = 1, подзапрос считает среднюю по отделу 1, получается 150000, и 180000 больше. У Веры 120000, и условие ложно. Захар выпал: e2.department_id = NULL не истинно ни для одной строки, AVG по пустому набору даёт NULL, сравнение тоже.

Запрос «без алиасов» молча считает другое. В подзапросе department_id = department_id сравнивает столбец внутренней таблицы с самим собой, связи с внешней строкой нет. Вышла средняя по всем сотрудникам с отделом, 126428.57, и в выборке появился Борис. Поэтому обеим копиям таблицы нужны псевдонимы (алиасы), здесь e и e2.

В SELECT подзапрос посчитал сотрудников, и «Поддержка» в выводе есть с нулём: COUNT(*) по пустому набору строк вернул 0.

В плане запроса (последний столбец вывода EXPLAIN QUERY PLAN) строка CORRELATED SCALAR SUBQUERY значит, что SQLite выполняет подзапрос заново для каждой строки результата внешнего запроса, а SCAN e значит полный просмотр employees на каждом шаге. После индекса по department_id там SEARCH e USING COVERING INDEX, и читаются только строки нужного отдела.

Подзапросы в UPDATE и DELETE

Подзапрос можно использовать и в командах, которые меняют данные: в SET и в WHERE.

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE departments (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT,
                        department_id INTEGER, salary INTEGER);
INSERT INTO departments VALUES
  (1, 'Разработка'), (2, 'Аналитика'), (3, 'Продажи'), (4, 'Поддержка');
INSERT INTO employees VALUES
  (1, 'Анна', 1, 180000), (2, 'Борис', 1, 150000), (3, 'Вера', 1, 120000),
  (4, 'Глеб', 2, 140000), (5, 'Дина', 2, 110000),
  (6, 'Егор', 3, 90000), (7, 'Жанна', 3, 95000),
  (8, 'Захар', NULL, 70000);
""")


def show(title, sql):
    print("--", title)
    for row in db.execute(sql):
        print(*row)

db.execute("""
UPDATE employees
SET salary = salary + 5000
WHERE department_id = (SELECT id FROM departments WHERE name = 'Продажи')
""")
show("после UPDATE", "SELECT name, salary FROM employees WHERE department_id = 3")

db.execute("""
DELETE FROM departments
WHERE NOT EXISTS (SELECT 1 FROM employees
                  WHERE employees.department_id = departments.id)
""")
show("после DELETE", "SELECT id, name FROM departments")

Вывод:

-- после UPDATE
Егор 95000
Жанна 100000
-- после DELETE
1 Разработка
2 Аналитика
3 Продажи

UPDATE нашёл id отдела «Продажи» скалярным подзапросом и поднял зарплату Егору и Жанне на 5000. DELETE удалил отделы без сотрудников, «Поддержки» больше нет. Здесь подзапросы читают другую таблицу. В MySQL менять таблицу и в подзапросе читать её же в общем случае нельзя: UPDATE t ... WHERE col = (SELECT ... FROM t ...) даст ошибку 1093 You can't specify target table 'x' for update in FROM clause, где вместо x стоит имя таблицы.

Типичные ошибки

Ловушку NOT IN на другой схеме можно отработать в подборке задач по SQL с решениями.

Частые вопросы

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

Общего ответа нет. В SQL Server запрос с подзапросом и равносильный ему запрос без подзапроса обычно работают одинаково, а в отдельных случаях проверки существования JOIN быстрее. Сравнивайте планы своей СУБД через EXPLAIN на своих данных.

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

Обычный не зависит от внешнего запроса, его результат можно посчитать один раз. Коррелированный ссылается на столбец внешнего запроса и логически вычисляется для каждой его строки. В примере выше SQLite без индекса на каждой такой строке просматривал всю таблицу employees.