PostgreSQL Subqueries

Subqueries, also known as inner queries or nested queries, refer to the embedding of query statements in the WHERE clause of a PostgreSQL query.

The query results of a SELECT statement can serve as input values for another statement.

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

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 for comparison with the selected columns of the subquery.

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

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

• The BETWEEN operator cannot be used with subqueries, but BETWEEN can be used within a subquery.

The subquery used in the SELECT statement is

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])

Online Example

Create COMPANY table (Download COMPANY SQL file ），the data content is as follows：

w3codeboxdb# 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 the subquery in the SELECT statement:

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

The result is as follows:

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

The subquery used in the INSERT statement is

The subquery can also be used with the INSERT statement. 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 ]

Online Example

Assuming that the structure of COMPANY_BKP is similar to the COMPANY table and can be created using the same CREATE TABLE statement, just change the table name to COMPANY_BKP. Now, let's copy the entire COMPANY table to COMPANY_BKP, the syntax is as follows:

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

Subqueries in the UPDATE statement use

Subqueries can be combined with UPDATE statements. When using subqueries with UPDATE statements, 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)　]

Online Example

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

The following example updates the SALARY of all customers in the COMPANY table with AGE greater than 27 the customer's SALARY updated to 0 times the original value.50 times:

w3codeboxdb=# 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 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)

Subqueries in the DELETE statement use

Subqueries can be combined 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)　]

Online Example

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

The following example deletes all records with AGE greater than or equal to 27 customer records:

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

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