Подзапросы

Подзапросы (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);

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

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

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

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

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

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

Коррелирующий подзапрос может выполняться и для той же таблицы, к которой выполняется основной запрос. Например, выберем из таблицы 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)

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

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

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