The UNION operator allows you to combine the results of two or more SELECT statements into a single result set. As long as the result sets combined using UNION consists of all the rows belonging to all queries in the union. They must also have the same number of columns, and the corresponding result set columns must have compatible data types.
UNION Operator is different from using joins that combine columns from two tables. Two basic rules for combining the result sets of two queries with UNION are:
The number and the order of the columns must be identical in all queries.
The data types must be compatible.
Sample Database All the examples on this page use the:
AdventureWorks Lightweight (LT) Sample Database.
Pubs Sample Database or
Northwind Sample Database.
if you don't have an instance of these databases running on your pc, please follow the links to download and install them.
Combining Results with UNION In it's simplest form, this query creates a UNION between the two tables:
SELECT * FROM Table1
UNION
SELECT * FROM Table2
| ** | ||
1 In the JOINS, the columns may be different in the tables but in the UNION the number of column must be same.
1 In case of UNION result set put rows one after each other i.e. result set puts vertically. But in case of JOIN result set put column one after each other i.e. result set puts horizontally. UNION makes a Cartesian product.
1 UNION is used for joining 2 result sets and JOIN is used for joining two or more tables, views, table-valued functions.
The UNION operator allows you to combine the results of two or more SELECT statements into a single result set. The result sets combined using UNION must all have the same structure. They must have the same number of columns, and the corresponding result set columns must have compatible data types.
Let's say we have two tables called TableA and TableB. Using the SQL UNION operator, we are going to combine the results of the two select statements as follows:
Below is the result of executing the above SQL UNION query. Only unique records are returned from both tables.
| Column 1 | Column 2 | |
|---|---|---|
| A | A | |
| A | B | |
| A | C | |
| ** | ||
| Column 1 | Column 2 | |
|---|---|---|
| B | A | |
| A | B | |
| B | C | |
| ** | ||
| Column 1 | Column 2 | |
|---|---|---|
| A | A | |
| A | B | |
| A | C | |
| B | A | |
| B | C | |
| * The UNION operator selects only distinct values by default ** Duplicate rows are displayed only once |
||
By default, the UNION operator removes duplicate rows from the result set. If you use the UNION ALL operator, all rows are included in the results and duplicates are not removed. SQL UNION ALL operator combines the results of two select statements. For example:
Below is the result set of executing the above SQL UNION ALL query, this result set includes all records, including duplicate records from both tables. As you can see the duplicate rows are repeated in the result set.
| Column 1 | Column 2 | |
|---|---|---|
| A | A | |
| A | B | |
| A | C | |
| ** | ||
| Column 1 | Column 2 | |
|---|---|---|
| B | A | |
| A | B | |
| B | C | |
| ** | ||
| Column 1 | Column 2 | |
|---|---|---|
| A | A | |
| A | B | |
| A | B | |
| A | C | |
| B | A | |
| B | C | |
| * Notice that duplicates are repeated in the result set. | ||
The result set column names of a UNION are the same as the column names in the result set of the first SELECT statement in the UNION. The result set column names of the other SELECT statements are ignored.
Note
If you do not specify any keyword with UNION operator then by default it takes the DISTINCT keyword i.e. “UNION DISTINCT”
which removes duplicate rows from the result set. If you use ALL, all rows are then included in the results and duplicates are not removed.
The exact results of a UNION operation depend on the sort order chosen during installation and the ORDER BY clause.
Any number of UNION operators can appear in a Transact-SQL statement, for example:
SELECT * FROM TableA
UNION
SELECT * FROM TableB
UNION
SELECT * FROM TableC
UNION
SELECT * FROM TableD
By default, SQL Server evaluates a statement containing UNION operators from left to right. Use parentheses to specify the order of evaluation. For example, the following statements are not equivalent:
/* First statement. */
In the first statement, duplicates are eliminated in the union between TableB and TableC. Then, in the union between that set and TableA, duplicates are not eliminated. In the second statement, duplicates are included in the union between TableA and TableB but are eliminated in the subsequent union with TableC. ALL has no effect on the final result of this expression.
When UNION is used, the individual SELECT statements cannot have their own ORDER BY or COMPUTE clauses. There can only be one ORDER BY or COMPUTE clause after the last SELECT statement, it is applied to the final, combined result set.
GROUP BY and HAVING can only be specified in the individual SELECT statements.
Follow these guidelines when using UNION operators:
All select lists in the statements being combined with UNION must have the same number of expressions (column names, arithmetic expressions, aggregate functions, and so on).
Corresponding columns in the result sets being combined with UNION, or any subset of columns used in individual queries, must be of the same data type, have an implicit data conversion possible between the two data types, or have an explicit conversion supplied. For example, UNION is not possible between a column of datetime data type and one of binary data type unless an explicit conversion is supplied, while UNION is possible between a column of money data type and one of int data type because they can be implicitly converted.
Corresponding result set columns in the individual statements being combined with UNION must occur in the same order, because UNION compares the columns one-to-one in the order given in the individual queries.
| ** | ||
When different data types are combined in a UNION operation, they are converted using the rules of data type precedence. In the preceding example, the int values are converted to float because float has a higher precedence than int. For more information, see Data Type Precedence.
This query produces an error message because the data types of corresponding columns are not compatible:
The column names in the table resulting from UNION are taken from the first individual query in the UNION statement. If you want to refer to a column in the result set by a new name (for example, in an ORDER BY clause), it must be referred to that way in the first SELECT:
UNION operator is used to combine the result sets of two SELECT statements & combined result set includes all records from both tables.
The number of columns & column data types must be same for using the UNION operator.
The UNION DISTINCT keyword is returns the duplicate records from the result set. In the UNION operator if no any keyword is specified then it acts as DISTINCT keyword.
UNION ALL simply joins 2 result sets including all the rows,does not remove the duplicate records from the result set.
Follow these guidelines when using UNION with other Transact-SQL statements:
The first query in the UNION statement can contain an INTO clause that creates a table to hold the final result set.
Only the first query can use an INTO clause; if it appears anywhere else, Microsoft SQL Server displays an error message. Also, remember that if the select into/bulkcopy option is not set, SELECT INTO can create only temporary tables.
ORDER BY and COMPUTE clauses to define the order of the final results or compute summary values are allowed only at the end of the UNION statement. They cannot be used within the individual queries that make up the UNION statement.
GROUP BY and HAVING clauses can be used within individual queries only; they cannot be used to affect the final result set.
The UNION operator can be used within an INSERT statement.
The FOR BROWSE clause cannot be used in statements involving the UNION operator.
Partitioned views allow the data in a large table to be split into subtables. The data is partitioned between the subtables based on ranges of data values in one of the columns. The data ranges for each subtable are defined in a CHECK constraint specified on the partitioning column. A view is then defined that uses UNION ALL to combine selects of all the subtables into a single result set. When SELECT statements referencing the view specify a search condition on the partition column, the query optimizer uses the CHECK constraint definitions to determine which subtable contains the rows.
For example, a sales table that records sales for 1998 has been partitioned into 12 subtables, one for each month. Each subtable has a constraint defined on the OrderMonth column:
CREATE TABLE May1998Sales (OrderID INT PRIMARY KEY, CustomerID INT NOT NULL, OrderDate DATETIME NULL CHECK (DATEPART(yy, OrderDate) = 1998) OrderMonth INT CHECK (OrderMonth = 5), DeliveryDate DATETIME NULL, CHECK (DATEPART(mm, OrderDate) = OrderMonth) ) The application populating May1998Sales must ensure all rows have 5 in the OrderMonth column and the order date specifies a date in May, 1998. This is enforced by the constraints defined on the table. A view is then defined that uses UNION ALL to select the data from all 12 tables as a single result set: CREATE VIEW Year1998Sales AS SELECT * FROM Jan1998Sales UNION ALL SELECT * FROM Feb1998Sales UNION ALL SELECT * FROM Mar1998Sales UNION ALL SELECT * FROM Apr1998Sales UNION ALL SELECT * FROM May1998Sales UNION ALL SELECT * FROM Jun1998Sales UNION ALL SELECT * FROM Jul1998Sales UNION ALL SELECT * FROM Aug1998Sales UNION ALL SELECT * FROM Sep1998Sales UNION ALL SELECT * FROM Oct1998Sales UNION ALL SELECT * FROM Nov1998Sales UNION ALL SELECT * FROM Dec1998Sales The SQL Server query optimizer recognizes that the search condition in this SELECT statement references only rows in the May1998Sales and Jun1998Sales tables, and limits its search to those tables: SELECT * FROM Year1998Sales WHERE OrderMonth IN (5,6) AND CustomerID = 64892 CHECK constraints are not strictly needed for the partitioned view to return the correct results. However, if the CHECK constraints have not been defined, the query optimizer must search all the tables instead of only those that cover the search condition on the partitioning column. Without the CHECK constraints the view operates like any other view with UNION ALL. The query optimizer cannot make any assumptions about the values stored in different tables and it cannot skip searching the tables that participate in the view definition. Another benefit of partitioned views is that they make it easier to maintain the subtables independently. For example, at the end of a period: The definition of the partitioned view for current results can be changed to add the newest period and drop the oldest period. The definition of the partitioned view for past results can be changed to add the period just dropped from the current results view. The past results view can also be updated to remove and archive the oldest period it covers. When you insert data into the partitioned views, the sp_executesql system stored procedure can be used to create INSERT statements with execution plans that have a high chance of being reused in systems with many concurrent users. ////////////////////////////////////////// 5 /////////////////////////////////////////// UNION operator is used to combine the result sets of two SELECT statements & combined result set includes all records from both tables.
The number of columns & column data types must be same for using the UNION operator.
The UNION DISTINCT keyword is returns the duplicate records from the result set. In the UNION operator if no any keyword is specified then it acts as DISTINCT keyword.
UNION ALL simply joins 2 result sets including all the rows,does not remove the duplicate records from the result set.
1. In the JOINS, the columns may be different in the tables but in the UNION the number of column must be same.
2. In case of UNION result set put rows one after each other i.e. result set puts vertically. But in case of JOIN result set put column one after each other i.e. result set puts horizontally. UNION makes a Cartesian product.
3. UNION is used for joining 2 result sets and JOIN is used for joining two or more tables, views, table-valued functions.
