SQL UNION ALL

The SQL Server UNION ALL is used to combine two or more SELECT statements and returns a single result set. The Union ALL will select all the records (including duplicate records) from all queries, and the syntax behind it is

SELECT Column1, Column2 ......., ColumnN FROM Table1
UNION ALL
SELECT Column1, Column2 ......., ColumnN FROM Table2

The basic rules to use this SQL Server UNION ALL are:

  1. The number of columns and their order must be the same in all the queries.
  2. The column data types should be compatible with each other.

We use two tables for this Union All operator Query demonstration. They are Employ and Employees 2015 present in our Database. The Employ table has ten records.

SQL EXCEPT 2

The [Employees 2015] table has six records. Notice that there are only two distinct records; the remaining are identical. For more fundamental topics, refer to the SQL Server tutorial.

SQL UNION Example 1

SQL Server UNION ALL example

The following query will return all the records (including duplicate records) from the Employ table and Employees 2015 table and display the result.

SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
FROM [Employ]
UNION ALL
SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
FROM [Employees 2015]
Choose All Columns from both Tables 1

SQL Server UNION ALL along with Where

How to use the UNION ALL operator along with the WHERE clause and the ORDER BY Clause? In this example, we are combining two SELECT statements:

  • The first SQL Server result set – It will select all the records from Employ whose Sales amount is greater than 500. Check out the SQL Server SELECT.
  • The second result set – It will select the records from Employees 2015 whose yearly income is greater than or equal to 70000. Refer to the SQL ORDER BY Clause.
  • And this UNION ALL operator will select all the records (including duplicates) from both the first result set and the second result set. Refer to the SQL WHERE clause.
SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
FROM [Employ]
WHERE [Sales] > 500
  UNION ALL
SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
FROM [Employees 2015]
WHERE [YearlyIncome] >= 70000
ORDER BY [ID]
UNION ALL 2

Union All Errors

The following Query will display the common error we face while working with the SQL Server UNION ALL operator. For this, we use two tables (Employee and Employees 2015) available in our Database. The screenshot below shows that the [Employee] table has 7 columns and 14 rows.

UNION ALL 7

Let us see what happens when we use the UNION ALL operator on columns of unequal length. Please refer to the SQL EXCEPT and SQL INTERSECT operators.

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [Employee]
UNION 
SELECT [FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employees 2015]
Error Message 205 : All queried must have an equal number of expressions 3

I hope you can read the above message. All queries combined using these operators must have an equal number of expressions in their target lists. Now, let us change the query to select an equal number of columns. Refer to the SQL UNION operator. 

SELECT [FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome], [Sales]
FROM [Employee]
  UNION ALL
SELECT [FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome], [Sales]
FROM [Employees 2015]
ORDER BY [YearlyIncome] DESC

The Union all query returns 20 records, i.e., 14 records from the Employee table + 6 from the Employees 2015 table.

UNION ALL Example 4
Categories SQL