DocumentDB SQL – составные SQL-запросы
Составной запрос позволяет объединять данные из существующих запросов, а затем применять фильтры, агрегаты и т. Д. Перед представлением результатов отчета, которые показывают объединенный набор данных. Составной запрос извлекает несколько уровней связанной информации по существующим запросам и представляет объединенные данные в виде единого и сведенного результата запроса.
Используя составной запрос, вы также можете:
- Выберите параметр сокращения SQL, чтобы удалить ненужные таблицы и поля в зависимости от пользовательских атрибутов.
- Установите предложения ORDER BY и GROUP BY.
- Установите предложение WHERE в качестве фильтра для результирующего набора составного запроса.
Выберите параметр сокращения SQL, чтобы удалить ненужные таблицы и поля в зависимости от пользовательских атрибутов.
Установите предложения ORDER BY и GROUP BY.
Установите предложение WHERE в качестве фильтра для результирующего набора составного запроса.
Вышеуказанные операторы могут быть составлены для формирования более мощных запросов. Поскольку DocumentDB поддерживает вложенные коллекции, композиция может быть объединена или вложена.
Давайте рассмотрим следующие документы для этого примера.
Документ AndersenFamily выглядит следующим образом.
< "id": "AndersenFamily", "lastName": "Andersen", "parents": [ < "firstName": "Thomas", "relationship": "father" >, < "firstName": "Mary Kay", "relationship": "mother" >], "children": [ < "firstName": "Henriette Thaulow", "gender": "female", "grade": 5, "pets": [ < "givenName": "Fluffy", "type": "Rabbit" >] > ], "location": < "state": "WA", "county": "King", "city": "Seattle" >, "isRegistered": true >
Документ SmithFamily заключается в следующем.
< "id": "SmithFamily", "parents": [ < "familyName": "Smith", "givenName": "James" >, < "familyName": "Curtis", "givenName": "Helen" >], "children": [ < "givenName": "Michelle", "gender": "female", "grade": 1 >, < "givenName": "John", "gender": "male", "grade": 7, "pets": [ < "givenName": "Tweetie", "type": "Bird" >] > ], "location": < "state": "NY", "county": "Queens", "city": "Forest Hills" >, "isRegistered": true >
Документ WakefieldFamily работает следующим образом.
< "id": "WakefieldFamily", "parents": [ < "familyName": "Wakefield", "givenName": "Robin" >, < "familyName": "Miller", "givenName": "Ben" >], "children": [ < "familyName": "Merriam", "givenName": "Jesse", "gender": "female", "grade": 6, "pets": [ < "givenName": "Charlie Brown", "type": "Dog" >, < "givenName": "Tiger", "type": "Cat" >, < "givenName": "Princess", "type": "Cat" >] >, < "familyName": "Miller", "givenName": "Lisa", "gender": "female", "grade": 3, "pets": [ < "givenName": "Jake", "type": "Snake" >] > ], "location": < "state": "NY", "county": "Manhattan", "city": "NY" >, "isRegistered": false >
Давайте посмотрим на пример каскадного запроса.
Ниже приведен запрос, который будет извлекать идентификатор и местоположение семьи, в которой первый заданный имя ребенка – Мишель.
SELECT f.id,f.location FROM Families f WHERE f.children[0].givenName = "Michelle"
Когда вышеуказанный запрос выполняется, он производит следующий вывод.
Давайте рассмотрим другой пример каскадного запроса.
Ниже приведен запрос, который вернет все документы, в которых первый дочерний класс превышает 3.
SELECT * FROM Families f WHERE (grade: f.children[0].grade>.grade > 3)
Когда вышеуказанный запрос выполняется, он производит следующий вывод.
[ < "id": "WakefieldFamily", "parents": [ < "familyName": "Wakefield", "givenName": "Robin" >, < "familyName": "Miller", "givenName": "Ben" >], "children": [ < "familyName": "Merriam", "givenName": "Jesse", "gender": "female", "grade": 6, "pets": [ < "givenName": "Charlie Brown", "type": "Dog" >, < "givenName": "Tiger", "type": "Cat" >, < "givenName": "Princess", "type": "Cat" >] >, < "familyName": "Miller", "givenName": "Lisa", "gender": "female", "grade": 3, "pets": [ < "givenName": "Jake", "type": "Snake" >] > ], "location": < "state": "NY", "county": "Manhattan", "city": "NY" >, "isRegistered": false, "_rid": "Ic8LAJFujgECAAAAAAAAAA==", "_ts": 1450541623, "_self": "dbs/Ic8LAA==/colls/Ic8LAJFujgE=/docs/Ic8LAJFujgECAAAAAAAAAA==/", "_etag": "\"00000500-0000-0000-0000-567582370000\"", "_attachments": "attachments/" >, < "id": "AndersenFamily", "lastName": "Andersen", "parents": [ < "firstName": "Thomas", "relationship": "father" >, < "firstName": "Mary Kay", "relationship": "mother" >], "children": [ < "firstName": "Henriette Thaulow", "gender": "female", "grade": 5, "pets": [ < "givenName": "Fluffy", "type": "Rabbit" >] > ], "location": < "state": "WA", "county": "King", "city": "Seattle" >, "isRegistered": true, "_rid": "Ic8LAJFujgEEAAAAAAAAAA==", "_ts": 1450541624, "_self": "dbs/Ic8LAA==/colls/Ic8LAJFujgE=/docs/Ic8LAJFujgEEAAAAAAAAAA==/", "_etag": "\"00000700-0000-0000-0000-567582380000\"", "_attachments": "attachments/" > ]
Давайте посмотрим на пример вложенных запросов.
Ниже приведен запрос, который будет выполнять итерацию всех родителей, а затем возвращать документ, в котором familyName – Смит.
SELECT * FROM p IN Families.parents WHERE p.familyName = "Smith"
Когда вышеуказанный запрос выполняется, он производит следующий вывод.
Давайте рассмотрим другой пример вложенного запроса.
Ниже приведен запрос, который вернет все фамилии .
SELECT VALUE p.familyName FROM Families f JOIN p IN f.parents
Когда вышеуказанный запрос выполняется, он производит следующий вывод.
Как составить составной SQL-запрос с обращением к сторонней таблице?
Таблица Films (ссылается на Cinema отношением ManyToOne):
CREATE TABLE films ( id INTEGER GENERATED BY DEFAULT AS SEQUENCE GLOBAL_SEQ PRIMARY KEY, cinema_id INTEGER NOT NULL, date DATE DEFAULT today() NOT NULL, CONSTRAINT films_cinema_id_date UNIQUE (cinema_id, date), FOREIGN KEY (cinema_id) REFERENCES cinemas (id) ON DELETE CASCADE );
Таблица Users :
CREATE TABLE users ( id INTEGER GENERATED BY DEFAULT AS SEQUENCE GLOBAL_SEQ PRIMARY KEY, );
И последняя, четвёртая таблица Votes отображает голосование пользователей за фильмы. То есть: есть несколько кинотеатров. В каждом кинотеатре каждый день идёт новый фильм. Пользователи голосуют за этот фильм, что отражается в таблице Votes :
CREATE TABLE votes ( user_id INTEGER NOT NULL, film_id INTEGER NOT NULL, CONSTRAINT votes_user_film_idx UNIQUE (user_id, film_id), FOREIGN KEY (user_id) REFERENCES users (id), FOREIGN KEY (film_id) REFERENCES films (id) );
Какой запрос мне составить, чтобы получить кинотеатр-победитель за конкретную дату? То есть кинотеатр, за фильм которого на конкретную дату проголосовало больше всего людей. Я подозреваю, что надо использовать MAX и EXISTS, но без понятия как. Не могли бы вы привести пример правильного запроса?
Что такое составной запрос sql
WITH предоставляет способ записывать дополнительные операторы для применения в больших запросах. Эти операторы, которые также называют общими табличными выражениями (Common Table Expressions, CTE ), можно представить как определения временных таблиц, существующих только для одного запроса. Дополнительным оператором в предложении WITH может быть SELECT , INSERT , UPDATE или DELETE , а само предложение WITH присоединяется к основному оператору, которым также может быть SELECT , INSERT , UPDATE или DELETE .
7.8.1. SELECT в WITH
Основное предназначение SELECT в предложении WITH заключается в разбиении сложных запросов на простые части. Например, запрос:
WITH regional_sales AS ( SELECT region, SUM(amount) AS total_sales FROM orders GROUP BY region ), top_regions AS ( SELECT region FROM regional_sales WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales) ) SELECT region, product, SUM(quantity) AS product_units, SUM(amount) AS product_sales FROM orders WHERE region IN (SELECT region FROM top_regions) GROUP BY region, product;
выводит итоги по продажам только для передовых регионов. Предложение WITH определяет два дополнительных оператора regional_sales и top_regions так, что результат regional_sales используется в top_regions , а результат top_regions используется в основном запросе SELECT . Этот пример можно было бы переписать без WITH , но тогда нам понадобятся два уровня вложенных подзапросов SELECT . Показанным выше способом это можно сделать немного проще.
Необязательное указание RECURSIVE превращает WITH из просто удобной синтаксической конструкции в средство реализации того, что невозможно в стандартном SQL. Используя RECURSIVE , запрос WITH может обращаться к собственному результату. Очень простой пример, суммирующий числа от 1 до 100:
WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n < 100 ) SELECT sum(n) FROM t;
В общем виде рекурсивный запрос WITH всегда записывается как не рекурсивная часть, потом UNION (или UNION ALL ), а затем рекурсивная часть, где только в рекурсивной части можно обратиться к результату запроса. Такой запрос выполняется следующим образом:
Вычисляется не рекурсивная часть. Для UNION (но не UNION ALL ) отбрасываются дублирующиеся строки. Все оставшиеся строки включаются в результат рекурсивного запроса и также помещаются во временную рабочую таблицу.
Вычисляется рекурсивная часть так, что рекурсивная ссылка на сам запрос обращается к текущему содержимому рабочей таблицы. Для UNION (но не UNION ALL ) отбрасываются дублирующиеся строки и строки, дублирующие ранее полученные. Все оставшиеся строки включаются в результат рекурсивного запроса и также помещаются во временную промежуточную таблицу.
Примечание
Хотя указание RECURSIVE позволяет определять рекурсивные запросы, внутри такие запросы обрабатываются итерационно.
В показанном выше примере в рабочей таблице на каждом этапе содержится всего одна строка и в ней последовательно накапливаются значения от 1 до 100. На сотом шаге, благодаря условию WHERE , не возвращается ничего, так что вычисление запроса завершается.
Рекурсивные запросы обычно применяются для работы с иерархическими или древовидными структурами данных. В качестве полезного примера можно привести запрос, находящий все непосредственные и косвенные составные части продукта, используя только таблицу с прямыми связями:
WITH RECURSIVE included_parts(sub_part, part, quantity) AS ( SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product' UNION ALL SELECT p.sub_part, p.part, p.quantity * pr.quantity FROM included_parts pr, parts p WHERE p.part = pr.sub_part ) SELECT sub_part, SUM(quantity) as total_quantity FROM included_parts GROUP BY sub_part
Работая с рекурсивными запросами, важно обеспечить, чтобы рекурсивная часть запроса в конце концов не выдала никаких кортежей (строк), в противном случае цикл будет бесконечным. Иногда для этого достаточно применять UNION вместо UNION ALL , так как при этом будут отбрасываться строки, которые уже есть в результате. Однако часто в цикле выдаются строки, не совпадающие полностью с предыдущими: в таких случаях может иметь смысл проверить одно или несколько полей, чтобы определить, не была ли текущая точка достигнута раньше. Стандартный способ решения подобных задач — вычислить массив с уже обработанными значениями. Например, рассмотрите следующий запрос, просматривающий таблицу graph по полю link :
WITH RECURSIVE search_graph(id, link, data, depth) AS ( SELECT g.id, g.link, g.data, 1 FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1 FROM graph g, search_graph sg WHERE g.id = sg.link ) SELECT * FROM search_graph;
Этот запрос зациклится, если связи link содержат циклы. Так как нам нужно получать в результате « depth » , одно лишь изменение UNION ALL на UNION не позволит избежать зацикливания. Вместо этого мы должны как-то определить, что уже достигали текущей строки, пройдя некоторый путь. Для этого мы добавляем два столбца path и cycle и получаем запрос, защищённый от зацикливания:
WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS ( SELECT g.id, g.link, g.data, 1, ARRAY[g.id], false FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1, path || g.id, g.id = ANY(path) FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT cycle ) SELECT * FROM search_graph;
Помимо предотвращения циклов, значения массива часто бывают полезны сами по себе для представления « пути » , приведшего к определённой строке.
В общем случае, когда для выявления цикла нужно проверять несколько полей, следует использовать массив строк. Например, если нужно сравнить поля f1 и f2 :
WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS ( SELECT g.id, g.link, g.data, 1, ARRAY[ROW(g.f1, g.f2)], false FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1, path || ROW(g.f1, g.f2), ROW(g.f1, g.f2) = ANY(path) FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT cycle ) SELECT * FROM search_graph;
Подсказка
Часто для выявления цикла достаточно одного поля, и тогда ROW() можно опустить. При этом будет использоваться не массив данных составного типа, а простой массив, что более эффективно.
Подсказка
Этот алгоритм рекурсивного вычисления запроса выдаёт в результате узлы, упорядоченные «сначала в ширину». Чтобы получить результаты, отсортированные в порядке «сначала в глубину», можно добавить во внешний запрос ORDER BY по столбцу « path » , полученному, как показано выше.
Для тестирования запросов, которые могут зацикливаться, есть хороший приём — добавить LIMIT в родительский запрос. Например, следующий запрос зациклится, если не добавить предложение LIMIT :
WITH RECURSIVE t(n) AS ( SELECT 1 UNION ALL SELECT n+1 FROM t ) SELECT n FROM t LIMIT 100;
Но в данном случае этого не происходит, так как в PostgreSQL запрос WITH выдаёт столько строк, сколько фактически принимает родительский запрос. В производственной среде использовать этот приём не рекомендуется, так как другие системы могут вести себя по-другому. Кроме того, это не будет работать, если внешний запрос сортирует результаты рекурсивного запроса или соединяет их с другой таблицей, так как в подобных случаях внешний запрос обычно всё равно выбирает результат запроса WITH полностью.
Запросы WITH имеют полезное свойство — они вычисляются только раз для всего родительского запроса, даже если этот запрос или соседние запросы WITH обращаются к ним неоднократно. Таким образом, сложные вычисления, результаты которых нужны в нескольких местах, можно выносить в запросы WITH в целях оптимизации. Кроме того, такие запросы позволяют избежать нежелательных вычислений функций с побочными эффектами. Однако есть и обратная сторона — оптимизатор не может распространить ограничения родительского запроса на запрос WITH так, как он делает это для обычного подзапроса. Запрос WITH обычно выполняется буквально и возвращает все строки, включая те, что потом может отбросить родительский запрос. (Но как было сказано выше, вычисление может остановиться раньше, если в ссылке на этот запрос затребуется только ограниченное число строк.)
Примеры выше показывают только предложение WITH с SELECT , но таким же образом его можно использовать с командами INSERT , UPDATE и DELETE . В каждом случае он по сути создаёт временную таблицу, к которой можно обратиться в основной команде.
7.8.2. Изменение данных в WITH
В предложении WITH можно также использовать операторы, изменяющие данные ( INSERT , UPDATE или DELETE ). Это позволяет выполнять в одном запросе сразу несколько разных операций. Например:
WITH moved_rows AS ( DELETE FROM products WHERE "date" >= '2010-10-01' AND "date" < '2010-11-01' RETURNING * ) INSERT INTO products_log SELECT * FROM moved_rows;
Этот запрос фактически перемещает строки из products в products_log . Оператор DELETE в WITH удаляет указанные строки из products и возвращает их содержимое в предложении RETURNING ; а затем главный запрос читает это содержимое и вставляет в таблицу products_log .
Следует заметить, что предложение WITH в данном случае присоединяется к оператору INSERT , а не к SELECT , вложенному в INSERT . Это необходимо, так как WITH может содержать операторы, изменяющие данные, только на верхнем уровне запроса. Однако при этом применяются обычные правила видимости WITH , так что к результату WITH можно обратиться и из вложенного оператора SELECT .
Операторы, изменяющие данные, в WITH обычно дополняются предложением RETURNING (см. Раздел 6.4), как показано в этом примере. Важно понимать, что временная таблица, которую можно будет использовать в остальном запросе, создаётся из результата RETURNING , а не целевой таблицы оператора. Если оператор, изменяющий данные, в WITH не дополнен предложением RETURNING , временная таблица не создаётся и обращаться к ней в остальном запросе нельзя. Однако такой запрос всё равно будет выполнен. Например, допустим следующий не очень практичный запрос:
WITH t AS ( DELETE FROM foo ) DELETE FROM bar;
Он удалит все строки из таблиц foo и bar . При этом число задействованных строк, которое получит клиент, будет подсчитываться только по строкам, удалённым из bar .
Рекурсивные ссылки в операторах, изменяющих данные, не допускаются. В некоторых случаях это ограничение можно обойти, обратившись к конечному результату рекурсивного WITH , например так:
WITH RECURSIVE included_parts(sub_part, part) AS ( SELECT sub_part, part FROM parts WHERE part = 'our_product' UNION ALL SELECT p.sub_part, p.part FROM included_parts pr, parts p WHERE p.part = pr.sub_part ) DELETE FROM parts WHERE part IN (SELECT part FROM included_parts);
Этот запрос удаляет все непосредственные и косвенные составные части продукта.
Операторы, изменяющие данные в WITH , выполняются только один раз и всегда полностью, вне зависимости от того, принимает ли их результат основной запрос. Заметьте, что это отличается от поведения SELECT в WITH : как говорилось в предыдущем разделе, SELECT выполняется только до тех пор, пока его результаты востребованы основным запросом.
Вложенные операторы в WITH выполняются одновременно друг с другом и с основным запросом. Таким образом, порядок, в котором операторы в WITH будут фактически изменять данные, непредсказуем. Все эти операторы выполняются с одним снимком данных (см. Главу 13), так что они не могут « видеть » , как каждый из них меняет целевые таблицы. Это уменьшает эффект непредсказуемости фактического порядка изменения строк и означает, что RETURNING — единственный вариант передачи изменений от вложенных операторов WITH основному запросу. Например, в данном случае:
WITH t AS ( UPDATE products SET price = price * 1.05 RETURNING * ) SELECT * FROM products;
внешний оператор SELECT выдаст цены, которые были до действия UPDATE , тогда как в запросе
WITH t AS ( UPDATE products SET price = price * 1.05 RETURNING * ) SELECT * FROM t;
внешний SELECT выдаст изменённые данные.
Неоднократное изменение одной и той же строки в рамках одного оператора не поддерживается. Иметь место будет только одно из нескольких изменений и надёжно определить, какое именно, часто довольно сложно (а иногда и вовсе невозможно). Это так же касается случая, когда строка удаляется и изменяется в том же операторе: в результате может быть выполнено только обновление. Поэтому в общем случае следует избегать подобного наложения операций. В частности, избегайте подзапросов WITH , которые могут повлиять на строки, изменяемые основным оператором или операторами, вложенные в него. Результат действия таких запросов будет непредсказуемым.
В настоящее время, для оператора, изменяющего данные в WITH , в качестве целевой нельзя использовать таблицу, для которой определено условное правило или правило ALSO или INSTEAD , если оно состоит из нескольких операторов.
Пред. | Наверх | След. |
7.7. Списки VALUES | Начало | Глава 8. Типы данных |
Вложенные запросы против 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 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 . Вложенные запросы лучше оставить для ситуаций, когда без их использования не обойтись.