Подзапросы

Обзор

Подзапрос PostgreSQL — это SQL-запрос, вложенный в родительский запрос. Подзапрос может быть вложен в оператор SELECT, INSERT, UPDATE, DELETE, SET, DO или в другой подзапрос. Подзапрос обычно добавляется в предложение WHERE. В предложении WHERE вы можете использовать однострочные операторы сравнения (>, <, = и др.) или многострочные выражения (IN, ANY, ALL и др.). Подзапрос должен быть заключен в круглые скобки.

Например, имеется таблица books:

 id |                title                | author_id | public_year |  genre  | evaluation | price | total_sales
----+-------------------------------------+-----------+-------------+---------+------------+-------+-------------
  1 | Mrs. Dalloway                       |         1 |        1925 | novel   |       7.82 |   360 |     6212880
  2 | To the Lighthouse                   |         1 |        1927 | novel   |       8.45 |   440 |     7216000
  3 | To Kill a Mockingbird               |         2 |        1960 | novel   |       7.48 |   750 |    11574000
  4 | The Great Gatsby                    |         3 |        1925 | novel   |       9.23 |   900 |    11110500
  5 | The Lord of the Rings               |         4 |        1955 | fantasy |       9.49 |  1200 |     5472000
  6 | 1984                                |         5 |        1949 | sci-fi  |       8.17 |   520 |     9642880
  7 | The Hobbit, or There and Back Again |         4 |        1937 | fantasy |       9.32 |  1100 |    19679000
  8 | War and Peace                       |         6 |        1869 | novel   |       9.69 |  1500 |    32548500
  9 | Hyperion                            |         7 |        1989 | sci-fi  |       9.46 |   610 |     8411290
 10 | The Time Machine                    |         8 |        1895 | sci-fi  |       8.12 |   450 |     6444450

Приведенный ниже запрос отображает все книги того же жанра, что и жанр книги с id = 1. Подзапрос SELECT genre FROM books WHERE id = 1 определяет жанр.

SELECT id, title, genre
FROM books
WHERE genre = (SELECT genre FROM books WHERE id = 1);

Результат:

 id |         title         | genre
----+-----------------------+-------
  1 | Mrs. Dalloway         | novel
  2 | To the Lighthouse     | novel
  3 | To Kill a Mockingbird | novel
  4 | The Great Gatsby      | novel
  8 | War and Peace         | novel

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

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

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

В приведенном ниже примере подзапрос SELECT MAX(total) FROM orders WHERE customer_id = 17 выбирает максимальное значение поля total для клиента с id = 17:

INSERT INTO order_statistic (customer_id, max_sum) VALUES (
   17,
   (SELECT MAX(total) FROM orders WHERE customer_id = 17)
)
RETURNING *;

Результат:

 customer_id | max_sum
-------------+---------
          17 | 5000.00

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

Можно использовать подзапросы до или после операторов сравнения. Этот тип подзапросов должен возвращать не более одного значения. Значение может быть результатом арифметического выражения или функции. PostgreSQL сравнивает значение результата подзапроса со значением на другой стороне оператора сравнения. Вы можете использовать следующие операторы сравнения:

Оператор Описание

=

Равно

>

Больше

>=

Больше или равно

<

Меньше

<=

Меньше или равно

!=

Не равно

<>

Не равно

<=>

Null-безопасное равно. Этот оператор выполняет сравнение на равенство, но возвращает 1, а не null, если оба операнда null, и 0, а не null, если один операнд null

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

SELECT id, title, price
FROM books
WHERE price >
(SELECT AVG(price) FROM books);

Результат:

 id |                title                | price
----+-------------------------------------+-------
  4 | The Great Gatsby                    |   900
  5 | The Lord of the Rings               |  1200
  7 | The Hobbit, or There and Back Again |  1100
  8 | War and Peace                       |  1500

Подзапросы с EXISTS

Синтаксис выражения EXISTS:

EXISTS (<подзапрос>)

Аргумент EXISTS должен быть подзапросом или оператором SELECT. Подзапрос выполняется, чтобы определить, возвращает ли он какие-либо строки. Если он возвращает хотя бы одну строку, результатом EXISTS является true. Если подзапрос не возвращает строк, результатом EXISTS будет false.

Подзапрос может ссылаться на переменные из родительского запроса.

Подзапрос выполняется до тех пор, пока не будет возвращена хотя бы одна строка. Поскольку результат зависит от того, возвращаются ли какие-либо строки и их значения не важны, общепринятым правилом кодирования является запись EXISTS в форме EXISTS (SELECT 1 WHERE…​).

Например, у нас есть таблица authors в дополнение к таблице books, упомянутой выше:

 id |        name         |    country
