Subqueries
Overview
A PostgreSQL subquery is an SQL query nested inside a parent query. A subquery can be nested inside a SELECT, INSERT, UPDATE, DELETE, SET, or DO statement or inside another subquery. A subquery is usually added within the WHERE clause. In the WHERE clause, you can use single-row comparison operators (>, <, =, and others) or multiple-row expressions (IN, ANY, ALL, and others). A subquery must be enclosed in parentheses.
For example, we have the following table 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
The query below displays all books of the same genre as a genre of the book with id = 1. A subquery SELECT genre FROM books WHERE id = 1 determines the genre.
SELECT id, title, genre
FROM books
WHERE genre = (SELECT genre FROM books WHERE id = 1);The result:
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
A subquery is executed before its parent query, and the subquery result is passed to the parent query. In this example, the subquery returns novel, and the parent query returns books of the novel genre. The subquery from the example is a scalar one.
Scalar subquery
A scalar subquery is a subquery that selects only one column or expression and returns one row, a single scalar value.
In the example below, the subquery SELECT MAX(total) FROM orders WHERE customer_id = 17 selects the maximum value of the total field for the customer with id = 17:
INSERT INTO order_statistic (customer_id, max_sum) VALUES (
17,
(SELECT MAX(total) FROM orders WHERE customer_id = 17)
)
RETURNING *;The result:
customer_id | max_sum
-------------+---------
17 | 5000.00
Subquery with comparison operators
You can use subquery before or after comparison operators. This type of subqueries can return at most one value. The value can be the result of an arithmetic expression or a function. PostgreSQL compares the subquery result value with the value on the other side of the comparison operator. You can use the following comparison operators:
| Operator | Description |
|---|---|
= |
Equal to |
> |
Greater than |
>= |
Greater than or equal to |
< |
Less than |
<= |
Less than or equal to |
!= |
Not equal to |
<> |
Not equal to |
<=> |
Null-safe equal to. This operator performs an equality comparison, but returns |
The following query displays books with the price higher than the average price:
SELECT id, title, price
FROM books
WHERE price >
(SELECT AVG(price) FROM books);The result:
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
Subquery with EXISTS
The EXISTS expression syntax:
EXISTS (<subquery>)The EXISTS argument is a subquery or a SELECT statement. The subquery is executed to determine whether it returns any rows. If it returns at least one row, the result of EXISTS is true. If subquery returns no rows, the result of EXISTS is false.
The subquery can refer to variables from the parent query.
The subquery is executed until at least one row is returned. Since the result depends on whether any rows are returned, the output list is not significant. A common coding convention is to write all EXISTS tests in the form EXISTS (SELECT 1 WHERE …).
For example, we have the table authors in addition to table books mentioned above:
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
Execute the following query:
SELECT id, title, author_id
FROM books b
WHERE EXISTS (SELECT 1 FROM authors WHERE id = b.author_id);The result contains books whose authors are in the authors table:
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
Subquery with IN
The IN expression syntax:
<in_expression> IN (<in_subquery>)in_subquery must return one column. The in_expression is evaluated and compared against each row of the subquery result. The IN result is true if any equal subquery row is found. The result is false if no equal row is found, or in_subquery returns no rows. The following query selects books of American authors:
SELECT id, title FROM books
WHERE author_id IN
(SELECT id FROM authors WHERE country='USA');The result:
id | title ----+----------------------- 3 | To Kill a Mockingbird 4 | The Great Gatsby 9 | Hyperion
Note, if in_expression returns null or if there are no equal subquery values and at least one subquery row returns null, the IN result is null, not false.
The left part of the IN expression can be a row constructor:
<row_constructor> IN (<in_subquery>)in_subquery must return the same number of columns as the row_constructor expression contains.
Example:
SELECT ROW( books.id, books.title) FROM books
WHERE author_id IN
(SELECT id FROM authors WHERE country='USA');The result:
row ----------------------------- (3,"To Kill a Mockingbird") (4,"The Great Gatsby") (9,Hyperion)
IN is equivalent to = ANY.
Subquery with NOT IN
The NOT IN expression syntax:
<not_in_expression> NOT IN (<not_in_subquery>)not_in_subquery must return one column. The not_in_expression is evaluated and compared to each row of the subquery result. The NOT IN result is true if only unique subquery rows are found, or not_in_subquery returns no rows. The result is false if any equal row is found.
The following query selects books of non-American authors:
SELECT id, title FROM books
WHERE author_id NOT IN
(SELECT id FROM authors WHERE country='USA');The result:
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 is equivalent to <> ALL.
The left part of the NOT IN expression can be a row constructor:
<row_constructor> NOT IN (<not_in_subquery>)not_in_subquery must return the same number of columns as the row_constructor expression contains.
Example:
SELECT ROW( books.id, books.title) FROM books
WHERE author_id NOT IN
(SELECT id FROM authors WHERE country='USA');The result:
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")
Subquery with ANY/SOME
SOME is a synonym for ANY. The ANY/SOME expression syntax:
<expression> <operator> ANY (<subquery>)
<expression> <operator> SOME (<subquery>)subquery must return one column. The expression is evaluated and compared to each row of the subquery result using operator. operator must return a boolean result. The result of ANY is true if any true result is obtained. The result is false if no true result is found, or subquery returns no rows.
= ANY is equivalent to IN.
Note, if operator does not return true and at least one subquery row returns null, the ANI result is null, not false.
Rewrite the example with IN as follows:
SELECT id, title FROM books
WHERE author_id = ANY
(SELECT id FROM authors
WHERE country='USA');The result is the same:
id | title ----+----------------------- 3 | To Kill a Mockingbird 4 | The Great Gatsby 9 | Hyperion
The left part of the ANY/SOME expression can be a row constructor:
<row_constructor> <operator> ANY (<subquery>)
<row_constructor> <operator> SOME (<subquery>)subquery must return the same number of columns as the row_constructor expression contains.
Example:
SELECT ROW( books.id, books.title) FROM books
WHERE author_id = ANY
(SELECT id FROM authors WHERE country='USA');The result:
row ----------------------------- (3,"To Kill a Mockingbird") (4,"The Great Gatsby") (9,Hyperion)
Subquery with ALL
<all_expression> <operator> ALL (<all_subquery>)all_subquery must return one column. The all_expression is evaluated and compared to each row of the subquery result using operator. operator must return a boolean result. The ALL result is true if all rows returns true or all_subquery returns no rows. The result is false if any false result is found. The result is null if no comparison with a subquery row returns false, and at least one comparison returns null.
<> ALL is equivalent to NOT IN.
Rewrite the example with NOT IN as follows:
SELECT id, title FROM books
WHERE author_id <> ALL
(SELECT id FROM authors WHERE country='USA');The result is the same:
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
The left part of the ALL expression can be a row constructor:
<row_constructor> <operator> ALL (<all_subquery>)subquery must return the same number of columns as the row_constructor expression contains.
Example:
SELECT ROW( books.id, books.title) FROM books
WHERE author_id <> ALL
(SELECT id FROM authors WHERE country='USA');The result:
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")
Single-row comparison
The single-row comparison has the following syntax:
<row_constructor> <operator> (<subquery>)subquery must return the same number of columns as the row_constructor expression contains. subquery cannot return more than one row. If it returns no rows, the result is null. You can use = , >, <, >=, <=, <>, !=, <=> comparison operators. The row_constructor is evaluated and compared to the single subquery result row using operator.
Example:
SELECT last_name FROM employees
WHERE ROW(department_id, manager_id) =
(SELECT department_id, manager_id FROM departments
WHERE location = "Moscow");The result:
last_name ------------ Ivanov
Subquery in the FROM clause
An SQL-statement with subquery in the FROM clause has the following syntax:
SELECT <expression> FROM (<subquery>) AS <alias>Every table in the FROM clause must have an alias. All columns in the subquery SELECT list must have unique names.
The following query shows books published after 1900 with a percentage of total sales:
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;The result:
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