Подзапросы в командах

Базы данных:

CREATE DATABASE IF NOT EXISTS test;

USE test;

CREATE TABLE Products
(
Id INT AUTO_INCREMENT PRIMARY KEY,
ProductName VARCHAR(30) NOT NULL,
Manufacturer VARCHAR(20) NOT NULL,
ProductCount INT DEFAULT 0,
Price DECIMAL NOT NULL
);
CREATE TABLE Customers
(
Id INT AUTO_INCREMENT PRIMARY KEY,
FirstName VARCHAR(30) NOT NULL
);
CREATE TABLE Orders
(
Id INT AUTO_INCREMENT PRIMARY KEY,
ProductId INT NOT NULL,
CustomerId INT NOT NULL,
CreatedAt DATE NOT NULL,
ProductCount INT DEFAULT 1,
Price DECIMAL NOT NULL,
FOREIGN KEY (ProductId) REFERENCES Products(Id) ON DELETE CASCADE,
FOREIGN KEY (CustomerId) REFERENCES Customers(Id) ON DELETE CASCADE
);

Заполнение баз данных:

USE test;

INSERT INTO Products (ProductName, Manufacturer, ProductCount, Price)
VALUES ('iPhone X', 'Apple', 2, 76000),
('iPhone 8', 'Apple', 2, 51000),
('iPhone 7', 'Apple', 5, 42000),
('Galaxy S9', 'Samsung', 2, 56000),
('Galaxy S8', 'Samsung', 1, 46000),
('Honor 10', 'Huawei', 2, 26000),
('Nokia 8', 'HMD Global', 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 S8'),
(SELECT Id FROM Customers WHERE FirstName='Tom'),
'2018-05-21',
2,
(SELECT Price FROM Products WHERE ProductName='Galaxy S8')
),
(
(SELECT Id FROM Products WHERE ProductName='iPhone X'),
(SELECT Id FROM Customers WHERE FirstName='Tom'),
'2018-05-23',
1,
(SELECT Price FROM Products WHERE ProductName='iPhone X')
),
(
(SELECT Id FROM Products WHERE ProductName='iPhone X'),
(SELECT Id FROM Customers WHERE FirstName='Bob'),
'2018-05-21',
1,
(SELECT Price FROM Products WHERE ProductName='iPhone X')
);

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

В выражении SELECT мы можем вводить подзапросы четырьмя способами:

1. В условии в выражении WHERE
2. В условии в выражении HAVING
3. В качестве таблицы для выборки в выражении FROM
4. В качестве спецификации столбца в выражении SELECT

Рассмотрим некоторые из этих случаев. Например, получим все товары, у которых цена выше средней:

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

Чтобы получить нужные товары, нам вначале надо выполнить подзапрос на получение средней цены товара: SELECT AVG(Price) FROM Products.

Оператор IN

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

SELECT * FROM Products
WHERE Id IN (SELECT ProductId FROM Orders)

То есть подзапрос в данном случае выбирает все идентификаторы товаров из Orders, затем по этим идентификаторам извлекаютя товары из Products.

Добавив оператор NOT, мы можем выбрать те товары, на которые нет заказов в таблице Orders:

SELECT * FROM Products
WHERE Id NOT IN (SELECT ProductId FROM Orders)

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

Получение набора значений

При использовании в операторах сравнения подзапросы должны возвращать одно скалярное значение. Но иногда возникает необходимость получить набор значений. Чтобы при использовании в операторах сравнения подзапрос мог возвращать набор значений, перед ним необходимо использовать один из операторов: ALL, SOME или ANY.

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

SELECT * FROM Products
WHERE Price < ALL(SELECT Price FROM Products WHERE Manufacturer='Apple')

Если бы мы в данном случае опустили бы ключевое слово ALL, то мы бы столкнулись с ошибкой.

Допустим, если данный подзапрос возвращает значения vаl1, val2 и val3, то условие фильтрации фактически было бы аналогично объединению этих значений через оператор AND:

WHERE Price < val1 AND Price < val2 AND Price < val3

В тоже время подобный запрос гораздо проще переписать другим образом:

SELECT * FROM Products
WHERE Price < (SELECT MIN(Price) FROM Products WHERE Manufacturer='Apple')

Как работает оператор ALL:

• x > ALL (1, 2) эквивалентно x > 2
• x < ALL (1, 2) эквивалентно x < 1
• x = ALL (1, 2) эквивалентно (x = 1) AND (x = 2)
• x <> ALL (1, 2) эквивалентно x NOT IN (1, 2)

Операторы ANY и SOME условие в операции сравнения должно быть истинным для хотя бы одного из значений, возвращаемых подзапросом. По своему действию оба этих оператора аналогичны, поэтому можно применять любой из них. Например, в следующем случае получим товары, которые стоят меньше самого дорогого товара компании Apple:

SELECT * FROM Products
WHERE Price < ANY(SELECT Price FROM Products WHERE Manufacturer='Apple')

И также стоит отметить, что данный запрос можно сделать проще, переписав следующим образом:

SELECT * FROM Products
WHERE Price < (SELECT MAX(Price) FROM Products WHERE Manufacturer='Apple')

Как работает оператор ANY (а также SOME):

• x > ANY (1, 2) эквивалентно x > 1
• x < ANY (1, 2) эквивалентно x < 2
• x = ANY (1, 2) эквивалентно x IN (1, 2)
• x <> ANY (1, 2) эквивалентно (x <> 1) OR (x <> 2)

Подзапрос как спецификация столбца

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

SELECT *, 
(SELECT ProductName FROM Products WHERE Id=Orders.ProductId) AS Product 
FROM Orders

Подзапросы в команде INSERT

В команде INSERT подзапросы могут применяться для определения значения, которое вставляется в один из столбцов:

INSERT INTO Orders (ProductId, CreatedAt, ProductCount, Price)
VALUES
( 
	(SELECT Id FROM Products WHERE ProductName='Galaxy S8'),
	'2018-05-23',  
	2, 
	(SELECT Price FROM Products WHERE ProductName='Galaxy S8')
)

Подзапросы в команде UPDATE

В команде UPDATE подзапросы могут применяться:

1. В качестве устанавливаемого значения после оператора SET
2. Как часть условия в выражении WHERE

Так, увеличим в таблице Orders количество купленных товаров компании Apple на 2:

UPDATE Orders
SET ProductCount = ProductCount + 2
WHERE ProductId IN (SELECT Id FROM Products WHERE Manufacturer='Apple');

Или установим для заказа цену товара, полученную в результате подзапроса:

UPDATE Orders
SET Price = (SELECT Price FROM Products WHERE Id=Orders.ProductId) + 3000
WHERE Id=1;

Подзапросы в команде DELETE

В команде DELETE подзапросы также применяются как часть условия. Так, удалим все заказы на Galaxy S8:

DELETE FROM Orders
WHERE ProductId=(SELECT Id FROM Products WHERE ProductName='Galaxy S8');