(mysql.info.gz) UNION

Info Catalog

(mysql.info.gz) JOIN

(mysql.info.gz) SELECT

 
 13.1.7.2 `UNION' Syntax
 .......................
 
      SELECT ...
      UNION [ALL | DISTINCT]
      SELECT ...
        [UNION [ALL | DISTINCT]
         SELECT ...]
 
 `UNION' is used to combine the result from many `SELECT' statements
 into one result set.  `UNION' is available from MySQL 4.0.0 on.
 
 Selected columns listed in corresponding positions of each `SELECT'
 statement should have the same type. (For example, the first column
 selected by the first statement should have the same type as the first
 column selected by the other statements.) The column names used in the
 first `SELECT' statement are used as the column names for the results
 returned.
 
 The `SELECT' statements are normal select statements, but with the
 following restrictions:
 
    * Only the last `SELECT' statement can have `INTO OUTFILE'.
 
    * `HIGH_PRIORITY' cannot be used with `SELECT' statements that are
      part of a `UNION'. If you specify it for the first `SELECT', it
      has no effect. If you specify it for any subsequent `SELECT'
      statements, a syntax error results.
 
 If you don't use the keyword `ALL' for the `UNION', all returned rows
 will be unique, as if you had done a `DISTINCT' for the total result
 set.  If you specify `ALL', you will get all matching rows from all the
 used `SELECT' statements.
 
 The `DISTINCT' keyword is an optional word (introduced in MySQL 4.0.17).
 It does nothing, but is allowed in the syntax as required by the SQL
 standard.
 
 Before MySQL 4.1.2, you cannot mix `UNION ALL' and `UNION DISTINCT' in
 the same query.  If you use `ALL' for one `UNION', it is used for all
 of them. As of MySQL 4.1.2, mixed `UNION' types are treated such that a
 `DISTINCT' union overrides any `ALL' union to its left. A `DISTINCT'
 union can be produced explicitly by using `UNION DISTINCT' or
 implicitly by using `UNION' with no following `DISTINCT' or `ALL'
 keyword.
 
 If you want to use an `ORDER BY' or `LIMIT' clause to sort or limit the
 entire `UNION' result, parenthesize the individual `SELECT' statements
 and place the `ORDER BY' or `LIMIT' after the last one.  The following
 example uses both clauses:
 
      (SELECT a FROM TBL_NAME WHERE a=10 AND B=1)
      UNION
      (SELECT a FROM TBL_NAME WHERE a=11 AND B=2)
      ORDER BY a LIMIT 10;
 
 This kind of `ORDER BY' cannot use column references that include a
 table name (that is, names in TBL_NAME.COL_NAME format). Instead,
 provide a column alias in the first `SELECT' statement and refer to the
 alias in the `ORDER BY', or else refer to the column in the `ORDER BY'
 using its column position.  (An alias is preferable because use of
 column positions is deprecated.)
 
 To apply `ORDER BY' or `LIMIT' to an individual `SELECT', place the
 clause inside the parentheses that enclose the `SELECT':
 
      (SELECT a FROM TBL_NAME WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
      UNION
      (SELECT a FROM TBL_NAME WHERE a=11 AND B=2 ORDER BY a LIMIT 10);
 
 The types and lengths of the columns in the result set of a `UNION'
 take into account the values retrieved by all the `SELECT' statements.
 Before MySQL 4.1.1, a limitation of `UNION' is that only the values from
 the first `SELECT' are used to determine result column types and
 lengths.  This could result in value truncation if, for example, the
 first `SELECT' retrieves shorter values than the second `SELECT':
 
      mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);
      +---------------+
      | REPEAT('a',1) |
      +---------------+
      | a             |
      | b             |
      +---------------+
 
 That limitation has been removed as of MySQL 4.1.1:
 
      mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);
      +---------------+
      | REPEAT('a',1) |
      +---------------+
      | a             |
      | bbbbbbbbbb    |
      +---------------+
 

Info Catalog

(mysql.info.gz) JOIN

(mysql.info.gz) SELECT