Подзапросы

Андрей Аксенов

Contents

Классификация подзапросов
Примеры использования

Подзапрос — это SQL-запрос, вложенный в другой запрос. Он выполняется первым, а основной запрос использует его результат. В этой статье описывается, как использовать подзапросы в SELECT-запросах.

Подзапросы можно классифицировать по-разному в зависимости от их поведения и места использования в основном запросе. В этом разделе представлены основные типы подзапросов в зависимости от следующих факторов:

структура возвращаемых данных (количество строк и столбцов);
наличие зависимости от внешнего запроса (коррелированность);
расположение в структуре основного запроса.

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

Скалярный подзапрос

Скалярный подзапрос — это подзапрос, который выбирает только один столбец или выражение и возвращает одну строку — единственное скалярное значение. Он часто возвращает результат агрегатных функций, таких как SUM(), AVG(), MIN() или MAX(). Скалярные подзапросы могут использоваться в любом месте, где ожидается одно значение, например, в операциях сравнения с такими операторами, как =, <, > и другими. Они также часто используются в списке SELECT, чтобы добавить вычисляемые значения к каждой строке результата.
Табличный подзапрос

Табличный подзапрос — это подзапрос, результат которого представляет собой таблицу с несколькими строками или несколькими столбцами. Его применение зависит от количества возвращаемых строк и столбцов.
- Одна строка, несколько столбцов
  
  Может использоваться для построчных сравнений с участием нескольких столбцов, например, (col1, col2) = (SELECT …).
- Несколько строк, один столбец
  
  Часто используется в подзапросах с выражениями, использующими операторы IN, ANY, ALL и другие.
- Несколько строк и столбцов
  
  Часто используется как производная таблица в выражениях FROM и JOIN.

Эта классификация описывает, зависит ли подзапрос от значений внешнего запроса.

Некоррелированный подзапрос

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

Зависит от значений текущей строки внешнего запроса. Он не может быть выполнен самостоятельно, поскольку ссылается на столбцы внешнего запроса. Greengage DB использует один из следующих методов для выполнения коррелированных подзапросов:
- Преобразование коррелированного подзапроса в операции соединения (JOIN)
  
  Это наиболее эффективный метод, применяемый для большинства коррелированных подзапросов.
- Выполнение подзапроса для каждой строки внешнего запроса
  
  Этот метод выполняет подзапрос для каждой строки внешнего запроса. Он менее эффективен и используется, когда подзапрос указан в списке SELECT или в условиях, содержащих OR.

Подзапросы могут встречаться в различных частях запроса SELECT, выполняя определенные задачи:

В выражении WHERE — для фильтрации строк на основе результатов другого запроса.

SQL-запрос с подзапросом в выражении WHERE имеет следующий вид:
```
SELECT <columns>
FROM <table_name>
WHERE (<column> [, <column> ...]) [operator] (<subquery>);
```
Это позволяет использовать условия с операторами IN, ANY, ALL, а также сравнения (=, <, > и другие) со скалярными подзапросами.
В выражениях FROM или JOIN — для определения временной таблицы (также известной как производная таблица).

SQL-запрос с подзапросом в выражении FROM имеет следующий вид:
```
SELECT <expression>
FROM (<subquery>) AS <alias>;
```
SQL-запрос с подзапросом в выражении JOIN имеет следующий вид:
```
SELECT <expression>
FROM <table_name>
JOIN (<subquery>) AS <alias> ON <join_condition>;
```
Каждая таблица в выражении FROM или JOIN должна иметь псевдоним. Все столбцы в списке SELECT подзапроса должны иметь уникальные имена.
В списке SELECT — для вычисления значений на основе связанных данных.

SQL-запрос с подзапросом в списке SELECT имеет следующий вид:
```
SELECT <expression>,
       (<subquery>) [AS <alias>]
FROM <table_name>;
```

Предварительные требования

Для выполнения команд, описанных в следующих разделах, подключитесь к мастер-хосту Greengage DB с помощью psql, как описано в статье Подключение к Greengage DB с использованием psql. Создайте новую базу данных и подключитесь к ней:

CREATE DATABASE marketplace;
\c marketplace

Затем создайте три связанных таблицы и заполните их данными:

categories

Создайте таблицу для хранения различных категорий товаров:

CREATE TABLE categories
(
    category_code TEXT PRIMARY KEY,
    name          TEXT
)
    DISTRIBUTED REPLICATED;

Добавьте данные:

INSERT INTO categories
VALUES ('elec', 'Electronics'),
       ('cloth', 'Clothing'),
       ('home', 'Home');

products

Создайте таблицу для хранения информации о каждом товаре, включая категорию, к которой он относится:

