5.40. PostgreSQL subquery

Subquery, or internal query, or nested query, refers to embedding query statements in the WHERE clause of a PostgreSQL query.

The query result of one SELECT statement can be used as the input value of another statement.

Subqueries can be used with SELECT, INSERT, UPDATE, and DELETE statements, and operators such as =, <, >, > =, < =, IN, BETWEEN, and so on.

Here are a few rules that subqueries must follow:

Subqueries must be enclosed in parentheses.
A subquery can have only one column in the SELECT clause, unless there are multiple columns in the main query, compared to the selected columns of the subquery.
ORDER BY cannot be used in subqueries, although the main query can use ORDER BY. You can use GROUP BY in a subquery with the same functionality as ORDER BY.
The subquery returns more than one row and can only be used with multivalued operators, such as the IN operator.
The BETWEEN operator cannot be used with a subquery, but BETWEEN can be used within a subquery.

5.40.1. Subqueries in SELECT statements use the ¶

Subqueries are usually used with SELECT statements. The basic syntax is as follows:

SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
      (SELECT column_name [, column_name ]
      FROM table1 [, table2 ]
      [WHERE])

5.40.2. Example ¶

Create the COMPANY table ( 下载 COMPANY SQL 文件 ), the data are as follows:

runoobdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Now, let’s use a subquery in the SELECT statement:

runoobdb=# SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY  WHERE SALARY > 45000) ;

The results are as follows:

 id | name  | age |  address    | salary
----+-------+-----+-------------+--------
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
(2 rows)

5.40.3. Subqueries in INSERT statements use the ¶

Subqueries can also be used with INSERT statements. The INSERT statement inserts the data returned by the subquery into another table.

The data selected in the subquery can be modified with any character, date, or number function.

The basic syntax is as follows:

INSERT INTO table_name [ (column1 [, column2 ]) ]
   SELECT [ *|column1 [, column2 ] ]
   FROM table1 [, table2 ]
   [ WHERE VALUE OPERATOR ]

Example ¶

Hypothetical COMPANY_BKP The structure of the COMPANY Tables are similar, and you can use the same CREATE TABLE To create, but the table name is changed to COMPANY_BKP . Now put the whole COMPANY The table is copied to COMPANY_BKP The syntax is as follows:

runoobdb=# INSERT INTO COMPANY_BKP SELECT * FROM COMPANY  WHERE ID IN (SELECT ID FROM COMPANY) ;

5.40.4. Subqueries in UPDATE statements use the ¶

A subquery can be associated with the UPDATE Statement is used in combination. When passed UPDATE Statement uses a subquery, one or more columns in the table are updated.

The basic syntax is as follows:

UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]

Example ¶

Hypothetically, we have COMPANY_BKP Table, yes COMPANY A backup of the table.

The following example sets the COMPANY For all customers whose AGE is greater than 27 in the table SALARY Updated to 0.50 times the original:

runoobdb=# UPDATE COMPANY SET SALARY = SALARY * 0.50 WHERE AGE IN (SELECT AGE FROM COMPANY_BKP WHERE AGE >= 27 );

This will affect two lines, and finally COMPANY The records in the table are as follows:

 id | name  | age | address     | salary
----+-------+-----+-------------+--------
| Allen |  25 | Texas       |  15000
| Teddy |  23 | Norway      |  20000
| Mark  |  25 | Rich-Mond   |  65000
| Kim   |  22 | South-Hall  |  45000
| James |  24 | Houston     |  10000
| Paul  |  32 | California  |  10000
| David |  27 | Texas       |  42500
(7 rows)

5.40.5. Subqueries in DELETE statements use the ¶

A subquery can be associated with the DELETE Statement, just like the other statements mentioned above.

The basic syntax is as follows:

DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]

Example ¶

Hypothetically, we have COMPANY_BKP Table, yes COMPANY A backup of the table.

The following example is deleted COMPANY All in the table AGE Customer records greater than or equal to 27:

runoobdb=# DELETE FROM COMPANY WHERE AGE IN (SELECT AGE FROM COMPANY_BKP WHERE AGE > 27 );

This will affect two lines, and finally COMPANY The records in the table are as follows:

 id | name  | age | address     | salary
----+-------+-----+-------------+--------
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
  6 | Kim   |  22 | South-Hall  |  45000
  7 | James |  24 | Houston     |  10000
  5 | David |  27 | Texas       |  42500
(6 rows)