----+---------------------+---------------
  2 | Harper Lee          | USA
  3 | F. Scott Fitzgerald | USA
  4 | J.R.R. Tolkien      | Great Britain
  5 | George Orwell       | Great Britain
  6 | Leo Tolstoy         | Russia
  7 | Dan Simmons         | USA
  8 | Herbert Wells       | Great Britain

Выполним следующий запрос:

SELECT id, title, author_id
FROM books b
WHERE EXISTS (SELECT 1 FROM authors WHERE id = b.author_id);

Результат содержит книги, авторы которых есть в таблице authors:

 id |                title                | author_id
----+-------------------------------------+-----------
  4 | The Great Gatsby                    |         3
  6 | 1984                                |         5
  7 | The Hobbit, or There and Back Again |         4
  5 | The Lord of the Rings               |         4
  8 | War and Peace                       |         6
  3 | To Kill a Mockingbird               |         2
  9 | Hyperion                            |         7
 10 | The Time Machine                    |         8

Подзапросы с IN

Синтаксис выражения IN:

<in_выражение> IN (<in_подзапрос>)

in_подзапрос должен вернуть один столбец. in_выражение вычисляется и сравнивается с каждой строкой результата подзапроса. Результат IN равен true, если найдена равная строка в результатах подзапроса. Результат false, если не найдено ни одной равной строки, или in_subquery не возвращает ни одной строки. Следующий запрос отбирает книги американских авторов:

SELECT id, title FROM books
WHERE author_id IN
    (SELECT id FROM authors WHERE country='USA');

Результат:

 id |         title
----+-----------------------
  3 | To Kill a Mockingbird
  4 | The Great Gatsby
  9 | Hyperion

Обратите внимание, что если in_выражение возвращает значение null или нет одинаковых значений подзапроса и по крайней мере одна строка подзапроса возвращает значение null, результатом IN является значение null, а не false.

Левая часть выражения IN может быть конструктором строки:

<конструктор_строки> IN (<in_подзапрос>)

in_подзапрос должен возвращать такое же количество столбцов, которое содержится в выражении конструктор_строки.

Пример:

SELECT ROW( books.id, books.title) FROM books
WHERE author_id IN
  (SELECT id FROM authors WHERE country='USA');

Результат:

             row
-----------------------------
 (3,"To Kill a Mockingbird")
 (4,"The Great Gatsby")
 (9,Hyperion)

IN эквивалентно = ANY.

Подзапросы с NOT IN

Синтаксис выражения NOT IN:

<not_in_выражение> NOT IN (<not_in_подзапрос>)

not_in_выражение должно возвращать один столбец. not_in_выражение вычисляется и сравнивается с каждой строкой результата подзапроса. Результат NOT IN равен true, если найдены только уникальные строки подзапроса, или not_in_подзапрос не возвращает строк. Результат false, если найдена любая равная строка.

Следующий запрос отбирает книги неамериканских авторов:

SELECT id, title FROM books
WHERE author_id NOT IN
  (SELECT id FROM authors WHERE country='USA');

Результат:

 id |                title
----+-------------------------------------
  1 | Mrs. Dalloway
  2 | To the Lighthouse
  5 | The Lord of the Rings
  7 | The Hobbit, or There and Back Again
  8 | War and Peace
  6 | 1984
 10 | The Time Machine

NOT IN эквивалентно <> ALL.

Левая часть выражения NOT IN может быть конструктором строки:

<конструктор_строки> NOT IN (<not_in_подзапрос>)

not_in_подзапрос должен возвращать такое же количество столбцов, которое содержится в выражении конструктор_строки.

Пример:

SELECT ROW( books.id, books.title) FROM books
WHERE author_id NOT IN
  (SELECT id FROM authors WHERE country='USA');

Результат:

                    row
-------------------------------------------
 (1,"Mrs. Dalloway")
 (2,"To the Lighthouse")
 (5,"The Lord of the Rings")
 (7,"The Hobbit, or There and Back Again")
 (8,"War and Peace")
 (6,1984)
 (10,"The Time Machine")

Подзапросы с ANY/SOME

SOME — это синоним ANY. Синтаксис выражения ANY/SOME:

<выражение> <оператор> ANY (<подзапрос>)
<выражение> <оператор> SOME (<подзапрос>)

подзапрос должен возвращать один столбец. выражение вычисляется и сравнивается с каждой строкой результата подзапроса с использованием оператора. оператор должен возвращать логический результат. Результат ANY будет true, если получен какой-либо true результат. Результат false, если результат true не найден, или подзапрос не возвращает строк.

= ANY эквивалентно IN.

