(mysql.info.gz) Scalar subqueries
Info Catalog
(mysql.info.gz) Subqueries
(mysql.info.gz) Subqueries
(mysql.info.gz) Comparisons using subqueries
13.1.8.1 The Subquery as Scalar Operand
.......................................
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 wherever 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 whether 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'. In fact, almost
all subqueries can be `NULL'. If the table used in the example were
empty, the value of the subquery would be `NULL'.
When you see examples in the following sections that contain the rather
spartan construct `(SELECT column1 FROM t1)', imagine that your own
code will contain much more diverse and complex constructions.
For example, 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 will be `2' because there is a row in `t2' containing a
column `s1' that has a value of `2'.
A scalar subquery can be part of an expression. Don't forget 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;
Info Catalog
(mysql.info.gz) Subqueries
(mysql.info.gz) Subqueries
(mysql.info.gz) Comparisons using subqueries
automatically generated byinfo2html