Подзапросы
Обзор
Подзапрос 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-безопасное равно. Этот оператор выполняет сравнение на равенство, но возвращает |
Например, следующий запрос отображает книги с ценой выше средней:
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_подзапрос> не возвращает ни одной строки. Следующий запрос отбирает книги американских авторов:
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