Operator UNION ALL in SQL makes it possible to unify 2 query results and does not remove duplicities in queries. Operator returns records of both queries with no regard for existing duplicities. UNION ALL belongs to group called SET operators together with UNION, EXCEPT, INTERSECT

SQL UNION ALL Syntax

SELECT Column
FROM dbo.Table
WHERE Condition

  UNION ALL

SELECT Column
FROM dbo.Table
WHERE Condition

Syntaxe is again similar to all Set operators. This query returns all records from both queries with no regard for duplicities in the column.

UNION ALL Example

Assignment of the task will be same as for example with UNION operator. Let’s have a table with list of products:

Let’s do the unification of 2 queries again:

• first query – records where Product Key (Primary key) is lower than 10
• second query – records where Product Key (Primary key) is lower than 5

Both selections overlap. Product Keys <= 5 are selected twice. Operator should return all 15 records (10 from the first query, 5 from second one). UNION returned 10 records

SELECT [ProductKey], [EnglishProductName]
FROM [AdventureWorksDW2014].[dbo].[DimProduct]
WHERE [ProductKey]<=10

  UNION ALL

SELECT [ProductKey], [EnglishProductName]
FROM [AdventureWorksDW2014].[dbo].[DimProduct]
WHERE [ProductKey]<=5

The result is all 15 records (last 5 records are duplicities)