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)
