SQL Tutorial and Tips

      Select | Insert | Update | Delete

      Open Cursor | Fetch

      Close Cursor | Union

        Structured Query Language
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: See Examples A.     B.     C.
YouTube - SQL Union Statement Examples
Full Screen Full Screen