Home

Автор: Роман Буданов, тренер курса “Первый Онлайн ИНститут Тестировщиков” (компания “Лаборатория Качества”)

Всем доброго времени суток, мои маленькие (и не очень) любители (и не очень) SQL! На курсе ПОИНТ мы с нуля разбираем построение запросов, обучаемся основным операторам, работе с разными типами данных, но сейчас я хочу с вами поговорить про один из полезных инструментов, увы, оставшихся  за рамками ПОИНТ — сегодня я хочу рассказать вам про такую полезную штуку, как подзапросы и показать несколько вариантов их приготовления.

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

А начать свой рассказ я хочу с объяснения того, что же такое этот ваш (наш) подзапрос — по сути своей, это запрос внутри запроса, с результирующей выборкой которого вы можете творить очень много разных вещей — от вытаскивания из неё всех данных (блок FROM) до задания условий на её основе(блок WHERE). То есть сначала у вас выполняется подзапрос, возвращает свою результирующую выборку, а уже потом, основываясь на результатах работы подзапроса, формируется результирующая выборка основного, “внешнего”, запроса. Впрочем, на словах не особо понятно, давайте покажу на примерах.

Примеры я буду приводить, основываясь на учебной базе данных ПОИНТ. Её схема выглядит вот так:

2. Места для использования подзапроса

a.    Блок From

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

Запрос SELECT * FROM company вернёт нам вот такую результирующую выборку:

А запрос SELECT * FROM (select * from company) c … вернёт нам абсолютно такую же результирующую выборку!

Чудеса, да и только. Как это работает:

1. Сначала мы в подзапросе выводим все поля из таблицы company.
2. Потом во внешнем запросе выводим все поля из результирующей выборки подзапроса (обратите внимание, что для подзапроса в блоке FROM обязательно нужно указать алиас, иначе в MySQL это работать не будет).

Казалось бы — абсолютно бесполезная штука, лишняя трата времени, нервов и ресурсов. Так оно, в принципе, и есть… До тех пор, пока не придёт осознание того, что в блоке FROM есть еще и такая полезная штука, как, например, JOIN - да, джоинить с подзапросом тоже можно! И вот тут-то мы начинаем понимаааааааать. Но еще не до конца. Поэтому давайте разберём пример: допустим, мы ищем покупки всех пользователей, у которых логин заканчивается на букву “а”.

Можно написать так:

select t.*, a.id, a.login from transactions t

join account a on t.account_id=a.id

where a.login like '%a'

и этот запрос нам вернёт такую результирующую выборку (выполнялся 3мс).

А можно написать так:

select * from transactions t

join (select id, login from account where login like '%a') a on t.account_id=a.id

и этот запрос вернёт нам абсолютно такую же результирующую выборку,

но сделает это втрое быстрее  — за 1 мс! Да, понятно, что выигрыш в 2 мс очень не солидно смотрится  — но вы учтите, что у нас учебная база в 5 таблиц и, суммарно, 200 записей. А если представить, что записей не 200, а, допустим, 20000, а то и все 200000 (а такое часто бывает на “боевых” проектах  — там просто ОГРОМНЫЕ базы), то выигрыш в 3 раза смотрится уже не так плохо, а? Давайте теперь подумаем, почему второй вариант работает быстрее.

Как работает первый запрос, с обычным джоином?

1. Берётся первая таблица.
2. Объединяется со второй таблицей.
3. Из получившейся результирующей выборки отбираются подходящие под условие записи.

Как работает второй запрос, с “подзапросным” джоином?

1. Берётся подзапрос, в нём находятся все записи, удовлетворяющие условию.
2. С этими записями уже происходит джоин первой таблицы.

То есть если в первом случае мы джоиним таблицу в 256 записей с таблицей в 20 записей, то во втором случае мы джоиним таблицу в 256 записей с таблицей в 3 записи. А ведь внутри подзапроса можно точно так же использовать джоины… Так что прирост быстродействия  — налицо!

b.    Блок WHERE

А сейчас начнётся самое зрелищное — условия формирования результирующей выборки внешнего запроса, основывающиеся на результате работы подзапроса!

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

Делаем так:

select max(price) from game

находим цену самой дорогой игры. Нашли, отлично. Получилось 8000. Дальше что? Можно, конечно, ручками вбить эти самые 8000 в другой запрос и получить искомое, да. Но это будет справедливо для текущего “наполнения” базы данных. А БД штука такая непостоянная… Я сейчас серьёзно — база, как правило, постоянно обновляется, получая “свежие” записи.

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

select a.login from account a

join transactions t on a.id=t.account_id

join game g on t.game_id=g.id

where

