SQLite Subqueries
A subquery, also called an inner query or nested query, refers to embedding a query statement within the WHERE clause of an SQLite query.
The result of a SELECT statement can be used as the input value for another statement.
Subqueries can be used with SELECT, INSERT, UPDATE, and DELETE statements, and can be accompanied by operators such as =, <, >, >=, <=, IN, BETWEEN, etc.
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 the main query has multiple columns to compare with the selected columns of 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, and it 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; however, BETWEEN can be used within a subquery.
Subqueries in SELECT Statements
Subqueries are most 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 ]
[WHERE])
Example
Assume 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's check the use of subqueries in a SELECT statement:
sqlite> SELECT *
FROM COMPANY
WHERE ID IN (SELECT ID
FROM COMPANY
WHERE SALARY > 45000) ;
This will produce the following result:
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
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 selected data 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 ]
Example
Assume COMPANY_BKP has a structure 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, to copy the entire COMPANY table to COMPANY_BKP, the syntax is as follows:
sqlite> INSERT INTO COMPANY_BKP
SELECT * FROM COMPANY
WHERE ID IN (SELECT ID
FROM COMPANY) ;
Subqueries in UPDATE Statements
Subqueries can be used in conjunction 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 [ VALUE ] (SELECT COLUMN_NAME FROM TABLE_NAME) [ WHERE) ]
Example
Assume we have the COMPANY_BKP table, which is a backup of the COMPANY table.
The following example updates the SALARY of all customers in the COMPANY table whose AGE is greater than or equal to 27 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, and the records in the COMPANY table will finally be 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
Subqueries in DELETE Statements
Subqueries can be used in conjunction 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) ]
Example
Assume we have the COMPANY_BKP table, which is a backup of the COMPANY table.
The following example deletes the records of all customers in the COMPANY table whose AGE is 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 records in the COMPANY table will finally be 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.0Other Extensions