May 16, 2021 SQLite
2. Sub-queries in SELECT statements are used
3. Sub-queries in INSERT statements are used
A child or internal query or nested query is a query embedded in the WHERE clause within another SQLite query.
The data returned using sub-queries is used as a condition in the main query to further restrict the data to be retrieved.
Subqueals can be used with SELECT, INSERT, UPDATE, and DELETE statements, along with the use of operators such as , slt;
Here are a few rules that sub-queries must follow:
Sub-queries must be enclosed in parentheses.
A child query can have only one column in the SELECT clause, unless there are multiple columns in the main query, compared to the selected column of the child query.
ORDER BY cannot be used in sub-queries, although the main query can use ORDER BY. GROUP BY can be used in sub-queries with the same functionality as ORDER BY.
Child queries return more than one line and can only be used with multi-value operators, such as the IN operator.
The BETWEEN operator cannot be used with sub-queries, but BETWEEN can be used within sub-queries.
Child queries are typically 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])
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's check the sub-query usage in the SELECT statement:
sqlite> SELECT *
FROM COMPANY
WHERE ID IN (SELECT ID
FROM COMPANY
WHERE SALARY > 45000) ;
This results in the following:
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
Child queries can also be used with INSERT statements. T he INSERT statement uses the data returned by the sub-query to be inserted into another table. The data selected in the subs query can be modified with 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 ]
Suppose COMPANY_BKP structure is similar to a COMPANY table and can be created using the same CREATE TABLE, but the table name is changed to COMPANY_BKP. Now copy the entire COMPANY table to the COMPANY_BKP syntax is as follows:
sqlite> INSERT INTO COMPANY_BKP
SELECT * FROM COMPANY
WHERE ID IN (SELECT ID
FROM COMPANY) ;
Subs queries can be used in conjunction with UPDATE statements. When sub-queries are used through update statements, a 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) ]
Suppose we have COMPANY_BKP table that is a backup of the COMPANY table.
The following example updates salarY for all customers with AGE greater than or equal to 27 in the COMPANY table to 0.50 times the original:
sqlite> UPDATE COMPANY
SET SALARY = SALARY * 0.50
WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
WHERE AGE >= 27 );
This affects two rows, and the last record in the COMPANY table is 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
Subs queryes 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) ]
Suppose we have COMPANY_BKP table that is a backup of the COMPANY table.
The following instance removes all customer records in the COMPANY table that AGE is greater than or equal to 27:
sqlite> DELETE FROM COMPANY
WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
WHERE AGE > 27 );
This affects two rows, and the last record in the COMPANY table is 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