MySQL Примеры подзапросов

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

A подзапрос Внутренний запрос SELECT — это запрос SELECT, который содержится внутри другого запроса. Внутренний запрос SELECT обычно используется для определения результатов внешнего запроса SELECT, поэтому база данных сначала вычисляет внутренний запрос, а затем передает его результат вышестоящему руководству.

Внутренний запрос называется внутренний запрос или вложенный запрос, а содержащий его оператор называется внешний запросДавайте рассмотрим синтаксис подзапроса.

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

Зачем использовать подзапрос?

Прежде чем рассматривать различные типы, полезно знать, когда подзапрос уместно использовать в операторе.

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

Подзапросы находятся по адресуtracЭто выгодно по трем практическим причинам:

• Читаемость: Каждая часть логического утверждения заключена в отдельный блок в скобках, поэтому утверждение воспринимается как последовательность небольших вопросов, а не как одно сложное выражение.
• изоляция: Внутренний запрос можно выполнить отдельно, чтобы убедиться, что он возвращает ожидаемое значение, что значительно упрощает тестирование и отладку.
• Гибкость: Аналогичный принцип работает в предложениях WHERE, HAVING, SELECT и FROM, а также внутри операторов INSERT, UPDATE и DELETE.

Компромисс заключается в скорости, которая рассматривается в сравнении операций объединения (JOIN) далее в этой статье.

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

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

1) Скалярный подзапрос

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

SELECT category_name FROM categories
WHERE category_id = (SELECT MIN(category_id) FROM movies);

Это даёт результат:

Давайте посмотрим, как работает этот запрос.

Как показано на схеме выполнения, MySQL первые запуски SELECT MIN(category_id) FROM moviesФункция получает одно значение и только после этого выполняет внешний запрос с этим значением. Поскольку возвращается одно значение, допустимыми операторами являются стандартные операторы сравнения: =, <> (или !=), >, >=, < и <=.

💡 Совет: Если подзапрос размещен после = возвращает более одной строки. MySQL вызывает ошибку 1242, Подзапрос возвращает более одной строки.Переключите оператора на INили ужесточить внутреннее условие WHERE.

2) Подзапрос строки

A подзапрос строки Также возвращается одна строка, но эта строка может содержать более одного столбца. Поэтому внешний запрос сравнивает строку значений с конструктором строки, а не с одним значением.

SELECT full_names, contact_number FROM members
WHERE (membership_number, gender) = (SELECT membership_number, gender FROM members WHERE full_names = 'Janet Jones');

Допустимыми операторами являются те же операторы сравнения, что и перечисленные выше, применяемые ко всей строке сразу.

3) Подзапрос к таблице

A подзапрос таблицы возвращает несколько строк и часто несколько столбцов, поэтому во внешнем запросе необходимо использовать оператор множеств, например: IN, NOT IN, ANY, ALL или EXISTS.

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

SELECT full_names, contact_number FROM members
WHERE membership_number IN (SELECT membership_number FROM movierentals WHERE return_date IS NULL);

Давайте посмотрим, как работает этот запрос.

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

Вложенные подзапросы на несколько уровней вложенности

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

Предположим, руководство хочет наградить сотрудника, получающего самую высокую зарплату. Мы можем выполнить такой запрос:

SELECT full_names FROM members
WHERE membership_number = (SELECT membership_number FROM payments
    WHERE amount_paid = (SELECT MAX(amount_paid) FROM payments));

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

Как использовать подзапросы с операциями INSERT, UPDATE и DELETE

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

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

INSERT INTO vip_members (membership_number, full_names)
SELECT membership_number, full_names FROM members
WHERE membership_number IN (SELECT membership_number FROM payments WHERE amount_paid > 5000);

ОБНОВЛЕНИЕ с помощью подзапроса. Здесь внутренний запрос определяет, какие строки будут затронуты. В приведенном ниже примере помечаются все участники, у которых еще не оплачена аренда.

UPDATE members
SET reminder_sent = 1
WHERE membership_number IN (SELECT membership_number FROM movierentals WHERE return_date IS NULL);

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

DELETE FROM members
WHERE membership_number NOT IN (SELECT membership_number FROM movierentals);

⚠️ Предупреждение: MySQL Не допускается, чтобы оператор изменял таблицу и одновременно выполнял выборку из той же таблицы внутри подзапроса в предложении FROM. Если появляется ошибка 1093, оберните внутренний запрос в производную таблицу, например. SELECT * FROM (SELECT ...) AS t, Так что MySQL Результат материализуется до применения изменений. Также целесообразно сначала выполнить внутренний запрос SELECT отдельно и подтвердить количество строк, прежде чем выполнять запрос. ОБНОВЛЕНИЕ ПО или УДАЛИТЬ в производстве.

Подзапросы против объединений (JOIN)

И подзапрос, и оператор JOIN позволяют объединять информацию из нескольких таблиц, поэтому возникает закономерный вопрос: какой из них использовать?

По сравнению с объединениями таблиц (JOIN), подзапросы просты в использовании и легко читаются. Они не так сложны, как... Играяи поэтому они часто используются новички в SQL.

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

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

Подзапросы также легко разбить на отдельные логические компоненты, что очень полезно, когда тестов и отладка запросов.

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