UNION : UNION Statement in SQL Server combines all rows in the results of two queries.
EXAMPLE’s Given below:
[sql]
SELECT 1
UNION
SELECT 2
[/sql]
Result:
———–
1
2
(2 row(s) affected)
Result is the combining the results of two select queries.
[sql]
SELECT ‘Apple’
UNION
SELECT ‘Mango’
UNION
SELECT ‘Grape’
[/sql]
Result:
—–
Apple
Grape
Mango
(3 row(s) affected)
Result is the combining the results of three select queries.
[sql]
SELECT ‘Apple’
UNION
SELECT ‘Apple’
UNION
SELECT ‘Grape’
[/sql]
result:
—–
Apple
Grape
(2 row(s) affected)
Result is the combining the results of three select queries, it can be observed that the duplicate rows in the select queries are ignored.
[sql]
SET NOCOUNT ON
DECLARE @table1 TABLE (
id INT)
DECLARE @table2 TABLE (
id INT)
DECLARE @i AS INT
SET @i = 1
WHILE @i <= 10
BEGIN
INSERT INTO @table1
SELECT @i
SET @i = @i + 1
END
SET @i = 5
WHILE @i <= 15
BEGIN
INSERT INTO @table2
SELECT @i
SET @i = @i + 1
END
SELECT *
FROM @table1
SELECT *
FROM @table2
SELECT *
FROM @table1
UNION
SELECT *
FROM @table2
[/sql]
Result:
id
———–
1
2
3
4
5
6
7
8
9
10
id
———–
5
6
7
8
9
10
11
12
13
14
15
id
———–
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
@table1 contains numbers 1 to 10, @table2 contains numbers 5 to 15
union of @table1 and @table2 contains numbers 1 to 15 (with out dublicate values)
So union statement works as follows:
If two queries are unioned and if the query 1 results in x rows and query 2 in y rows of which z rows are dupicate with respect to query1,
then union gives x+y-z rows.
Note:
Make sure that the select statements fetch the same number of columns, if not you would get the error
Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists
Sample Code:
[sql]
SELECT ‘Apple’
UNION
SELECT ‘Apple’,’Grape’
[/sql]
Make sure that the column datatypes are same in each select statements, if not you would get the error
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘two’ to data type int.
Sample Code:
[sql]
SELECT ‘Apple’,1
UNION
SELECT ‘Grape’,’two’
[/sql]
The column names in the first select statement are used in the output of union statement. Use alias names if you wanted to change the column names.
If you are specifng values directly in select queries then use alias names for columns.
Sample queries:
[sql]
SELECT * INTO #a FROM (
SELECT ‘Apple’ ,1
UNION
SELECT ‘Grape’,’2′) a
[/sql]
Output:
Msg 8155, Level 16, State 2, Line 4
No column name was specified for column 1 of ‘a’.
Msg 8155, Level 16, State 2, Line 4
No column name was specified for column 2 of ‘a’.
Msg 1038, Level 15, State 5, Line 1
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as “” or [] are not allowed. Change the alias to a valid name.
Msg 1038, Level 15, State 5, Line 1
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as “” or [] are not allowed. Change the alias to a valid name.
Fixed code:
[sql]
SELECT * INTO #a FROM (
SELECT ‘Apple’ a1 ,1 a2
UNION
SELECT ‘Grape’,’2′) a
[/sql]
Applicable to SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 r2, SQL Server 2012