На некоторые вопросы требуется два шага, чтобы ответить. «Какие заказы выше среднего?» требует, чтобы вы сначала нашли среднее значение, а затем сравнивали с ним каждый заказ. Подзапросы SQL позволяют записать оба шага в один оператор.

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

Что такое подзапрос?

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

SELECT id, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

Примеры таблиц

CREATE TABLE customers (id INT, name TEXT, city TEXT);
CREATE TABLE orders (id INT, customer_id INT, total INT);

INSERT INTO customers VALUES
 (1,'Asha','Pune'), (2,'Ben','Delhi'), (3,'Chitra','Pune'), (4,'Dev','Goa');

INSERT INTO orders VALUES
 (101,1,1200), (102,1,800), (103,2,300), (104,3,2500), (105,3,100);

Обратите внимание, что у Дева нет заказов. Эта деталь важна для нескольких примеров ниже.

Типы подзапросов с первого взгляда

Скалярные подзапросы

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

SELECT id, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

-- 101 | 1200
-- 104 | 2500

Вы не могли бы написать WHERE total > AVG(total), потому что агрегаты не разрешены непосредственно в WHERE. Подзапрос позволяет обойти эту проблему, вычисляя значение отдельно.

Что, если «скалярный» подзапрос вернет несколько строк? Большинство баз данных, включая PostgreSQL, MySQL и SQL Server, выдают ошибку. SQLite нет. В нашем тесте он молча использовал только первую строку, что могло скрыть реальную ошибку, поэтому не полагайтесь на такое поведение.

В и НЕ В

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

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

-- Asha
-- Chitra

NOT IN делает обратное, находя клиентов, которые никогда не заказывали:

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

-- Dev

Здесь это работает, но NOT IN имеет опасный крайний случай, который мы рассмотрим ниже.

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

Подзапрос в FROM действует как временная таблица, часто называемая производной таблицей. Это полезно, когда вам нужно дважды агрегировать данные, как в этом запросе для получения средних расходов на одного клиента в каждом городе:

SELECT city, AVG(spent) AS avg_spent
FROM (
  SELECT c.city, SUM(o.total) AS spent
  FROM customers c
  JOIN orders o ON o.customer_id = c.id
  GROUP BY c.id, c.city
) AS per_customer
GROUP BY city;

-- Delhi |  300.0
-- Pune  | 2300.0

Внутренний запрос подсчитывает итоги по каждому клиенту, а внешний усредняет эти итоги. Присвоение производной таблице псевдонима — хорошая привычка, поскольку этого требуют некоторые базы данных. Если GROUP BY для вас новичок, см. SQL GROUP BY и HAVING.

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

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

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

-- Asha   | 2
-- Ben    | 1
-- Chitra | 2
-- Dev    | 0

Дев появляется с нулем. Внутреннее соединение полностью исключило бы Dev, поскольку у Dev нет соответствующих строк. Тот же результат можно получить с помощью LEFT JOIN и GROUP BY, которые мы объясняем в разделе Объяснение соединений SQL.

Связанные подзапросы

Предыдущий пример также коррелирует, поскольку внутренний запрос ссылается на c.id из внешнего запроса. Некоррелированный подзапрос может выполняться отдельно. Коррелированный вариант невозможен, поскольку он зависит от значения из текущей внешней строки.

При этом количество заказов превышает средний показатель того же клиента:

SELECT o.id, o.customer_id, o.total
FROM orders o
WHERE o.total > (
  SELECT AVG(i.total)
  FROM orders i
  WHERE i.customer_id = o.customer_id
);

-- 101 | 1 | 1200
-- 104 | 3 | 2500

Вот как это читать. Для заказа 101 внутренний запрос усредняет заказы клиента 1 (1200 и 800), что дает 1000. Поскольку 1200 больше, строка сохраняется. Для порядка 102 применяется то же среднее значение, но значение 800 не превышает его, поэтому оно отбрасывается. Одиночный заказ клиента 2 равен его среднему значению, поэтому он тоже отбрасывается.

«Один раз в строке» описывает логику, и оптимизаторы запросов часто переписывают ее во что-то гораздо более эффективное. Тем не менее, медленный коррелированный подзапрос заслуживает изучения. Индекс во внутреннем столбце (здесь orders.customer_id) может иметь большое значение. См. Объяснение индексов SQL.

СУЩЕСТВУЕТ и НЕ СУЩЕСТВУЕТ

EXISTS задает вопрос «да» или «нет»: возвращает ли внутренний запрос хотя бы одну строку? Он может остановиться на первом же совпадении, и то, что вы выберете внутри, не имеет значения, поэтому люди часто пишут SELECT 1.

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

-- Asha, Ben, Chitra

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

-- Dev

Ловушка NOT IN NULL

Это одна из наиболее распространенных ошибок подзапросов. Мы создали таблицу с одним реальным идентификатором и одним NULL, а затем запустили против нее обе формы:

CREATE TABLE r (customer_id INT);
INSERT INTO r VALUES (1), (NULL);

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM r);
-- (no rows at all)

SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM r WHERE r.customer_id = c.id);
-- Ben, Chitra, Dev

Почему первый ничего не возвращает? id NOT IN (1, NULL) означает id <> 1 AND id <> NULL. Любое сравнение с NULL скорее неизвестно, чем истинно, поэтому все условие никогда не может быть истинным. Нет ни сообщения об ошибке, ни предупреждения, просто пустой результат, который выглядит правдоподобно.

Подзапрос против JOIN против CTE

Многие запросы могут быть написаны более чем одним способом. Ранее мы находили клиентов с большим заказом с помощью IN. Вот тот же результат с соединением:

SELECT DISTINCT c.name FROM customers c
JOIN orders o ON o.customer_id = c.id AND o.total > 1000;

-- Asha
-- Chitra

Обратите внимание на DISTINCT. Соединение может создавать дубликаты, если у клиента есть несколько совпадений, тогда как IN и EXISTS не могут.

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

Распространенные ошибки

• Используйте = с многострочным результатом. Используйте IN или убедитесь, что внутренний запрос возвращает одну строку.
• Использование NOT IN для столбца, допускающего значение NULL. Вы можете незаметно потерять каждую строку.
• Использование непонятных псевдонимов. В коррелированных запросах уточняйте каждый столбец, иначе он может быть привязан к неправильной таблице.
• Глубокая вложенность. Три уровня трудно читать, поэтому преобразуйте их в CTE.
• Добавление ORDER BY внутрь. Обычно это не влияет на внешний результат, и некоторые базы данных отклоняют его.

Итоги

Подзапросы — это естественный способ выражения двухэтапных вопросов. Начните со скалярных подзапросов и IN, затем попрактикуйтесь в коррелированных, рассматривая по одной внешней строке за раз. И помните NOT EXISTS всякий раз, когда возможны значения NULL. Для практики напишите один вопрос тремя способами: с подзапросом, соединением и CTE, и сравните результаты и читаемость.

Часто задаваемые вопросы

Продолжайте учиться