YouTip LogoYouTip

Home > SQLite > Sqlite Subquery

Sqlite Subquery

📅 2026-06-18 | 📂 SQLite

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

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

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

Here are a few rules that subqueries must follow:

  • Subqueries must be enclosed in parentheses.
  • A subquery can only select one column in the SELECT clause, unless the main query has multiple columns to compare with the columns selected by the subquery.
  • ORDER BY cannot be used in a subquery, although the main query can use ORDER BY. GROUP BY can be used in a subquery, functioning the same as ORDER BY.
  • If a subquery returns more than one row, it can only be used with multi-value operators, like the IN operator.
  • The BETWEEN operator cannot be used with a subquery; however, BETWEEN can be used within a subquery.

Subquery Usage in SELECT Statement

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 ]
                            )

Example

Assume 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 us check the usage of a subquery in the 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 Statement

Subqueries can also be used with INSERT statements. The INSERT statement uses the data returned by the subquery to insert into another table. The data selected in the subquery can be modified with any character, date, or number functions.

The basic syntax is as follows:

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

Example

Assume the COMPANY_BKP table has a structure similar to the COMPANY table and can be created using the same CREATE TABLE statement, just with the table name changed to COMPANY_BKP. Now, let us copy the entire COMPANY table into COMPANY_BKP with the following syntax:

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

Subquery Usage in UPDATE Statement

A subquery can be used with an UPDATE statement. When using a subquery with an UPDATE statement, one or more columns of a table are updated.

The basic syntax is as follows:

UPDATE table
SET column_name = new_value
[ WHERE OPERATOR 
    (SELECT COLUMN_NAME FROM TABLE_NAME)
    [ WHERE) ]

Example

Assume 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 finally the 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

Subquery Usage in DELETE Statement

A subquery can be used with a DELETE statement, just like the other statements mentioned above.

The basic syntax is as follows:

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

Example

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

The following example deletes all records from the COMPANY table where the 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 finally the 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
← Sqlite AutoincrementSqlite Transaction →

📂 Categories