DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH PRINT BOOK
 

(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