Как делать подзапросы в sql
Перейти к содержимому

Как делать подзапросы в sql

  • автор:

Как делать подзапросы в sql

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

Он может использоваться в различных частях основного SQL запроса, но чаще всего он используется в условиях ограничений выборки с помощью операторов сравнения ( = , <> , > , < ).

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

MySQL
SELECT (SELECT name FROM company LIMIT 1) AS company_name; 
company_name
Don_avia

Таким же образом можно использовать скалярные подзапросы для фильтрации строк с помощью WHERE , используя операторы сравнения.

MySQL
SELECT * FROM FamilyMembers WHERE birthday = (SELECT MAX(birthday) FROM FamilyMembers); 
member_id status member_name birthday
8 daughter Wednesday Addams 2005-01-13T00:00:00.000Z

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

При использовании результата подзапроса с операторами сравнения, как в нашем примере, важно, чтобы подзапрос возвращал именно скалярное значение (1 строка и 1 колонка).

Если бы данный подзапрос вернул несколько значений, то СУБД бы вернула ошибку, сообщающую, что ожидалось, что подзапрос вернёт лишь 1 запись: «ER_SUBQUERY_NO_1_ROW: Subquery returns more than 1 row».

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

Как делать подзапросы в SQL: полное руководство и примеры

Как делать подзапросы в SQL? В SQL подзапросы используются для выполнения запросов внутри других запросов. Они позволяют получить данные, основываясь на результатах другого запроса. Вот пример простого подзапроса:

SELECT name FROM students WHERE id IN (SELECT student_id FROM grades WHERE grade = 'A');

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

Детальный ответ

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

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

Подзапросы, или вложенные запросы, представляют собой запросы, которые выполняются внутри других запросов. Они могут быть использованы в разных частях SQL-запроса, таких как SELECT , INSERT , UPDATE или DELETE . Подзапросы обычно возвращают набор данных, который затем может быть обработан внешним запросом.

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

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

Пример 1: Вложенный SELECT

Предположим, у нас есть две таблицы: Customers и Orders . Мы хотим получить список клиентов, которые сделали заказы.

SELECT * FROM Customers WHERE CustomerID IN (SELECT CustomerID FROM Orders) 

В этом примере вложенный запрос SELECT CustomerID FROM Orders возвращает список идентификаторов клиентов, у которых есть заказы. Затем эти идентификаторы используются во внешнем запросе для выборки клиентов из таблицы Customers .

Пример 2: Подзапрос в условии

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

SELECT * FROM Products WHERE Price > (SELECT AVG(Price) FROM Products) 

В данном примере подзапрос SELECT AVG(Price) FROM Products вычисляет среднюю стоимость всех продуктов. Затем эта средняя стоимость используется во внешнем запросе для выборки продуктов, у которых цена выше средней.

Подзапросы с операторами сравнения

Подзапросы могут быть использованы с различными операторами сравнения, такими как = , < , >, <= , >= , <> . Вот несколько примеров использования подзапросов с операторами сравнения:

SELECT * FROM Orders WHERE OrderID NOT IN (SELECT OrderID FROM OrderDetails) 

В этом примере мы проверяем, есть ли какие-либо заказы, для которых нет деталей заказа.

SELECT * FROM Customers WHERE Country = (SELECT Country FROM Customers WHERE CustomerName = 'John') 

В этом примере мы выбираем всех клиентов из той же страны, в которой находится клиент с именем 'John'.

Вывод

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

Как делать подзапросы в sql

Подзапросы (subquery) представляют такие запросы, которые могут быть встроены в другие запросы.

Например, определим таблицы для товаров, покупателей и заказов:

CREATE TABLE Products ( Id SERIAL PRIMARY KEY, ProductName VARCHAR(30) NOT NULL, Company VARCHAR(20) NOT NULL, ProductCount INTEGER DEFAULT 0, Price NUMERIC NOT NULL ); CREATE TABLE Customers ( Id SERIAL PRIMARY KEY, FirstName VARCHAR(30) NOT NULL ); CREATE TABLE Orders ( Id SERIAL PRIMARY KEY, ProductId INTEGER NOT NULL REFERENCES Products(Id) ON DELETE CASCADE, CustomerId INTEGER NOT NULL REFERENCES Customers(Id) ON DELETE CASCADE, CreatedAt DATE NOT NULL, ProductCount INTEGER DEFAULT 1, Price NUMERIC NOT NULL );

