SQLite Subquery

A SQLite subquery is a query that is nested within another SQL query. A subquery can be used to return data from multiple tables, or to calculate values based on data in other tables. Subqueries can also be used with the ORDER BY clause to sort the results of a query.

SQLite subqueries can be used in SELECT, INSERT, UPDATE, and DELETE statements.

There are two types of SQLite subqueries: correlated and non-correlated.
Correlated subqueries are SQL queries that reference columns from outer queries.
Non-correlated subqueries are SQL queries that do not reference any columns from outer queries.

The syntax for using a subquery in the SQLite database is as follows:

Syntax

SELECT * FROM table1
WHERE column1 IN (SELECT column2 FROM table2 WHERE condition);

INSERT INTO table1 (column1, column2, column3) 
VALUES (value1, value2, (SELECT column3 FROM table2 WHERE condition));

UPDATE table1 
SET column1 = (SELECT column2 FROM table2 WHERE condition) 
WHERE condition;

DELETE FROM table1 
WHERE column1 IN (SELECT column2 FROM table2 WHERE condition);

Example

The first example shows how to use the SQLite subquery in a SELECT statement.

SELECT s.*
FROM students s 
WHERE s.address_id IN 
(SELECT sa.address_id FROM student_address sa);

The second example shows how to delete records from a table using the SQLite subquery.

DELETE FROM students s 
WHERE s.address_id IN 
(
SELECT sa.address_id FROM student_address sa 
WHERE sa.city IN ('Boston','San Antonio')
);