(mysql.info) scalar-subqueries

(mysql.info) comparisons-using-subqueries
 
 13.2.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
 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 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'.
 
 There are a few contexts in which a scalar subquery cannot be used. If
 a statement allows only a literal value, you cannot use a subquery. For
 example, `LIMIT' requires literal integer arguments, and `LOAD DATA
 INFILE' requires a literal string filename. 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'.
 
 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;
Info Catalog
(mysql.info) subqueries
(mysql.info) comparisons-using-subqueries
automatically generated byinfo2html