In its simplest form, a subquery is a scalar subquery that
returns a single value. A scalar subquery is a simple operand,
and you can use it almost anywhere a single column value or
literal is legal, and you can expect it to have those
characteristics that all operands have: a data type, a length,
an indication that it can be NULL
, and so on.
For example:
CREATE TABLE t1 (s1 INT, s2 CHAR(5) NOT NULL);
INSERT INTO t1 VALUES(100, 'abcde');
SELECT (SELECT s2 FROM t1);
The subquery in this SELECT
returns a single value ('abcde'
) that has a
data type of CHAR
, a length of 5,
a character set and collation equal to the defaults in effect at
CREATE TABLE
time, and an
indication that the value in the column can be
NULL
. Nullability of the value selected by a
scalar subquery is not copied because if the subquery result is
empty, the result is NULL
. For the subquery
just shown, if t1
were empty, the result
would be NULL
even though
s2
is NOT NULL
.
There are a few contexts in which a scalar subquery cannot be
used. If a statement permits only a literal value, you cannot
use a subquery. For example, LIMIT
requires
literal integer arguments, and LOAD
DATA
requires a literal string file name. You cannot
use subqueries to supply these values.
When you see examples in the following sections that contain the
rather spartan construct (SELECT column1 FROM
t1)
, imagine that your own code contains much more
diverse and complex constructions.
Suppose that we make two tables:
CREATE TABLE t1 (s1 INT);
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 (s1 INT);
INSERT INTO t2 VALUES (2);
Then perform a SELECT
:
SELECT (SELECT s1 FROM t2) FROM t1;
The result is 2
because there is a row in
t2
containing a column s1
that has a value of 2
.
The preceding query can also be written like this, using
TABLE
:
SELECT (TABLE t2) FROM t1;
A scalar subquery can be part of an expression, but remember the parentheses, even if the subquery is an operand that provides an argument for a function. For example:
SELECT UPPER((SELECT s1 FROM t1)) FROM t2;
The same result can be obtained using SELECT
UPPER((TABLE t1)) FROM t2
.