Таблица Orders содержит ссылки на две другие таблицы через поля ProductId и CustomerId.

Добавим в эти таблицы некоторые данные:

INSERT INTO Products(ProductName, Company, ProductCount, Price) VALUES ('iPhone X', 'Apple', 2, 66000), ('iPhone 8', 'Apple', 2, 51000), ('iPhone 7', 'Apple', 5, 42000), ('Galaxy S9', 'Samsung', 2, 56000), ('Galaxy S8 Plus', 'Samsung', 1, 46000), ('Nokia 9', 'HDM Global', 2, 26000), ('Desire 12', 'HTC', 6, 38000); INSERT INTO Customers(FirstName) VALUES ('Tom'), ('Bob'),('Sam'); INSERT INTO Orders(ProductId, CustomerId, CreatedAt, ProductCount, Price) VALUES ( (SELECT Id FROM Products WHERE ProductName='Galaxy S9'), (SELECT Id FROM Customers WHERE FirstName='Tom'), '2017-07-11', 2, (SELECT Price FROM Products WHERE ProductName='Galaxy S9') ), ( (SELECT Id FROM Products WHERE ProductName='iPhone 8'), (SELECT Id FROM Customers WHERE FirstName='Tom'), '2017-07-13', 1, (SELECT Price FROM Products WHERE ProductName='iPhone 8') ), ( (SELECT Id FROM Products WHERE ProductName='iPhone 8'), (SELECT Id FROM Customers WHERE FirstName='Bob'), '2017-07-11', 1, (SELECT Price FROM Products WHERE ProductName='iPhone 8') );

При добавлении данных в таблицу Orders как раз используются подзапросы. Например, первый заказ был сделан покупателем Tom на товар Galaxy S9. Поэтому в таблицу Orders необходимо сохранить информацию о заказе, где поле ProductId указывает на Id товара Galaxy S9, поле Price - на его цену, а поле CustomerId - на Id покупателя Tom. Но на момент написания запроса нам может быть неизвестен ни Id покупателя, ни Id товара, ни цена товара. В этом случае можно выполнить подзапрос.

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

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

SELECT * FROM Products WHERE Price = (SELECT MIN(Price) FROM Products);

Или найдем товары, цена которых выше средней:

SELECT * FROM Products WHERE Price > (SELECT AVG(Price) FROM Products);

Подзапросы в PostgreSQL

Коррелирующие подзапросы

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

Но кроме того есть коррелирующие подзапросы (correlated subquery), результаты которых зависят от строк, которые извлекаются в основном запросе.

Например, выберем все заказы из таблицы Orders, добавив к ним информацию о товаре:

SELECT CreatedAt, Price, (SELECT ProductName FROM Products WHERE Products.Id = Orders.ProductId) AS Product FROM Orders;

Здесь для каждой строки из таблицы Orders будет выполняться подзапрос, результат которого зависит от столбца ProductId. И каждый подзапрос может возвращать различные данные.

Correlated subquery in PostgreSQL

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

SELECT ProductName, Company, Price, (SELECT AVG(Price) FROM Products AS SubProds WHERE SubProds.Company=Prods.Company) AS AvgPrice FROM Products AS Prods WHERE Price > (SELECT AVG(Price) FROM Products AS SubProds WHERE SubProds.Company=Prods.Company)

Коррелирующий подзапрос в PostgreSQL

В данном случае определено два коррелирующих подзапроса. Первый подзапрос определяет спецификацию столбца AvgPrice. Он будет выполняться для каждой строки, извлекаемой из таблицы Products. В подзапрос передается производитель товара и на его основе выбирается средняя цена для товаров именно этого производителя. И так как производитель у товаров может отличаться, то и результат подзапроса в каждом случае также может отличаться.

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

Чтобы избежать двойственности при фильтрации в подзапросе при сравнении производителей ( SubProds.Company=Prods.Company ) для внешней выборки установлен псевдоним Prods, а для выборки из подзапросов определен псевдоним SubProds.

SQL: подзапросы, их виды, корректное использование

Подзапросы, возвращающие единственное значение

Подзапросы (вложенные запросы), возвращающие единственное значение, наиболее часто применяются в случаях, когда значение определённого столбца в основном запросе требуется сравнить с некоторым единственным значением при помощи одного из операторов сравнения (=, , =). Значение, с которым производится сравнение, как раз и возвращается подзапросом (вложенным запросом).

