The SQL UNION ALL is used to combine two or more SELECT statements, and returns a single result set. Union ALL in SQL will select all the records (including duplicate records) from all queries.
SQL Union All Syntax
The basic syntax behind the Union All in SQL Server is as shown below:
|
1 2 3 4 |
-- SQL Server UNION ALL Syntax SELECT Column1, Column2 ......., ColumnN FROM Table1 UNION ALL SELECT Column1, Column2 ......., ColumnN FROM Table2 |
The basic rules to use this Union ALL in SQL Server are:
- The number of columns, and its order must be same in all the queries.
- The column data types should compatible with each other.
In this article we will show you, How to write or use SQL Union All operator with example. For this demonstration, We are going to use two tables (Employ, and Employees 2015) present in our [SQL Tutorial] Database.
From the below figure you can observe that, [Employ] table have ten records
And [Employees 2015] table have six records. Notice that, there is only two distinct records ((2, SQL, Server), and (2, Rob, Johnson)), and the remaining records are same.
SQL Union All example
The following SQL Query will return all the records (including duplicate records) from Employ table, Employees 2015 table, and display the result
SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
-- Example for SQL Server Union ALL SELECT [ID] ,[FirstName] ,[LastName] ,[Occupation] ,[YearlyIncome] ,[Sales] FROM [SQL Tutorial].[dbo].[Employ] UNION ALL SELECT [ID] ,[FirstName] ,[LastName] ,[Occupation] ,[YearlyIncome] ,[Sales] FROM [SQL Tutorial].[dbo].[Employees 2015] |
OUTPUT
SQL Union All along with Where
The following SQL Query will display, How to use the SQL UNION ALL operator along with the where clause, and ORDER BY Clause. In this example, we are combining two statement:
- First result set – It will select all the records from Employ, whose Sales amount is greater than 500
- Second result set – It will select all the records from Employees 2015 whose yearly income is greater than or equal to 70000
- and the Union All operator will select all the records (including duplicates) from both the first result set, and the second result set.
SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
-- Example for SQL Server Union ALL SELECT [ID] ,[FirstName] ,[LastName] ,[Occupation] ,[YearlyIncome] ,[Sales] FROM [SQL Tutorial].[dbo].[Employ] WHERE [Sales] > 500 UNION ALL SELECT [ID] ,[FirstName] ,[LastName] ,[Occupation] ,[YearlyIncome] ,[Sales] FROM [SQL Tutorial].[dbo].[Employees 2015] WHERE [YearlyIncome] >= 70000 ORDER BY [ID] |
OUTPUT
SQL Union All Errors
The following SQL Query will display the common error that we face while working with the SQL Union All operator. For this, We are going to use two tables (Employee and Employees 2015) present in our [SQL Tutorial] Database.
From the below screenshot you can observe that, [Employee] table have 7 columns, and 14 rows.
Let us see what will happen, when we use Union All operator on unequal length of columns.
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
-- SQL UNION ALL Example SELECT [FirstName] ,[LastName] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] ,[HireDate] FROM [SQL Tutorial].[dbo].[Employee] UNION SELECT [FirstName] ,[LastName] ,[Occupation] ,[YearlyIncome] ,[Sales] FROM [SQL Tutorial].[dbo].[Employees 2015] |
OUTPUT
Hope you can read the message. Now, let us change the query to select equal number of columns
SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- Example for SQL Server Union ALL SELECT [FirstName] ,[LastName] ,[Occupation] ,[YearlyIncome], [Sales] FROM [SQL Tutorial].[dbo].[Employee] UNION ALL SELECT [FirstName] ,[LastName] ,[Occupation] ,[YearlyIncome], [Sales] FROM [SQL Tutorial].[dbo].[Employees 2015] ORDER BY [YearlyIncome] DESC |
OUTPUT
From the above screenshot you can observe that, it is returning 20 records i.e., 14 from Employee table + 6 from Employees 2015 table.
Thank You for Visiting Our Blog
Leave a Reply