Subqueries, or internal queries, or nested queries, refer to the
SQLite
In the query
WHERE
The query statement is embedded in the clause.
One
SELECT
The query result of a statement can be used as an input value for another statement.
A subquery can be associated with the
SELECT
、
INSERT
、
UPDATE
And
DELETE
Statements can be accompanied by operators such as =, <, >, > =, < =, IN, BETWEEN, and so on.
Here are a few rules that subqueries must follow:
Subqueries must be enclosed in parentheses.
The subquery is in the
SELECTThere can be only one column in the clause, unless there are multiple columns in the main query, compared to the selected column of the subquery.ORDER BYCannot be used in subqueries, although the main query can use theORDER BY. Can be used in subqueriesGROUP BY, features vs.ORDER BYThe same.The subquery returns more than one row and can only be used with multivalued operators, such as the IN operator.
BETWEENOperators cannot be used with subqueries, butBETWEENcan be used within subqueries.
1.40.1.
SELECT
Subqueries in statements use the ¶
Subqueries are usually associated with
SELECT
Statement is used together. 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])
1.40.2. Example ¶
Hypothetical
COMPANY
The 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
SELECT
Statement uses:
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
1.40.3.
INSERT
Subqueries in statements use the ¶
Subqueries can also be associated with
INSERT
Statement is used together. The
INSERT
statement inserts the data returned by the subquery into another table. The data selected in the subquery can be modified with any character, date, or number function.
The basic syntax is as follows:
INSERT INTO table_name [ (column1 [, column2 ]) ]
SELECT [ *|column1 [, column2 ]
FROM table1 [, table2 ]
[ WHERE VALUE OPERATOR ]
1.40.4. Example ¶
Hypothetical
COMPANY_BKP
The structure of the
COMPANY
Tables are similar, and you can use the same
CREATE
TABLE
To create, but the table name is changed to
COMPANY_BKP
. Now put the whole
COMPANY
The table is copied to
COMPANY_BKP
The syntax is as follows:
sqlite> INSERT INTO COMPANY_BKP
SELECT * FROM COMPANY
WHERE ID IN (SELECT ID
FROM COMPANY) ;
1.40.5. Subqueries in UPDATE statements use the ¶
A subquery can be associated with the
UPDATE
Statement is used in combination. When passed
UPDATE
Statement uses a subquery, 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) ]
1.40.6. Example ¶
Hypothetically, we have
COMPANY_BKP
Table, yes
COMPANY
A backup of the table.
The following example sets the
COMPANY
All in the table
AGE
For customers greater than or equal to 27
SALARY
Updated 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 will affect two lines, and finally
COMPANY
The records in the 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
1.40.7.
DELETE
Subqueries in statements use the ¶
A subquery can be associated 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) ]
1.40.8. Example ¶
Hypothetically, we have
COMPANY_BKP
Table, yes
COMPANY
A backup of the table.
The following example is deleted
COMPANY
All in the table
AGE
Customer records 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 lines, and finally
COMPANY
The records in the 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