Difference between MSSQL UNION and UNION ALL

UNION
The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.

UNION ALL
The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.

A UNION statement effectively does a SELECT DISTINCT on the results set. If you know that all the records returned are unique from your union, use UNION ALL instead, it gives faster results.

There is one more difference between the two when a column of TEXT datatype is involved. When you use UNION for a TEXT column you will get an error, but no error for UNION ALL

Run the following code to check the above issue,

--Error on UNIONDECLARE @TEST1 TABLE(TEXT_COLUMN TEXT)

INSERT INTO @TEST1 (TEXT_COLUMN)SELECT 'TEST DATA - UNION'

SELECT TEXT_COLUMN FROM @TEST1UNION
SELECT 'TEST - UNION'

The error is

Msg 5335, Level 16, State 1, Line 5
The data type text cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable.

The below query will work fine but it will fetch all the data.

SELECT TEXT_COLUMN FROM @TEST1UNION ALL
SELECT 'TEST - UNION'