SQLite subquery
Subquery or nested queries or internal query is embedded in the query WHERE clause within another SQLite query.
Use of sub-query returns the data will be used in the main query as a condition to further limit the data to be retrieved.
Subqueries can be used with SELECT, INSERT, UPDATE, and DELETE statements may be accompanied by the use operators such as =, <,>,> =, <=, IN, BETWEEN like.
The following is a subquery several rules that must be followed:
Subqueries must be enclosed in parentheses.
Sub-query SELECT clause can only have one column, unless multiple columns in the main query, the selected column subquery comparison.
ORDER BY can not be used in a subquery, although the main query can use ORDER BY. You can use the GROUP BY in subqueries, functions the same as the ORDER BY.
Subquery returns more than one line, can only be used with multi-value operator, such as the IN operator.
BETWEEN operator can not be used with sub-queries, however, BETWEEN can be used within subqueries.
SELECT statement subquery
Subquery is usually used with a SELECT statement. 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])
Examples
Suppose 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 examine the SELECT statement subquery:
sqlite> SELECT *
FROM COMPANY
WHERE ID IN (SELECT ID
FROM COMPANY
WHERE SALARY> 45000);
This produces the following results:
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
INSERT statement subquery
Subqueries can also be used with INSERT statement. INSERT statement uses a subquery to return data into another table. In sub-query the selected data can be any character, date, or numeric function modification.
The basic syntax is as follows:
INSERT INTO table_name [(column1 [, column2])]
SELECT [* | column1 [, column2]
FROM table1 [, table2]
[WHERE VALUE OPERATOR]
Examples
Suppose COMPANY_BKP COMPANY table structure and similar, and can use the same CREATE TABLE to create, just changed the name of the table COMPANY_BKP. Now copy the entire COMPANY table to COMPANY_BKP, syntax is as follows:
sqlite> INSERT INTO COMPANY_BKP
SELECT * FROM COMPANY
WHERE ID IN (SELECT ID
FROM COMPANY);
UPDATE statement subquery
Subqueries can be used with the UPDATE statement. When using a subquery UPDATE statement, single or multiple columns are updated in the table.
The basic syntax is as follows:
UPDATE table SET column_name = new_value [WHERE OPERATOR [VALUE] (SELECT COLUMN_NAME FROM TABLE_NAME) [WHERE)]
Examples
Suppose that we have COMPANY_BKP table, backup COMPANY table.
The following examples of the COMPANY table all AGE than or equal to 27 clients SALARY updated the original 0.50-fold:
sqlite> UPDATE COMPANY
SET SALARY = SALARY * 0.50
WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
WHERE AGE> = 27);
This will affect the two lines of the last COMPANY records in the table 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
DELETE statement subquery
Subqueries can be used with the DELETE statement, just like other statements as mentioned above.
The basic syntax is as follows:
DELETE FROM TABLE_NAME [WHERE OPERATOR [VALUE] (SELECT COLUMN_NAME FROM TABLE_NAME) [WHERE)]
Examples
Suppose that we have COMPANY_BKP table, backup COMPANY table.
The following examples delete all customer records greater than or equal AGE 27 COMPANY table:
sqlite> DELETE FROM COMPANY
WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
WHERE AGE> 27);
This will affect the two lines of the last COMPANY records in the table 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