g.price=(select max(g1.price) from game g1)

получаем

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

Если вы принципиально хотите цепляться к айдишнику, а не к цене, то можно поизвращаться и сделать несколько по-другому:

select a.login from account a

join transactions t on a.id=t.account_id

join game g on t.game_id=g.id

where

g.id=

(

select id from game g1

where g1.price=(select max(price) from game g2)

)

На выходе получаем абсолютно то же самое:

Да, глаза вас не обманывают  — вы увидели именно то, что увидели — это подзапрос внутри подзапроса. Да, так тоже можно было.

Почти уверен, что вы сейчас задались — вопросом: “А насколько глубока кроличья нора?”, поэтому сразу отвечу: “Имя им  легион”. Вложенным подзапросам, я имею в виду. Т. е. вы можете плодить столько вложенных друг в друга подзапросов, сколько вашей душе (и здравому смыслу) угодно. И это действительно бывает полезно!

Как это работает: как я и говорил, разворачивать “матрёшку” из подзапросов мы начинаем с самого “дна”  — с последнего вложенного подзапроса, то бишь:

1. Сначала мы находим во вложенном подзапросе самую дорогую игру.
2. Потом находим её айдишник в подзапросе “верхнего уровня”.
3. После чего во внешнем запросе отбираем все транзакции, в которых участвовала игра с этим айдишником.

Думаю, вы уже задались вопросом: “А почему нельзя было в одном подзапросе вывести и цену самой дорогой игры, и её айдишник, а не городить один подзапрос в другом?”. А всё потому, дамы и господа, что оператор max(), как и любая уважающая себя агрегатная функция, нормально работает только по одному полю — т. е. если написать так:

select id, game_name, max(price) from game

то в результате мы увидим вот такую красоту:

Казалось бы - всё ок, да? Вывелся айдишник игры, вывелось её название и цена, как заказывали. Но, вот незадача — это неправильные данные. Проверяем:

select id, game_name, price from game

where id=1 or price=8000

Если бы подвоха не было, то этот запрос вернул бы нам одну-единственную запись — ту же, что и предыдущий запрос.

А мы получаем вот это:

то есть мы видим, что поля id и game_name у нас запрос вывел от первой (по порядку) записи в таблице, а в поле price — самую большую цену, как и заказывали. Вот такой вот обман. MySQL просто вежливый и не стал нам говорить: “Что за ты бред несёшь, я это выполнять не буду” (как сделала бы почти любая другая СУБД), а взял и выполнил тот бред, что мы написали.

Это первый пункт ответа на вопрос “А почему нельзя было в одном подзапросе вывести и цену самой дорогой игры, и её айдишник, а не городить один подзапрос в другом?”. Второй пункт заключается в том, мы используем оператор математического сравнения — а в этом случае подзапрос должен возвращать только одно значение (одно поле, одна запись), чтобы MySQL не растерялся и не спросил: “А с чем именно в подзапросе мне сравнивать значение g.id?”. Вы же не пишете в реальной жизни a=b,c,d,e,f ? Нет, вы пишете a=b — одно значение слева от знака равенства, одно - справа, чтобы не возникло путаницы. Так и здесь — запрос должен возвращать одно-единственное значение, и тогда всё у вас будет работать верно.

3. Основные операторы взаимодействия с подзапросами в блоке WHERE

a.    Операторы сравнения

Раз уж я начал рассказывать про операторы сравнения, давайте ими и продолжу — особенно если учесть то, что, по сути, всё основное я уже рассказал. К вышесказанному добавить хочу только то, что сравнивать можно не только знаком “равно” = , но и всеми остальными логическими операторами:

больше > (находим всех пользователей, которые купили игры, стоящие дороже самой дорогой игры):

select a.login from account a

join transactions t on a.id=t.account_id

join game g on t.game_id=g.id

where

g.price>(select max(g1.price) from game g1)

меньше < (находим всех пользователей, которые купили игры, стоящие дешевле самой дорогой игры):

select a.login from account a

join transactions t on a.id=t.account_id

join game g on t.game_id=g.id

where

g.price<(select max(g1.price) from game g1)

и не равно != (находим всех пользователей, которые купили игры, стоящие дороже или дешевле самой дорогой игры):

select a.login from account a

join transactions t on a.id=t.account_id

join game g on t.game_id=g.id

where

g.price!=(select max(g1.price) from game g1)

b.    Операторы вхождения во множество

Помните, выше мы с вами писали прекрасный запрос на поиск людей, купивших самую дорогую игру? Тот, который напоминал капусту  — с двумя вложенными подзапросами?

Вот он, красавец:

select a.login from account a

join transactions t on a.id=t.account_id

