SQLite子查询

Subquery or internal query or nested query is another query within an SQLite query, and is embedded in the WHERE clause.

Subqueries are used to return data that will be used in the main query, as conditions to further limit the data to be retrieved.

Subqueries can be used with SELECT, INSERT, UPDATE, and DELETE statements, as well as =, <, >, >=, <=, IN, BETWEEN, and other operators.

There are some rules that must be followed for subqueries-

• Subqueries must be placed within parentheses.

• A subquery can have only one column in the SELECT clause, unless the main query has multiple columns available for the subquery to compare its selected columns.

• Although the main query can use ORDER BY, ORDER BY cannot be used within a subquery. GROUP BY can be used to perform the same function as ORDER BY in a subquery.

• Subqueries that return multiple rows can only be used with multiple value operators (such as the IN operator).

• The BETWEEN operator cannot be used with subqueries; however, BETWEEN can be used within a subquery.

Subqueries with 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

The COMPANY table with 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 use the SELECT statement to check the following subquery.

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

Subqueries with INSERT statements

Subqueries can also be used with INSERT statements. INSERT statements use data returned from subqueries to insert into another table. You can use any character, date, or numeric function to modify the selected data in the subquery.

The basic syntax is as follows-

INSERT INTO table_name [ (column1　[, column2　]) ]
　　　SELECT [　*|column1　[, column2　]
　　　FROM table1　[, table2　]
　　　[ WHERE VALUE OPERATOR ]

Example

Consider a table COMPANY_BKP, which has a similar structure to the COMPANY table and can be used as a table name with the same CREATE TABLE syntax. To copy the entire COMPANY table to COMPANY_BKP, use the following syntax-

sqlite> INSERT INTO COMPANY_BKP
　　　SELECT　*　FROM COMPANY　
　　　WHERE ID IN (SELECT ID　
　　　　　　FROM COMPANY) ;

Subqueries with UPDATE statements

Subqueries can be combined with UPDATE statements. When using subqueries with UPDATE statements, you can update a single column or multiple columns 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 ) ]

Example

Assuming we have a COMPANY_BKP table available, which is a backup of the COMPANY table.

The following example updates the salaries of all customers in the COMPANY table with ages greater than or equal to27Update the salaries of all customers aged 0.50 times.

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 final COMPANY table will have the following records-

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 with DELETE statements

Subqueries can be used with DELETE statements, just like any other statement mentioned above.

The basic syntax is as follows-

DELETE FROM TABLE_NAME[ WHERE OPERATOR [ VALUE ]
　　　(SELECT COLUMN_NAME
　　　　　　FROM TABLE_NAME)
　　　[ WHERE ) ]

Example

Assuming we have a COMPANY_BKP table available, which is a backup of the COMPANY table.

The following example deletes records from the COMPANY table with ages greater than or equal to27of all customers' records.

sqlite> DELETE FROM COMPANY
　　　WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
　　　WHERE AGE >　27　);

This will affect two rows, and the final COMPANY table will have the following records-

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