SQLite Subquery

Summary: in this tutorial, you will learn about the SQLite subquery to construct more readable and complex queries.

Introduction to SQLite subquery

A subquery is a SELECT statement nested in another statement. See the following statement.

SELECT column_1
FROM table_1
WHERE column_1 = (
   SELECT column_1 
   FROM table_2
);Code language: SQL (Structured Query Language) (sql)

The following query is the outer query:

SELECT column_1
  FROM table_1
 WHERE colum_1 =Code language: SQL (Structured Query Language) (sql)

And the following query is the subquery.

(SELECT column_1
  FROM table_2)Code language: SQL (Structured Query Language) (sql)

You must use a pair of parentheses to enclose a subquery. Note that you can nest a subquery inside another subquery with a certain depth.

Typically, a subquery returns a single row as an atomic value, though it may return multiple rows for comparing values with the IN operator.

You can use a subquery in the SELECT, FROM, WHERE, and JOIN clauses.

SQLite subquery examples

We will use the tracks and albums tables from the sample database for the demonstration.