CREATE TABLE products
(
    id            INT,
    name          TEXT,
    category_code TEXT REFERENCES categories (category_code),
    price         NUMERIC(8, 2),
    stock         INT
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (id);

Добавьте данные:

INSERT INTO products
VALUES (1, 'Laptop', 'elec', 1200.00, 15),
       (2, 'Headphones', 'elec', 150.00, 40),
       (3, 'Coffee Maker', 'home', 85.00, 20),
       (4, 'T-Shirt', 'cloth', 25.00, 100),
       (5, 'Desk Chair', 'home', 200.00, 10),
       (6, 'Smartphone', 'elec', 800.00, 30),
       (7, 'Jacket', 'cloth', 60.00, 50),
       (8, 'Blender', 'home', 55.00, 25);
-- Товары с ID 7 и 8 отсутствует в таблице 'sales'

sales

Создайте таблицу, содержащую записи о продажах:

CREATE TABLE sales
(
    id         INT,
    product_id INT,
    quantity   INT,
    date       DATE
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (product_id);

Добавьте данные:

INSERT INTO sales
VALUES (1, 1, 1, '2025-01-03'),
       (2, 2, 2, '2025-01-05'),
       (3, 3, 1, '2025-01-07'),
       (4, 4, 3, '2025-01-10'),
       (5, 5, 1, '2025-01-15'),
       (6, 6, 2, '2025-01-22'),
       (7, 2, 1, '2025-02-01'),
       (8, 4, 2, '2025-02-03'),
       (9, 1, 1, '2025-02-07'),
       (12, 6, 1, '2025-02-18'),
       (13, 3, 1, '2025-03-01'),
       (14, 5, 2, '2025-03-05'),
       (16, 2, 3, '2025-03-10'),
       (17, 1, 1, '2025-03-15'),
       (18, 4, 2, '2025-03-20');

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

SELECT name,
       price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

Результат должен выглядеть следующим образом:

    name    |  price
------------+---------
 Laptop     | 1200.00
 Smartphone |  800.00
(2 rows)

В этом примере используется коррелированный скалярный подзапрос в выражении WHERE для фильтрации товаров, цена которых выше средней цены в их категории:

SELECT name,
       price
FROM products p1
WHERE price > (SELECT AVG(price)
               FROM products p2
               WHERE p2.category_code = p1.category_code);

Результат выглядит следующим образом:

    name    |  price
------------+---------
 Laptop     | 1200.00
 Smartphone |  800.00
 Jacket     |   60.00
 Desk Chair |  200.00
(4 rows)

В этом примере используется подзапрос с оператором IN для фильтрации товаров, которые были проданы начиная с 1 марта 2025 года.

SELECT name,
       price
FROM products
WHERE id IN (SELECT product_id
             FROM sales
             WHERE sales.date >= '2025-03-01');

Результат должен выглядеть следующим образом:

     name     |  price
--------------+---------
 Headphones   |  150.00
 Coffee Maker |   85.00
 T-Shirt      |   25.00
 Desk Chair   |  200.00
 Laptop       | 1200.00
(5 rows)

В следующем примере используется подзапрос с оператором IN для построчного сравнения по двум столбцам. Фильтруются товары, у которых пара (category_code, price) совпадает с парой, для которой цена меньше 100, а код категории — home:

SELECT name,
       price
FROM products
WHERE (category_code, price) IN (SELECT category_code,
                                        price
                                 FROM products
                                 WHERE price < 100
                                   AND category_code = 'home');

Результат следующий:

     name     | price
--------------+-------
 Coffee Maker | 85.00
 Blender      | 55.00
(2 rows)

Этот пример показывает, как с помощью оператора NOT IN и подзапроса найти товары, у которых нет продаж:

SELECT name,
       price
FROM products
WHERE id NOT IN (SELECT product_id FROM sales);

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

  name   | price
---------+-------
 Jacket  | 60.00
 Blender | 55.00
(2 rows)

В этом примере используется оператор ANY с подзапросом для фильтрации товаров, цена которых выше, чем хотя бы у одного товара в категории elec:

SELECT name,
       price
FROM products
WHERE price > ANY (SELECT price
                   FROM products
                   WHERE category_code = 'elec');

Вывод выглядит следующим образом:

    name    |  price
------------+---------
 Desk Chair |  200.00
 Smartphone |  800.00
 Laptop     | 1200.00
(3 rows)

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

SELECT name,
       price
FROM products
WHERE price > ALL (SELECT price
                   FROM products
                   WHERE category_code = 'home');

Результат выглядит следующим образом:

    name    |  price
------------+---------
 Laptop     | 1200.00
 Smartphone |  800.00
(2 rows)

В этом примере используется оператор EXISTS с коррелированным подзапросом для выбора товаров, у которых есть хотя бы одна продажа:

SELECT name,
       price
FROM products
WHERE EXISTS (SELECT 1
              FROM sales
              WHERE sales.product_id = products.id);

Вывод выглядит следующим образом:

     name     |  price
--------------+---------
 Coffee Maker |   85.00
 T-Shirt      |   25.00
 Laptop       | 1200.00
 Headphones   |  150.00
 Desk Chair   |  200.00
 Smartphone   |  800.00
(6 rows)

Чтобы найти товары, у которых нет продаж, используйте NOT EXISTS:

SELECT name,
       price
FROM products
WHERE NOT EXISTS (SELECT 1
                  FROM sales
                  WHERE sales.product_id = products.id);

Вывод выглядит так:

  name   | price
---------+-------
 Jacket  | 60.00
 Blender | 55.00
(2 rows)

В данном примере используется сравнение с подзапросом, возвращающим одну строку, чтобы найти товары с такими же значениями category_code и price, как у товара с названием T-Shirt:

SELECT name,
       price
FROM products
WHERE (category_code, price) = (SELECT category_code,
                                       price
                                FROM products
                                WHERE name = 'T-Shirt');

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

SELECT name,
       price
FROM products
WHERE ROW (category_code, price) = (SELECT category_code,
                                           price
                                    FROM products
                                    WHERE name = 'T-Shirt');

Подзапрос должен возвращать ровно одну строку с двумя столбцами:

  name   | price
---------+-------
 T-Shirt | 25.00
(1 row)

Этот пример демонстрирует использование подзапроса как производной таблицы в выражении FROM. Подзапрос выбирает товары с ценой меньше 100, а внешний запрос возвращает их идентификаторы и названия:

SELECT low_price_products.id,
       low_price_products.name
FROM (SELECT id, name
      FROM products
      WHERE price < 100) AS low_price_products;

Вывод выглядит следующим образом:

 id |     name
----+--------------
  3 | Coffee Maker
  4 | T-Shirt
  7 | Jacket
  8 | Blender
(4 rows)

В следующем примере используется подзапрос как производная таблица для расчета общего количества продаж для каждого товара. Затем внешний запрос соединяет этот итог с таблицей products, чтобы отобразить названия товаров вместе с их общими объемами продаж:

SELECT products.name,
       sales_summary.total_quantity
FROM (SELECT sales.product_id,
             SUM(sales.quantity) AS total_quantity
      FROM sales
      GROUP BY sales.product_id) AS sales_summary
         JOIN products ON products.id = sales_summary.product_id;

Результат должен выглядеть так:

     name     | total_quantity
--------------+----------------
 Coffee Maker |              2
 T-Shirt      |              7
 Laptop       |              3
 Desk Chair   |              3
 Smartphone   |              3
 Headphones   |              6
(6 rows)

Этот пример демонстрирует использование производной таблицы в JOIN. Во вложенном запросе вычисляется дата последней продажи для каждого товара, а внешний запрос объединяет этот результат с таблицей products, чтобы совместить сведения о товаре с информацией о его последней продаже:

SELECT products.name,
       recent_sales.last_sale_date
FROM products
         JOIN (SELECT sales.product_id,
                      MAX(sales.date) AS last_sale_date
               FROM sales
               GROUP BY sales.product_id) AS recent_sales ON products.id = recent_sales.product_id;

Результат должен выглядеть следующим образом:

     name     | last_sale_date
--------------+----------------
 Coffee Maker | 2025-03-01
 T-Shirt      | 2025-03-20
 Laptop       | 2025-03-15
 Desk Chair   | 2025-03-05
 Smartphone   | 2025-02-18
 Headphones   | 2025-03-10
(6 rows)

В этом примере в списке SELECT используется коррелированный скалярный подзапрос для подсчета общего количества продаж для каждого товара. Подзапрос ссылается на внешний запрос через products.id и выполняется один раз для каждого товара, возвращая количество продаж вместе с названием товара:

SELECT name,
       (SELECT COUNT(*)
        FROM sales
        WHERE sales.product_id = products.id) AS sales_count
FROM products;

В этом примере используются LEFT JOIN и GROUP BY для подсчета количества продаж по каждому товару, что дает тот же результат, что и коррелированный скалярный подзапрос выше:

SELECT products.name,
       COUNT(sales.product_id) AS sales_count
FROM products
         LEFT JOIN sales ON sales.product_id = products.id
GROUP BY products.name;

Результат должен выглядеть так:

     name     | sales_count
--------------+-------------
 Coffee Maker |           2
 T-Shirt      |           3
 Jacket       |           0
 Blender      |           0
 Headphones   |           3
 Laptop       |           3
 Desk Chair   |           2
 Smartphone   |           2
(8 rows)

JOIN

CTE

Подзапросы

Классификация подзапросов

Структура результата подзапроса

Коррелированность

Расположение в основном SQL-запросе

Примеры использования

Скалярный подзапрос

Коррелированный подзапрос

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

Оператор IN

Оператор NOT IN

Оператор ANY

Оператор ALL

Оператор EXISTS

Сравнение единичных строк

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

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

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