Structured Query Language

SQL Tutorial & Tips (select an SQL statement) Select Insert Update Delete Open Cursor Fetch Close Cursor Union

                  UNION
		  
Combines the results of two or more queries into a single
results set consisting of all the rows belonging to all
queries in the union.
 
Syntax:

SELECT select_list [INTO clause] 
	[FROM clause] 
	[WHERE clause] 
	[GROUP BY clause] 
	[HAVING clause] 
 
[UNION [ALL] 
 
SELECT select_list 
	[FROM clause] 
	[WHERE clause] 
	[GROUP BY clause] 
	[HAVING clause]...] 
[ORDER BY clause] 
[COMPUTE clause]

where
 
INTO
  Creates a new table based on the columns specified in the select
  list and the rows chosen in the WHERE clause. The first query
  in the Transact-SQL statement is the only one that can contain
  an INTO clause.
 
UNION
  Is a set operation that requires two operands.
 
ALL
  Incorporates all rows into the results, including duplicates.
 




Remarks:
 
Because UNION is a binary operation, you must add parentheses to
an expression involving more than two queries to specify evaluation
order. The default evaluation order of a Transact-SQL statement
containing UNION operators is left to right. By default duplicate
rows are eliminated; however, using UNION with the ALL keyword
returns all rows, including duplicates.
 
The first query in the UNION statement can contain an INTO clause
that creates a table to hold the final results set. The INTO statement
must be in the first query or an error message will be returned.
 
GROUP BY and HAVING clauses can be used only within individual queries
and cannot be used to affect the final results set. ORDER BY and
COMPUTE clauses are allowed only at the end of the UNION statement
to define the order of the final results or to compute summary values.
 
The UNION operator cannot appear within a CREATE VIEW statement or
within a subquery. You cannot specify the FOR BROWSE option in queries
with the UNION operator.
The UNION operator can appear within an INSERT-SELECT statement.
For example:

    INSERT INTO salesall
	SELECT * FROM sales
	UNION
	SELECT * FROM saleseast
	
All select lists in a Transact-SQL statement must have the same
number of expressions (such as column names, arithmetic expressions,
and aggregate functions). For example, the following statement is
invalid because the first select list is longer than the second:

    SELECT au_id, title_id, au_ord FROM titleauthor
    UNION
    SELECT stor_id, date FROM sales

Corresponding columns in the individual queries of a UNION statement
must occur in the same order, because UNION compares the columns one
to one in the order given in the individual queries.
 
The descriptions of the columns that are part of a UNION operation
do not have to be identical. 










 
Examples:
 
A. Simple UNION
The results set in this example includes the contents of the
stor_id and stor_name columns of both the stores and storeseast
tables.

      SELECT stor_id, stor_name FROM stores
      UNION
      SELECT stor_id, stor_name FROM storeseast

B. Selecting INTO with UNION
In this example, the INTO clause in the first query specifies
that the table called results hold the final results set of the
union of the designated columns of the publishers, stores, and
storeseast tables.

      SELECT pub_id, pub_name, city INTO results FROM publishers
      UNION
      SELECT stor_id, stor_name, city FROM stores
      UNION
      SELECT stor_id, stor_name, city FROM storeseast

C. UNION of Two SELECT Statements with ORDER BY
The order of certain parameters used with the UNION clause is
important. This example illustrates the incorrect and correct
usage of UNION in two SELECT statements in which a column is to
be renamed in the output.
 
/* INCORRECT */

      SELECT city FROM storeseast
	ORDER BY cities
      UNION
      SELECT cities = city FROM stores

/* CORRECT */

      SELECT cities = city FROM storeseast
      UNION
      SELECT city FROM stores
        ORDER BY cities