PostgreSQL Subqueries

A subquery, also called an inner query or nested query, refers to a query statement embedded in the WHERE clause of a PostgreSQL query.

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

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

The following are several 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 to compare with the selected columns of the subquery.

• ORDER BY cannot be used in a subquery, although the main query can use ORDER BY. GROUP BY can be used in a subquery, and it serves the same function as ORDER BY.

• If a subquery returns more than one row, it can only be used with multi-value operators, such as the IN operator.

• The BETWEEN operator cannot be used with a subquery; however, BETWEEN can be used within a subquery.

Subquery usage in SELECT statement

Subqueries are commonly 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])

Example

Create the COMPANY table (Download COMPANY SQL file), and the data content is as follows:

exampledb# 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 a SELECT statement:

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

The result obtained is as follows:

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

---

Subquery usage in INSERT statement

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

The data selected in the subquery can be modified using any character, date, or numeric functions.

The basic syntax is as follows:

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

Example

Assume that COMPANY_BKP has a similar structure to the COMPANY table and can be created using the same CREATE TABLE, except that the table name is changed to COMPANY_BKP. Now copy the entire COMPANY table to COMPANY_BKP, the syntax is as follows:

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

---

Subquery usage in UPDATE statement

Subqueries can be used in conjunction with UPDATE statements. When a subquery is used with an UPDATE statement, 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

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

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

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

This will affect two rows, and the final records in the COMPANY 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
  1 | Paul  |  32 | California  |  10000
  5 | David |  27 | Texas       |  42500
(7 rows)

---

Subquery usage in DELETE statement

Subqueries can be used in conjunction with DELETE statements, 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

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

The following example deletes the records of all customers in the COMPANY table whose AGE is greater than or equal to 27:

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

This will affect two rows, and the final records in the COMPANY 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)

Other extensions