Using SQLite

Name

SELECT — Extract expression value from database

Syntax

Description

A SELECT expression is a subquery within parentheses. The SELECT statement must return only one column. The value of the expression becomes the value of the first row (and only the first row). If no rows are returned, the expression is NULL.

SQLite supports several different types of subqueries, so you need to be careful about which style you’re using. Several expressions, such as IN, allow a direct subquery as part of their syntax. Similarly, several SQL commands, such as CREATE TABLE, support a subquery syntax. In these cases, the subquery is returning a set of data, not a single value. When you use this form of a subquery as a standalone expression, it will only return one value.

This can sometimes have unexpected results. For example, consider these two expressions:

col IN (   SELECT c FROM t   );
col IN ( ( SELECT c FROM t ) );

The only difference between these two expressions is the extra set of parentheses around the subquery in the second line. In the first case, the IN expression sees the subquery as a direct part of the IN expression. This allows the IN to test col against each row returned by the subquery.

In the second case, the extra set of inner parentheses converts the subquery into an expression. This conversion makes IN see a single-value expression list, rather than a subquery. As a result, the col expression ...