Обратите внимание, что если оператор не возвращает true и хотя бы одна строка подзапроса возвращает null, результатом ANI будет null, а не false.

Перепишем пример с IN следующим образом:

SELECT id, title FROM books
  WHERE author_id = ANY
  (SELECT id FROM authors
      WHERE country='USA');

Результат будет такой же:

 id |         title
----+-----------------------
  3 | To Kill a Mockingbird
  4 | The Great Gatsby
  9 | Hyperion

Левая часть выражения ANY/SOME может быть конструктором строки:

<конструктор_строки> <оператор> ANY (<подзапрос>)
<конструктор_строки> <оператор> SOME (<подзапрос>)

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

Пример:

SELECT ROW( books.id, books.title) FROM books
WHERE author_id = ANY
  (SELECT id FROM authors WHERE country='USA');

Результат:

             row
-----------------------------
 (3,"To Kill a Mockingbird")
 (4,"The Great Gatsby")
 (9,Hyperion)

Подзапросы с ALL

Синтаксис выражения ALL:

<all_выражение> <оператор> ALL (<all_подзапрос>)

all_подзапрос должен возвращать один столбец. all_выражение вычисляется и сравнивается с каждой строкой результата подзапроса с использованием оператора. оператор должен возвращать логический результат. Результат ALL равен true, если все строки возвращают true или all_подзапрос не возвращает ни одной строки. Результат false, если найден какой-либо false результат. Результатом является null, если ни одно сравнение со строкой подзапроса не возвращает false, и хотя бы одно сравнение возвращает null.

<> ALL эквивалентно NOT IN.

Перепишите пример с NOT IN следующим образом:

SELECT id, title FROM books
WHERE author_id <> ALL
 (SELECT id FROM authors WHERE country='USA');

Результат будет такой же:

 id |                title
----+-------------------------------------
  1 | Mrs. Dalloway
  2 | To the Lighthouse
  5 | The Lord of the Rings
  7 | The Hobbit, or There and Back Again
  8 | War and Peace
  6 | 1984
 10 | The Time Machine

Левая часть выражения ALL может быть конструктором строки:

<конструктор_строки> <оператор> ALL (<all_подзапрос>)

all_подзапрос должен возвращать такое же количество столбцов, которое содержится в выражении конструктор_строки.

Пример:

SELECT ROW( books.id, books.title) FROM books
WHERE author_id <> ALL
  (SELECT id FROM authors WHERE country='USA');

Результат:

                    row
-------------------------------------------
 (1,"Mrs. Dalloway")
 (2,"To the Lighthouse")
 (5,"The Lord of the Rings")
 (7,"The Hobbit, or There and Back Again")
 (8,"War and Peace")
 (6,1984)
 (10,"The Time Machine")

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

Сравнение единичных строк имеет следующий синтаксис:

<конструктор_строки> <оператор> (<подзапрос>)

подзапрос должен возвращать такое же количество столбцов, которое содержится в выражении конструктор_строки. подзапрос не может возвращать более одной строки. Если он не возвращает строк, результат null. Можно использовать следующие операторы сравнения: = , >, <, >=, <=, <>, !=, <=>. конструктор_строки вычисляется и сравнивается с одиночной строкой результата подзапроса с использованием оператора.

Пример:

SELECT last_name FROM employees
  WHERE ROW(department_id, manager_id) =
    (SELECT department_id, manager_id FROM departments
      WHERE location  = "Moscow");

Результат:

 last_name
------------
 Ivanov

Подзапросы в выражениях FROM

SQL-выражение с подзапросом в FROM имеет следующий синтаксис:

SELECT <выражение> FROM (<подзапрос>) AS <псевдоним>

Каждая таблица в выражении FROM должна иметь псевдоним. Все столбцы в списке SELECT подзапроса должны иметь уникальные имена.

Следующий запрос показывает книги, изданные после 1900 года, и их процент от общего объема продаж:

SELECT book_id, book_title, percentage
FROM
  (SELECT id AS book_id, title AS book_title, public_year AS year, ROUND(total_sales/118311500*100,2) AS percentage FROM books) AS b
WHERE year > 1900;

Результат:

 book_id |             book_title              | percentage
---------+-------------------------------------+------------
       1 | Mrs. Dalloway                       |       5.25
       2 | To the Lighthouse                   |       6.10
       3 | To Kill a Mockingbird               |       9.78
       4 | The Great Gatsby                    |       9.39
       5 | The Lord of the Rings               |       4.63
       6 | 1984                                |       8.15
       7 | The Hobbit, or There and Back Again |      16.63
       9 | Hyperion                            |       7.11
Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней