SQLite Subquery

A subquery, also known as an inner query or nested query, refers to embedding a query within the WHERE clause of an SQLite query.

The result of a SELECT statement can serve as the input value for another statement.

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

Here are several rules that subqueries must follow:

• Subqueries must be enclosed in parentheses.
• A subquery in the SELECT clause can only have one column, unless multiple columns in the main query are compared 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 the subquery, serving the same function as ORDER BY.
• If a subquery returns more than one row, it can only be used with multi-value operators like IN.
• The BETWEEN operator cannot be used with a subquery, but BETWEEN can be used within the subquery.

Subquery Usage in SELECT Statements

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

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 examine the use of a subquery 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

Subquery Usage in INSERT Statements

Subqueries can also be used with INSERT statements. The INSERT statement uses data returned by a subquery to insert into another table. The data selected in the subquery can be modified by 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 that COMPANY_BKP has the same structure as the COMPANY table and can be created using the same CREATE TABLE, just with the table name changed to COMPANY_BKP. Now, to copy the entire COMPANY table into COMPANY_BKP, the syntax is as follows:

sqlite> INSERT INTO COMPANY_BKP
           SELECT * FROM COMPANY;

SELECT * FROM COMPANY 
WHERE ID IN (SELECT ID 
             FROM COMPANY);

Subquery Usage in UPDATE Statements

Subqueries can be used in conjunction with UPDATE statements. When using subqueries with UPDATE statements, single or multiple columns in a 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

Suppose we have a 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 their original SALARY:

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 records in the COMPANY table will 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

Subquery Usage in DELETE Statements

Subqueries can be used 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

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

The following example deletes all customer records from 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 final records in the COMPANY table will 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.0