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 is only tested against the first
row returned by the subquery.
Care must be taken when using
parentheses around SELECT
expressions. Extraneous parentheses shouldn’t alter scalar
expressions, but if a subquery is part of a larger expression,
there can be a critical difference between a subquery and an
expression derived from a subquery.