SQL SERVER – Puzzle – Solution – Why Does UNION ALL Work but UNION Produces Error?

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)