Postgresql 简明教程

PostgreSQL - Sub Queries

子查询或内部查询或嵌套查询是另一个 PostgreSQL 查询中的查询，并内嵌在 WHERE 子句中。

A subquery or Inner query or Nested query is a query within another PostgreSQL query and embedded within the WHERE clause.

一个子查询用于返回在主查询中的某个条件中将被用于进一步限制要检索的数据的数据。

A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

子查询可用于 SELECT、INSERT、UPDATE 和 DELETE 语句以及以下运算符 =、<、>、>=、⇐、IN 等。

Subqueries can be used with the SELECT, INSERT, UPDATE and DELETE statements along with the operators like =, <, >, >=, ⇐, IN, etc.

有一些规则，子查询必须遵守 −

There are a few rules that subqueries must follow −

Subqueries must be enclosed within parentheses.
A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns.
An ORDER BY cannot be used in a subquery, although the main query can use an ORDER BY. The GROUP BY can be used to perform the same function as the ORDER BY in a subquery.
Subqueries that return more than one row can only be used with multiple value operators, such as the IN, EXISTS, NOT IN, ANY/SOME, ALL operator.
The BETWEEN operator cannot be used with a subquery; however, the BETWEEN can be used within the subquery.

Subqueries with the SELECT Statement

子查询最常与SELECT语句一起使用。基本语法如下所示：

Subqueries are most frequently used with the SELECT statement. 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])

Example

考虑带有以下记录的 COMPANY 表 −

Consider the COMPANY table having the following records −

 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)

现在，让我们使用 SELECT 语句检查以下子查询−

Now, let us check the following sub-query with SELECT statement −

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

这将产生以下结果 -

This would produce the following result −

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

Subqueries with the INSERT Statement

子查询也可以用于 INSERT 语句。INSERT 语句使用子查询返回的数据插入至另一张表中。子查询中的所选数据可以使用字符、日期或数字函数之一修改。

Subqueries also can be used with INSERT statements. The INSERT statement uses the data returned from the subquery to insert into another table. The selected data in the subquery can be modified with any of the character, date, or number functions.

基本语法如下 −

The basic syntax is as follows −

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

Example

考虑一张 COMPANY_BKP 表，它具有与 COMPANY 表类似的结构，且可以使用相同的 CREATE TABLE 来创建，使用 COMPANY_BKP 作为表名。现在，要将完整的 COMPANY 表复制到 COMPANY_BKP 中，以下是语法 −

Consider a table COMPANY_BKP, with similar structure as COMPANY table and can be created using the same CREATE TABLE using COMPANY_BKP as the table name. Now, to copy complete COMPANY table into COMPANY_BKP, following is the syntax −

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

Subqueries with the UPDATE Statement

子查询可以与 UPDATE 语句联合使用。在使用 UPDATE 语句与子查询时，可以更新表中的单个或多个列。

The subquery can be used in conjunction with the UPDATE statement. Either single or multiple columns in a table can be updated when using a subquery with the UPDATE statement.

基本语法如下 −

The basic syntax is as follows −

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

Example

假设我们有可用的 COMPANY_BKP 表，它是 COMPANY 表的备份。

Assuming, we have COMPANY_BKP table available, which is backup of the COMPANY table.

以下示例将 COMPANY 表中所有年龄大于或等于 27 的客户的 SALARY 更新为 0.50 倍 −

The following example updates SALARY by 0.50 times in the COMPANY table for all the customers, whose AGE is greater than or equal to 27 −

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

这将影响两行，最终 COMPANY 表将具有以下记录 -

This would affect two rows and finally the COMPANY table would have the following records −

 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
  1 | Paul  |  32 | California  |  10000
  5 | David |  27 | Texas       |  42500
(7 rows)

Subqueries with the DELETE Statement

与上面提到的其他任何语句一样，子查询可与 DELETE 语句一起使用。

The subquery can be used in conjunction with the DELETE statement like with any 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

假设我们有 COMPANY_BKP 表，它是 COMPANY 表的备份。

Assuming, we have COMPANY_BKP table available, which is a backup of the COMPANY table.

以下示例将删除 COMPANY 表中所有客户的记录，这些客户的年龄大于或等于 27 -

The following example deletes records from the COMPANY table for all the customers, whose AGE is greater than or equal to 27 −

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

这将影响两行，最终 COMPANY 表将具有以下记录 -

This would affect two rows and finally the COMPANY table would have the following records −

 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)