A subquery or Inner inquiry or Nested question is an inquiry inside another PostgreSQL question and implanted inside the WHERE statement.
A subquery is utilized to restore information that will be utilized in the primary question as a condition to additionally confine the information to be recovered.
Subqueries can be utilized with the SELECT, INSERT, UPDATE and DELETE articulations alongside the administrators like =, <, >, >=, <=, IN, and so forth
There are a couple of decides that subqueries should follow −
- Subqueries should be encased inside enclosures.
- A subquery can have just a single segment in the SELECT proviso, except if different sections are in the principle question for the subquery to look at its chose segments.
- An ORDER BY can't be utilized in a subquery, albeit the principle inquiry can utilize an ORDER BY. The GROUP BY can be utilized to play out a similar capacity as the ORDER BY in a subquery.
- Subqueries that return beyond what one line must be utilized with various worth administrators, for example, the IN, EXISTS, NOT IN, ANY/SOME, ALL administrator.
- The BETWEEN administrator can't be utilized with a subquery; notwithstanding, the BETWEEN can be utilized inside the subquery.
Subqueries with the SELECT Statement
Subqueries are most often utilized with the SELECT assertion. The essential sentence structure is as per the following −
SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name OPERATOR
(SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE])
Example
Consider the COMPANY table having the accompanying 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)
Presently, let us check the accompanying sub-inquiry with SELECT articulation −
testdb=# SELECT *
FROM COMPANY
WHERE ID IN (SELECT ID
FROM COMPANY
WHERE SALARY > 45000) ;
This would deliver the accompanying outcome −
id | name | age | address | salary
----+-------+-----+-------------+--------
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
(2 rows)
Subqueries with the INSERT Statement
Subqueries additionally can be utilized with INSERT explanations. The INSERT explanation utilizes the information got back from the subquery to embed into another table. The chose information in the subquery can be adjusted with any of the character, date, or number capacities.
The fundamental linguistic structure is as per the following −
INSERT INTO table_name [ (column1 [, column2 ]) ]
SELECT [ *|column1 [, column2 ] ]
FROM table1 [, table2 ]
[ WHERE VALUE OPERATOR ]
Example
Consider a table COMPANY_BKP, with comparative design as COMPANY table and can be made utilizing a similar CREATE TABLE utilizing COMPANY_BKP as the table name. Presently, to duplicate total COMPANY table into COMPANY_BKP, following is the grammar −
testdb=# INSERT INTO COMPANY_BKP
SELECT * FROM COMPANY
WHERE ID IN (SELECT ID
FROM COMPANY) ;
Subqueries with the UPDATE Statement
The subquery can be utilized related to the UPDATE articulation. Either single or different segments in a table can be refreshed when utilizing a subquery with the UPDATE explanation.
The fundamental linguistic structure is as per the following −
UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]
Example
Expecting, we have COMPANY_BKP table accessible, which is reinforcement of the COMPANY table.
The accompanying model updates SALARY by 0.50 occasions in the COMPANY table for all the clients, whose AGE is more prominent than or equivalent to 27 −
testdb=# UPDATE COMPANY
SET SALARY = SALARY * 0.50
WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
WHERE AGE >= 27 );
This would influence two lines lastly the COMPANY table would have the accompanying 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
The subquery can be utilized related to the DELETE articulation like with some other assertions referenced previously.
The fundamental punctuation is as per the following −
DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]
Example
Expecting, we have COMPANY_BKP table accessible, which is a reinforcement of the COMPANY table.
The accompanying model erases records from the COMPANY table for all the clients, whose AGE is more noteworthy than or equivalent to 27 −
testdb=# DELETE FROM COMPANY
WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
WHERE AGE > 27 );
This would influence two lines lastly the COMPANY table would have the accompanying 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)