join game g on t.game_id=g.id

where

g.id=(select id from game g1 where g1.price=(select max(price) from game g2))

Он, конечно, всем хорош, но, увы, как и все в этом мире, неидеален. В чём же заключается его неидеальность, вполне резонно спросите вы? А в том, что запрос этот будет работать только в том случае, когда у нас в базе всего одна “самая дорогая” игра. А может у нас быть в базе несколько игр с одинаковой самой высокой ценой? Да запросто, как два байта переслать! И в этом случае у нас запрос споткнётся. Угадаете, почему? Прааааавильно, потому что “внешний” подзапрос вернёт больше одного значения. Что с этим делать? Обратить внимание на операторы вхождения значения в множество, IN и NOT IN. Честно говоря, за проверку вхождения в множество отвечает только один из них, IN. NOT IN делает, как вы, наверное, догадались, диаметрально противоположную проверку.

Так вот, раз уж у нас “внешний” подзапрос будет выводить не одно значение, а несколько (читай — множество), то мы смело можем заменить в нашем запросе знак равенства оператором IN, вот так:

select a.login from account a

join transactions t on a.id=t.account_id

join game g on t.game_id=g.id

where

g.id in (select id from game g1 where g1.price=(select max(price) from game g2))

и всё у нас прекрасно заработает. А как оно заработает? Да достаточно просто на самом деле:

1. Сначала во “внутреннем” подзапросе находится цена самой дорогой игры.
2. Потом во “внешнем” подзапросе находятся игры, имеющие самую высокую цену (несколько).
3. И, под конец, у нас в основном запросе отбираются те транзакции, у которых game_id входит во множество айдишников игр, которое вернул нам “внешний” подзапрос.

Вот видите? Как я и говорил, всё достаточно просто. А если бы мы заменили в запросе IN на NOT IN, тогда бы запрос выдавал нам все транзакции, в которых НЕ участвовали самые дорогие игры.

c. Операторы существования записей

Не пугайтесь страшного названия, мои дорогие читатели — не так страшен чёрт, как его малюют. Сейчас я вам это докажу. Смотрите — этих операторов, как и со множествами, всего 2 — EXISTS и NOT EXISTS. И опять, как и раньше, за проверку существования записей отвечает только EXISTS. NOT EXISTS проверяет, что записей не существует.

Если говорить чуть подробнее, то EXISTS проверяет, что подзапрос вернул непустую результирующую выборку. А NOT EXISTS, соответственно, пустую.

Давайте пример: допустим, нам надо найти всех пользователей, у которых нет ни одной игры, вышедшей в нечётный день месяца. Запрос будет выглядеть так:

select * from account a

where not exists

(

select t.account_id from transactions t

join game g on t.game_id=g.id

where day(g.Release_date)%2=1 and t.account_id=a.id

)

и вернёт он нам пустую результирующую выборку. Нет, это не потому, что запрос неверный. Это потому, что подходящих записей в базе просто нет — да, и такое бывает.

Теперь давайте с вами посмотрим, как это работает (для удобства объяснения давайте представим, что СУБД обрабатывает записи в таблице по очереди).

Как это работает:

1. Берётся первая запись из таблицы account.
2. Строится подзапрос, в котором для этой записи (условие t.account_id=a.id в подзапросе) ищутся транзакции, в которых участвовали игры, вышедшие в нечётный день месяца.
3. Если подзапрос вернул пустоту, значит, первая запись из таблицы account удовлетворяет условию NOT EXISTS и она добавляется в результирующую выборку запроса.
4. Берётся вторая запись из таблицы account.
5. И для неё (как и для всех остальных записей таблицы account) повторяются шаги 2 и 3.

То есть, грубо говоря, для каждой записи таблицы account создаётся отдельный подзапрос, и СУБД проверяет, вернул этот подзапрос пустоту или нет.

Как вы, наверное, заметили, здесь используется такая интересная штука, как связь между данными из внешнего запроса и данными из внутреннего запроса (то самое условие t.account_id=a.id в подзапросе)  — здесь оно просто необходимо, т. к. оператор NOT EXISTS не даёт нам доступа к данным подзапроса — он только говорит нам, вернул ли запрос пустоту, как мы того ожидали, или нет, и мы не можем проверить, для какой именно записи из таблицы account этот подзапрос был создан.

Заключение

Вот, в принципе, и всё, что я хотел вам сегодня рассказать. Надеюсь, эта статья была для вас полезна и помогла вам разобраться в повадках этих грудоломов (личинок Чужих из одноименного фильма) от мира SQL.

Интересных вам задач и объёмных БД, дамы и господа!

Обсудить в форуме