Earlier I demonstrated a very interesting behavior in my blog post – UNION and UNION ALL with TEXT DataType – Observation, where we see that UNION clause with TEXT datatype returns Error but with UNION ALL it does not return an error. I did not know that earlier before I posted that blog post. When I had executed the queries I immediately learned about the reasons. While I posted the blog post I did not post the reason for this particular behavior because I just did not feel it was relevant. However, after I posted that blog post there were many different emails and comments asking for the reasons.
Before we continue this blog post, let us see the results with UNION ALL and UNION with TEXT datatypes.
With UNION ALL:
With UNION:
Now here is the reason:
When we use UNION ALL between SELECT statements at that time it returns results as it is but when we use UNION at that time it automatically sorts data internally, while removing duplicate data. Sorting TEXT data is not possible, hence it returns the error which we see on the screen. In another word we can say that UNION clause does ORDER BY operation on the resultset which is not supported by the TEXT datatype hence we see the error.
There are some execellent comments in the blog post, I encourage everyone to read it.
Reference: Pinal Dave (https://blog.sqlauthority.com)
