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
UNION
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
UNION ALL
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)
13 Comments. Leave new
In the UNION example @TEST1 variable id of type text and atext type stores the data in a blob while the string ‘TEST – UNION’ is of varchar type. UNION operator gives the distinct values set discarding the duplicate values but UNION ALL includes all the duplicate values from all datasets. Thus, because of metadata difference in these two datasets, UNION is giving error while comparing for distinct/duplicate values.
In old versions of SQL Server, error message is different.
The text data type cannot be selected as DISTINCT because it is not comparable.
This also proves that UNION is performing DISTINCT.
And text data type is not supporting DISTINCT.
Best solution is avoid text data type, because it is deprecated data type.
Thank you for the nice tip !
It is new for me.
Union removes the duplicate values. But at the time of removing duplicate values, values should be belongs to same data types. But above case, first we have inserted the values to Text datatype it is same as blob..and we are tried to compare the test datatype to varchar datatype… That’s why we are received the error in first query.
In second script UNION All, It is just concatenating(Clubbing) the data and showing.
Hi,
I enjoyed this article. I would love to see some more such interesting article from your side.
Thanks for writing .
Keep up your great work!
Regards,
varija tripathi
When we use Union, it will take larger datatype of the column. In this example it will take Text as a result column type. Also it will try to sort the result set column. Text datatype won’t compatiable with this operations. So we are getting the error message.
‘Order by’ not supporting text data type.
When we do ‘union’ then its automatically orders records to distinguish it.
That’s why its giving error.
When we do ‘union’ then its automatically orders records??? I agree union is trying to distinguish distinct value but how it related with Order by???
Rikhil, you are right.
Its only distinguish records.
text data type not comparable so it not supported by both ‘distinct’ and ‘order by’.
Thanks to correct me.
Thanks Sanjay!
okay. Thank you sanjay for clearing confusion :)
IN oracle we can use the MINUS to pull out all the changed records only. Is there similar functionality in SQLServer?