SQLite subquery
A subquery or inner query or nested query is a query that is embedded in a WHERE clause within another SQLite query.
The data returned using a subquery will be used as conditions in the main query to further limit the data to be retrieved.
Subqueries can be used with SELECT, INSERT, UPDATE and DELETE statements, along with operators such as =, <, >, >=, <=, IN, BETWEEN, etc.
The following are several rules that subqueries must follow:
Subqueries must be enclosed in parentheses.
A subquery can only have one column in the SELECT clause, unless there are multiple columns in the main query that are compared with the selected columns of the subquery.
ORDER BY cannot be used in subqueries, although ORDER BY can be used in the main query. GROUP BY can be used in subqueries and has the same functionality as ORDER BY.
Subquery returns more than one row and can only be used with multi-valued operators, such as the IN operator.
The BETWEEN operator cannot be used with a subquery, however, BETWEEN can be used within a subquery.
Using Subqueries in SELECT Statements
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])
Example
Suppose the COMPANY table has the following 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
Now, let us check the subquery usage in the SELECT statement:
sqlite> SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY WHERE SALARY > 45000) ;
This will produce the following results:
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
Using subqueries in INSERT statements
Subqueries can also be used with INSERT statements. The INSERT statement inserts into another table using the data returned by the subquery. The data selected in the subquery can be modified using any character, date, or numeric function.
The basic syntax is as follows:
INSERT INTO table_name [ (column1 [, column2 ]) ] SELECT [ *|column1 [, column2 ] FROM table1 [, table2 ] [ WHERE VALUE OPERATOR ]
Example
Assume that the structure of COMPANY_BKP is similar 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 with the following syntax:
sqlite> INSERT INTO COMPANY_BKP SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY) ;
Subqueries in the UPDATE statement
Subqueries can be used in conjunction with the UPDATE statement. When using a subquery with the UPDATE statement, single or multiple 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 that we have the COMPANY_BKP table, which is a backup of the COMPANY table.
The following example updates the SALARY of all customers whose AGE is greater than or equal to 27 in the COMPANY table to 0.50 times the original value:
sqlite> 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 ---------- ---------- ---------- ---------- ---------- 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
Using subqueries in DELETE statements
Subqueries can be used in conjunction with the DELETE statement, 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 that we have the COMPANY_BKP table, which is a backup of the COMPANY table.
The following example deletes all customer records in the COMPANY table with an AGE greater than or equal to 27:
sqlite> 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.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
