You can find the difference between UNION and UNION ALL in the following posts Union vs. Union All – Which is better for performance?
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
--Error on UNION
DECLARE @TEST1 TABLE(TEXT_COLUMN TEXT)
INSERT INTO @TEST1 (TEXT_COLUMN)
SELECT 'TEST DATA - UNION'
SELECT TEXT_COLUMN FROM @TEST1
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.
Now let us see second example where there is no error because we are using UNION ALL.
--No Error on UNION ALL
DECLARE @TEST2 TABLE(TEXT_COLUMN TEXT)
INSERT INTO @TEST2 (TEXT_COLUMN)
SELECT 'TEST DATA - UNION ALL'
SELECT TEXT_COLUMN FROM @TEST2
SELECT 'TEST - UNION ALL'
The result is
TEXT_COLUMN ------------------------ TEST DATA - UNION ALL TEST - UNION ALL
I find this difference pretty interesting. I recently learned about it. Just thought of asking – did you know about this or just learned along with me?
Reference: Pinal Dave (https://blog.sqlauthority.com)