A Subquery or Inner question or Nested inquiry is a question inside another SQLite question and implanted inside the WHERE proviso.
A subquery is utilized to restore information that will be utilized in the primary inquiry as a condition to additionally confine the information to be recovered.
Subqueries can be utilized with the SELECT, INSERT, UPDATE, and DELETE proclamations alongside the administrators, for example, =, <, >, >=, <=, IN, BETWEEN, 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 section in the SELECT condition, except if numerous segments are in the fundamental question for the subquery to look at its chose segments.
- An ORDER BY can't be utilized in a subquery, albeit the fundamental question 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 different worth administrators, for example, the IN administrator.
- BETWEEN administrator can't be utilized with a subquery; nonetheless, BETWEEN can be utilized inside the subquery.
Subqueries with SELECT Statement
Subqueries are most regularly utilized with the SELECT assertion. The fundamental language 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 COMPANY table with the accompanying records.
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0
Presently, let us check the accompanying sub-question with SELECT explanation.
sqlite> SELECT *
FROM COMPANY
WHERE ID IN (SELECT ID
FROM COMPANY
WHERE SALARY > 45000) ;
This will create the accompanying outcome.
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
Subqueries with INSERT Statement
Subqueries can likewise be utilized with INSERT articulations. The INSERT explanation utilizes the information got back from the subquery to embed into another table. The chose information in the subquery can be changed with any of the character, date, or number capacities.
Following is the fundamental language 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 comparable construction as COMPANY table and can be made utilizing a similar CREATE TABLE utilizing COMPANY_BKP as the table name. To duplicate the total COMPANY table into COMPANY_BKP, following is the punctuation −
sqlite> INSERT INTO COMPANY_BKP
SELECT * FROM COMPANY
WHERE ID IN (SELECT ID
FROM COMPANY) ;
Subqueries with UPDATE Statement
The subquery can be utilized related to the UPDATE explanation. Either single or different segments in a table can be refreshed when utilizing a subquery with the UPDATE articulation.
Following is the fundamental punctuation 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 a reinforcement of COMPANY table.
Following model updates SALARY by 0.50 occasions in COMPANY table for all the clients, whose AGE is more noteworthy than or equivalent to 27.
sqlite> UPDATE COMPANY
SET SALARY = SALARY * 0.50
WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
WHERE AGE >= 27 );
This would affect two lines lastly COMPANY table would have the accompanying records −
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 10000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 42500.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0
Subqueries with DELETE Statement
Subquery can be utilized related to the DELETE proclamation like with some other assertions referenced previously.
Following is the essential grammar is as per the following −
DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]
Example
Accepting, we have COMPANY_BKP table accessible which is a reinforcement of COMPANY table.
Following model erases records from COMPANY table for all the clients whose AGE is more prominent than or equivalent to 27.
sqlite> DELETE FROM COMPANY
WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
WHERE AGE > 27 );
This will affect two columns lastly COMPANY table will have the accompanying records −
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 42500.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0