Last week, I asked a very simple puzzle about SQL SERVER – Puzzle – Why Does UNION ALL Work but UNION Produces Error?. The puzzle was very simple and interesting both together. I got quite a few valid answers to the puzzle so far. You may check original puzzle post to see all the valid answers. In this blog post we will quickly discuss the answer of our puzzle.
During the puzzle, I have asked a very simple question, which can be explained with the image here:
The question was – Why TEXT datatype is not allowed to use in the UNION operation?
Solution to Puzzle
Let me quote few of the valid answer from the original post –
Tim Monfries – UNION must perform a comparison operation to determine uniqueness whereas UNION ALL does not since it returns all records. Since the text datatype is not comparable, UNION’s comparison-for-uniqueness operation cannot be performed and thus the query fails.
Ruslan Aleksandrovic – UNION need to select all values from second select statement which not in first select statement and need to compare values. TEXT data type is very hard to compare. UNION ALL do not compare and just select ALL values from all statements.
Reference: Pinal Dave (https://blog.sqlauthority.com)
Union here will throw an error as it compares value on both the columns of different tables and tries to find Uniqueness to Sort the result. Since we have Text data type which is hard to compare and it is not really required in this scenario. Whereas Union All does not look for Distinct value and will return all the records from both the rows of different tables…