Postgresql Sub Queries

PostgreSQL Subqueries

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

The result of a SELECT statement's query can serve as input for another statement.

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

Here are a few rules that subqueries must follow:

Subqueries must be enclosed in parentheses.
A subquery in a SELECT clause can only have one column, unless the main query has multiple columns to compare with the columns selected by 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, which functions the same 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, but BETWEEN can be used within a subquery.

Using Subqueries in SELECT Statements

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

Example

Create the COMPANY table (Download COMPANY SQL file), with the following data:

tutorialdb# 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:

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

The result will be as follows:

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

Using Subqueries in INSERT Statements

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 number functions.

The basic syntax is as follows:

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

Example

Assume the COMPANY_BKP table has a structure similar to the COMPANY table and can be created using the same CREATE TABLE statement, just with the table name changed to COMPANY_BKP. Now, let's copy the entire COMPANY table to COMPANY_BKP with the following syntax:

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

Using Subqueries in UPDATE Statements

Subqueries can be used with UPDATE statements. When using a subquery 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  (SELECT COLUMN_NAME FROM TABLE_NAME) [ WHERE) ]

Example

Assume 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 where AGE is greater than 27 to 0.50 times the original value:

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

This will affect two rows. 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)

Using Subqueries in DELETE Statements

Subqueries can be used with DELETE statements, just like the other statements mentioned above.

The basic syntax is as follows:

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

Example

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

The following example deletes all customer records from the COMPANY table where AGE is greater than or equal to 27:

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

This will affect two rows. 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)

YouTip