SQL UNION ALL 1

SQL UNION ALL

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

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:

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

For this SQL SQL Union All operator Query demonstration, We use two tables (Employ, and Employees 2015) present in our Database. The Employ table have ten records

First Table data 5

And [Employees 2015] table has six records. Notice that there are only two distinct records ((2, SQL, Server), and (2, Rob, Johnson)), and the remaining records are the same.

Second Table Records 1

SQL Union All example

The following query will return all the records (including duplicate records) from Employ table, 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]
SQL UNION ALL 1

SQL Union All along with Where

How to use the UNION ALL operator along with the where clause and 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
  • The second result set – It will select the records from Employees 2015 whose yearly income is greater than or equal to 70000
  • and this operator will select all the records (including duplicates) from both the first result set and the second result set.
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]
SQL UNION ALL 2

Union All Errors

The following Query will display the common error that we face while working with the SQL Union All operators. For this, We use two tables (Employee and Employees 2015) available in our Database. From the below screenshot, you can observe that the [Employee] table has 7 columns and 14 rows.

SQL UNION ALL 7

Let us see what happens when we use the Union All operator on the unequal length of columns.

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [Employee]
UNION 
SELECT [FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employees 2015]
SQL UNION ALL 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

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

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

SQL UNION ALL 4