Вложенные запросы против JOIN в SQL
Введение
Одной из проблем при написании SQL-запросов является выбор между использованием вложенных запросов или объединением таблиц через JOIN. В каких ситуациях следует применять тот или иной подход — рассмотрим в данном материале.
Обычно сложные запросы состоят из основного внешнего SQL-запроса, в который вложены один или несколько подзапросов.
Подзапросы бывают простыми и коррелирующими. Коррелирующие вложенные запросы используют данные из внешнего по отношению к нему запроса.
А использование JOIN может вообще не подразумевать дополнительных подзапросов, а лишь объединяет данные двух или более таблиц в результирующий набор данных. Чаще всего такое объединение делается по первичным и внешним ключам.
При составлении сложных SQL-запросов использоваться могут как JOIN, так и подзапросы, но действуют они по-разному. Когда-то мы можем выбирать более удобный для себя вариант, а иногда подзапросы становятся нашим единственным выходом. Рассмотрим несколько примеров.
Перед вами таблица product, хранящая в себе данные о различных товарах.
Она содержит следующие данные:
id— идентификатор товара.name— название товара.cost— стоимость товара.year— год изготовления товара.city— город изготовления товара.
А вот ещё одна таблица — sale. Здесь находятся сведения о продажах товаров из приведённой выше таблицы.
id— идентификатор продажи.product_id— идентификатор проданного товара.price— цена продажи.year— год продажи.city— город, в котором товар был продан.
Эти две таблицы мы будем использовать при написании сложных SQL-запросов с JOIN и подзапросами.
Когда вложенные запросы стоит заменить на JOIN
Новички часто используют именно вложенные запросы, потому что их проще читать и понимать. В это же время JOIN работает более эффективно, не уступая в читаемости запросов по мере их усложнения. Для начала рассмотрим случаи, когда подзапросы лучше переписать с использованием JOIN для повышения эффективности и удобочитаемости.
Скалярный подзапрос
Работа со скалярным подзапросом один из тех вариантов, когда лучше использовать JOIN. Он возвращает единственное значение (один столбец и одну строку), которое будет использоваться внешним запросом. Рассмотрим пример.
Допустим, мы хотим получить названия и стоимость продуктов, проданных за $2000.
Посмотрим на код с вложенным запросом:
SELECT name, cost
FROM product
WHERE id = (SELECT product_id
FROM sale
WHERE price=2000
AND product_id=product.id
);Результат выглядит следующим образом:
Внешний запрос выбирает из таблицы product столбцы с названиями и стоимостью товаров. Поскольку нам нужны не все товары, мы используем выражение WHERE для фильтрации результата, полученного из вложенного запроса, по идентификаторам товаров.
Теперь посмотрим на вложенный запрос. Таблица sale содержит записи о продажах товаров. Сначала подзапрос выбирает записи только тех товаров, которые были проданы за $2000. Затем он использует идентификаторы проданных товаров (product_id) в отобранных по условию продажах для их сопоставления с соответствующими записями в таблице product (product_id=product.id). Как мы можем видеть, за $2000 были проданы 2 товара: кресло и стол для телевизора, стоимость которых соответственно равна $500 и $2000. Этот подзапрос относится к числу коррелирующих, так как использует данные из внешнего запроса.
На самом деле, такой запрос не очень эффективен. Давайте перепишем этот же запрос, но уже с использованием JOIN.
SELECT p.name, p.cost FROM product p JOIN sale s ON p.id = s.product_id WHERE s.price = 2000;
В этом запросе мы соединили записи из двух таблиц с помощью оператора JOIN, связав полученные данные идентификаторами товаров. В конце, используя выражение WHERE, мы оставили записи лишь о тех продажах, в которых сумма сделки составила $2000.
Подзапрос внутри оператора IN
Если подзапрос содержится внутри оператора IN, его тоже следует переписать с помощью JOIN. В таком случае подзапрос вернет внешнему запросу список значений.
Допустим, мы хотим получить названия и стоимость товаров, которые были проданы:
SELECT name, cost FROM product WHERE id IN (SELECT product_id FROM sale)
В данном случае внешний запрос выбирает из таблицы product названия и стоимость товаров, после чего оставляет лишь те из них, чьи идентификаторы содержатся в списке, возвращаемом подзапросом. Подзапрос, в свою очередь, выбирает из таблицы sale все записи о проданных товаров. По этой причине конечный результат включает в себя информацию только о тех товарах из таблицы product, которые были проданы согласно записям в таблице sale.
Итоговая выборка данных выглядит следующим образом:
Из всех товаров было продано 5 (4 из таблицы выше + tv table, который там так же должен быть).
Перепишем запрос, используя оператор JOIN:
SELECT DISTINCT p.name, p.cost FROM product p JOIN sale s ON s.product_id = p.id;
В итоге наш запрос стал значительно проще. Он объединяет данные из двух таблиц по идентификаторам товаров. Поскольку это то же самое, что и INNER JOIN, запись о товаре из таблицы product не будет возвращена, если сведений о продаже этого товара нет в таблице sale.
Подзапрос внутри оператора NOT IN
Этот случай аналогичен предыдущему, только теперь мы должны получить список непроданных товаров.
Пример с подзапросом внутри оператора NOT IN:
SELECT name, cost FROM product WHERE id NOT IN (SELECT product_id FROM sale);
Подзапросом выбираем идентификаторы товаров в таблице sale и сравнивает их с идентификаторами из внешнего запроса. Если во внешнем запросе такого идентификатора нет, запись о товаре возвращается.
Переписав запрос с помощью JOIN, получаем следующий вариант:
SELECT DISTINCT p.name, p.cost FROM product p LEFT JOIN sale s ON s.product_id=p.id WHERE s.product_id IS NULL;
Как и в примерах выше, данный запрос объединяет записи из двух таблиц по идентификаторам товаров. Также нам следует использовать ключевое слово DISTINCT, чтобы отбросить дубликаты из итоговой выборки.
Обратите внимание, что мы использовали LEFT JOIN в сочетании с WHERE. Используя такую конструкцию запроса, изначально мы выбираем абсолютно все записи товаров из таблицы product, и лишь потом выбираем те из них, чьи идентификаторы в таблице sale равны NULL. В нашем случае значение NULL свидетельствует о том, что товар не был продан.
Коррелирующие подзапросы в выражениях EXISTS и NOT EXISTS
Если вложенный запрос используется с одним из этих операторов, его также можно переписать с использованием JOIN.
Давайте получим подробную информацию о продукции, которую не удалось реализовать в 2020 году.
SELECT name, cost, city
FROM product
WHERE NOT EXISTS (SELECT id FROM sale
WHERE year = 2020 AND product_id = product.id);Вот так выглядит результирующая выборка:
Из общей совокупности товаров, возвращаемой внешним запросом, вложенный запрос выбирает лишь те, которые были проданы в 2020 году. Если подзапрос не смог обнаружить запись, выражение NOT EXISTS вернет значение True.
В итоге мы получаем записи о товарах, которые либо были проданы НЕ в 2020 году, либо не были проданы вовсе.
А так выглядит тот же запрос с использованием JOIN:
SELECT p.name, p.cost, p.city FROM product p LEFT JOIN sale s ON s.product_id = p.id WHERE s.year<>2020 OR s.year IS NULL;
Данный запрос соединяет таблицы product и sale с помощью оператора LEFT JOIN. Это позволяет нам включить в выборку товары, которые не были проданы. Выражение WHERE выбирает две категории товаров:
- У которых нет сведений о продажах (
s.year == NULL). - Которые были проданы НЕ в 2020 году (
s.year <> 2020).
Когда вложенные запросы нельзя заменить оператором JOIN
Несмотря на эффективность оператора JOIN, иногда лучше использовать вложенные запросы. Рассмотрим такие случаи.
Подзапрос внутри FROM вместе с GROUP BY
В качестве первого примера рассмотрим применение запроса, вложенного FROM и использующего GROUP BY для вычисления агрегированных значений.
SELECT city, sum_price FROM ( SELECT city, SUM(price) AS sum_price FROM sale GROUP BY city ) AS s WHERE sum_price < 2100;
В данном случае запрос выбирает города и для каждого из них вычисляет сумму продаж с помощью агрегатной функции SUM(). Внешний запрос выбирает из вложенного только те города, сумма продаж в которых составляет менее $2100 (WHERE sum_price < 2100).
Запрос, вложенный в WHERE и возвращающий агрегированное значение
Другая ситуация, при которой нельзя переписать вложенный запрос с помощью JOIN — агрегированное значение, сравниваемое в предложении WHERE. Пример:
SELECT name FROM product WHERE cost < (SELECT AVG(price) FROM sale);
Этот запрос отбирает названия товаров, чья цена ниже средней суммы продаж. Средняя сумма продаж рассчитывается с помощью агрегатной функции AVG() и возвращается из подзапроса. Затем во внешнем запросе стоимость каждого товара сравнивается с этим средним значением.
Подзапрос в комбинации с ALL
Теперь рассмотрим ситуацию, когда запрос вложен в ALL.
SELECT name FROM product WHERE cost > ALL(SELECT price FROM sale);
Подзапрос возвращает все цены продаж из таблицы sale. Внешний же запрос возвращает название товара, чья цена в product больше любой суммы продаж в sale.
Когда лучше использовать подзапросы, а когда JOIN?
Мы рассмотрели распространенные случаи использования подзапросов, а также ситуации, в которых их можно переписать с использованием JOIN. В большинстве случаев использование JOIN более эффективно, однако иногда вложенные запросы просто необходимы.
Новичкам в SQL легче понимать именно вложенные запросы, хотя для опытных специалистов удобнее читать именно JOIN-конструкции по мере усложнения самих запросов. Более того, если ваш запрос будет содержать несколько уровней вложенных запросов, это сильно ударит по производительности и читаемости кода.
Там, где это возможно, лучше использовать оператор JOIN. Вложенные запросы лучше оставить для ситуаций, когда без их использования не обойтись.
Источник: Learn SQL
👉🏻Подписывайтесь на PythonTalk в Telegram 👈🏻