Верные признаки того, что подзапрос вернёт одно единственное значение:

  • в подзапросе применяется одна из агрегатных функций (COUNT, SUM, AVG, MAX, MIN);
  • подзапрос извлекает значение уникального идентификатора, например, первичного ключа.

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

В примерах работаем с базой данных "Театр". Таблица Play содержит данные о постановках. Таблица Team - о ролях актёров. Таблица Actor - об актёрах. Таблица Director - о режиссёрах. Поля таблиц, первичные и внешние ключи можно увидеть на рисунке ниже (для увеличения нажать левой кнопкой мыши).

Пример 1. Вывести спектакли режиссёра John Barton. Запрос будет следующим:

SELECT Name FROM PLAY WHERE Dir_ID=( SELECT Dir_ID FROM DIRECTOR WHERE FName='John' AND LName='Barton')

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

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

Подзапросы, возвращающие множество значений

Подзапросы, возвращающие множество значений, могут применяться в запросах с предикатами IN и EXISTS и кванторными функциями ALL и ANY.

Пример 2. В таблице TEAM столбец Mainteam содержит данные о том, главная ли роль закреплена с спектакле за актёром. Значение столбца 'Y' означает "да", 'N' - "нет". Вывести список актёров, которые когда-либо исполняли главные роли. Запрос будет следующим:

SELECT FName, LName FROM ACTOR WHERE Actor_ID IN ( SELECT Actor_ID FROM TEAM WHERE Mainteam='Y')

Подзапрос вернёт множество значений FName и LName, которые через ключ Actor_ID будет передано в основной запрос и окончательно выведены в качестве результата.

На сайте есть отдельный урок о запросах с предикатом IN.

Пример 3. В таблице ACTOR (актёр) есть столбец SEX, содержащий данные о поле ('M' - мужской, 'F' - женский) актёра. Вывести спектакли, в которых играют только мужчины. Запрос будет следующим:

SELECT pl.* FROM PLAY pl WHERE NOT EXISTS ( SELECT 1 FROM TEAM te JOIN ACTOR ac ON ac.Actor_ID=te.Actor_ID WHERE te.Play_ID=pl.Play_ID AND ac.Sex='F')

Предикат NOT EXISTS принимает подзапрос как аргумент и оценивает его как подходящий, если значения sex для одного или более актёров в таблице actor не равны F.

На сайте есть подробный урок о запросах с предикатами EXISTS и NOT EXISTS.

Пример 4. Определить самый популярный жанр театра. Пишем запрос с использованием кванторной функции ALL:

SELECT Genre FROM PLAY GROUP BY Genre HAVING COUNT (*) >= ALL ( SELECT COUNT (*) FROM PLAY GROUP BY Genre)

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

На сайте есть подробный урок о запросах с кванторными функциями ALL и ANY.

Некоррелирующие и коррелирующие подзапросы

Подзапрос, возвращающий результат или результаты, для получения которых значения указанного столбца не должны соотноситься (коррелировать) со значениями столбцов, указанных в основном запросе, называется некоррелирующим. Результат выполнения некоррелирующего запроса не зависит от значений, возвращаемых основным запросом. Обычно некоррелирующие запросы применяются в запросах, в которых значение определённого столбца сравнивается со значением, возвращаемым подзапросом, в запросах с предикатом IN, кванторными функциями ALL и ANY. Однако уже в запросах с предикатом EXISTS применяются коррелирующие подзапросы.

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

Пример 5. Вывести список актёров с количеством их ролей. Пишем следующий запрос с коррелирующим подзапросом:

SELECT DISTINCT a.Actor_ID, a.FName, a.LName, ( SELECT COUNT (*) FROM ACTOR a1 JOIN team t1 ON a1.Actor_ID=t1.ACTOR_ID WHERE a1.Actor_ID=a.Actor_ID GROUP BY a1.Actor_ID) AS NumRoles FROM ACTOR a JOIN team t ON a.Actor_ID=t.ACTOR_ID ORDER BY a.Actor_ID

В основном запросе происходит первое обращение к таблице ACTOR, которая получает псевдоним a. В подзапросе происходит второе обращение к таблице ACTOR, которая получает псевдоним a1. При этом в секции WHERE подзапроса указано условие: идентификаторы актёров, возвращаемые основным запросом и подзапросом, должны совпадать. Это условие - характерный признак коррелирующего подзапроса.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *