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

Базовые подзапросы

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

mysql> select customerid, amount
-> from orders
-> WHERE amount = (select max(amount) from orders);

Результат его выполнения показан ниже:

В этом случае подзапрос возвращает единственное значение (максимальную сумму заказа), которая затем затем участвует в сравнении в рамках внешнего запроса.

Тот же самый вывод дает запрос с соединением:

mysql> select customerid, amount
-> from orders
-> ORDER BY amount DESC
-> LIMIT 1;

Ввиду того, что приведенный запрос основан на LIMIT, он не совместим с большинством СУРБД, однако в MySQL он выполняется более эффективно, чем вариант с подзапросом.

Одна из главных причин, почему механизм подзапросов так долго не появлялся в MySQL, состоит в том, что существует очень мало вещей, которые можно выполнить только с их помощью. С технической точки зрения, вы можете создать одиночный, совместимый с ANSI SQL запрос, который дает тот же эффект, но основывается на неэффективном трюкачестве, которое носит название MAX-CONCAT.

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

Подзапросы и операции

Существует пять специальных операций подзапросов. Четыре из них используются в обычных подзапросах, и одна (EXISTS) —как правило, только связанных подзапросах.

Таблица Операции подзапросов

Каждая из этих операций может находиться только после операции сравнения, за исключением IN, которая имеет свою операцию сравнения (=), "свернутую внутри".

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

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

mysql> select isbn, title
-> from books
-> WHERE NOT EXISTS
-> (select * from order_items WHERE order_items.isbn = books.isbn);

Результат будет таким:

В приведенном запросе демонстрируется применение как связанных подзапросов, так и специальной операции подзапросов EXISTS. Запрос извлекает все книги, котоые никогда не были заказаны. Обратите внимание, что внутренний запрос включает таблицу order_items только в список FROM, однако ссылается на books. isbn. Другими словами, внутренний запрос ссылается на данные внутреннего запроса. Это и есть определение связанного подзапроса: вы ищите строки внутреннего запроса, которые совпадают (или не совпадают) со строками внешнего запроса.

Операция EXISTS возвращает true, если подзапрос содержит хоть одну совпадающую строку. Соответственно, операция NOT EXISTS возвращает true, ели подзапрос не содержит совпадающих строк.

Строковые подзапросы

Все рассмотренные до сих пор подзапросы возвращали единственное значение, которое в большинстве случаев равно true или false. Строковые подзапросы возвращают целую строку, которая затем может сравниться с целой строкой во внешнем запросе. В общем случае такой подход используется в отношении строк одной таблицы, которые также существуют в другой таблице. Хотя это и не очень хороший пример для базы данных books, однако ниже показан обобщенный пример упомянутого синтаксисаЖ

select c1, c2, c3
from t1
where (c1, c2, c3) in (select c1, c2, c3 from t2);

Использование подзапроса как временной таблицы

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

Например:

mysql> select * from
-> (select customerid, name from customers WHERE city = 'Гатчина')
-> as gatchina_customers;

Результат будет таким:

Обратите внимание, что мы поместили подзапрос в конструкцию FROM. Непосредственно после закрывающей скобки подзапроса вы должны присвоить результату подзапроса какой-то псевдоним. После этого с псевдонимом можно работать как с любой другой таблицей.

Обновление записей в базе данных

Помимо того, что данные необходимо извлекать из базы данных, очень часто из нужно изменять. Например, иногда требуется повысить цены на книги в базе данных. Это можно сделать с помощью оператора UPDATE.

Типичная форма этого оператора выглядит следующим образом:

UPDATE [LOW_PRIORITY] [IGNORE] tablename
SET column1 = expression1, column2 = expression2, ...
[WHERE condition]
[ORDER BY order_criteria]
[LIMIT number]

Основная идея заключается в обновлении таблицы с именем tablename путем установки значения каждого указанного столбца column1, column2 и так далеее равным определенному выражению expression1, expression2 и так далее. Работу оператора UPDATE можно ограничить определенными строками, используя конструкцию WHERE и ограничив общее количество строк, которые будут обновлены, с помощью конструкции LIMIT. Конструкция ORDER BY обычно используется в связке с конструкцией LIMIT. Например, если необходимо обновить только первых 10 строк, то часто требуется сначала расположить строки в определенном порядке. Необязательные конструкции LOW_PRIORITY и IGNORE, если указаны, то работают точно так же, как и в операторе INSERT.

Рассмотрим несколько примеров.

Если нужно повысить цену абсолютно всех книг на 10%, можно воспользоваться оператором UPDATE без конструкции WHERE:

mysql> update books
-> set price = price*1.1;

Если же требуется изменить одну строку, например, адрес определенного клиента, можно поступить следующим образом:

update customers
set address = ' ул. Мира 65'
WHERE customerid = 4;