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

You already know what UNION and UNION ALL operators do. They combine datasets while UNION keeps the distinct data UNION ALL will retain all the data. You can read more about that over here: SQL SERVER – Difference Between Union vs. Union All – Optimal Performance Comparison. Let us see why does union all work but union produces error in this puzzle.

Today we will see a puzzle which is related to Union and Union All operator and the hint is in the solution mentioned in the link here.

Let us first create a dataset

-- Create the first table
CREATE TABLE text_union(comment TEXT)
INSERT INTO text_union
SELECT 'this is just test comment'
-- Create the second table
CREATE TABLE text_union_all(comment TEXT)
INSERT INTO text_union_all
SELECT 'Other test comment'

Next we will run two SQL Script where one works and another gives error.

Following query will just work fine.

USE tempdb;
SELECT comment FROM text_union
SELECT comment FROM text_union_all

However, following query will produce error. The error clearly says Text Datatype can’t be used in the UNION.

USE tempdb;
SELECT comment FROM text_union
SELECT comment FROM text_union_all

The real question to all of you is –

Why TEXT datatype is not allowed to use in the UNION operation?

Please leave your answer in the comments section. I will publish all the answers on Monday.


Reference : Pinal Dave (https://blog.sqlauthority.com)

SQL Scripts, SQL Server, SQL Union clause
Previous Post
SQL SERVER – Puzzle – Brain Teaser – Changing Data Type is Changing the Default Value
Next Post
SQL SERVER – Puzzle – Solution – Why Does UNION ALL Work but UNION Produces Error?

Related Posts

25 Comments. Leave new

  • Maneesh singh
    January 4, 2018 10:21 am

    Union will try to result unique resut set, and for that it need to do the comparison . Comparison is not possible with text data type.

  • 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
    January 4, 2018 12:42 pm

    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.

  • As text datatype is stored as blob and the lengths of the two strings are unequal.
    select len(‘this is just test comment’) –25
    select len(‘Other test comment’) –18
    As blob storage comparison is not possible it is throwing an error as not comparable.

  • Sakthi Balaji
    January 4, 2018 1:06 pm

    It will throw error while using UNION operator because, the UNION operator in background uses SORT physical operator to identify the DISTINCT data between Datasets.

    Since the larger datatypes like TEXT, NTEXT cannot be used for SORT operations it will throw error while using
    UNION & ORDER BY on these datatypes.

    We can see the details in the Query Execution plan.

    Please correct me if am wrong

  • Because the Text data type cannot be sorted. In UNION is used DISTINCT Sort operation.

  • The text data type cannot be used for distinct,union,intersect,except because it is not comparable.

  • You can’t do a DISTINCT operation on a TEXT field. UNION is a “UNION ALL with DISTINCT”.

  • Robert French
    January 4, 2018 5:07 pm

    The reason UNION will not work with the fields is that they are ‘Text’ data types. The UNION keyword will sort the data to remove any duplicate data. It is not possible to sort on Text’ data type fields.

  • UNION ALL just combines the results. It does not do any special operation after fetching data. It has to give distinct results by comparing each and every row for UNION and in this case as error says text data can’t be compared.

  • nootropicstallion
    January 4, 2018 9:41 pm

    I believe the text field is not comparable by design in SQL as well as being deprecated and should not be used anyways.

  • Sanjay Monpara
    January 5, 2018 12:06 pm

    The text, ntext, and image data types cannot be compared or sorted.
    in UNION it requires to sort data so it will not work.

  • Raj shankar B
    January 5, 2018 2:10 pm

    Becuase text data type is not supporting DISTINCT.

  • The text data type can not be selected as DISTINCT because it is not comparable

  • UNION ALL just appends the source records into a single result set.

    UNION does the same as UNION ALL but the removes duplicate records. Duplicate removal means each field in each record is compared to its counterparts on all the other records. TEXT data type does not support the comparison operators. Since comparison can not be done UNION ALL fails.

  • Donnie Sawford
    January 6, 2018 8:03 am

    Union all is unsorted, but Union requires a sort operation to filter out duplicates. The text, ntext, and image data types cannot be compared or sorted.

  • Hi Pinal

    Seems to me that TEXT data type is incompatible in the EQUAL TO operator.

    So there is no way to calculate the proper resultset for UNION operation, where naturally EQUAL operator is used.

  • Koushal Patel
    January 7, 2018 4:42 am

    Union discards duplicate records. In order to do that, it does a comparison. And the data type TEXT throws an error when compared. We can not compare two values of TEXT data types.

  • John Mitchell
    January 8, 2018 2:56 pm

    The other contributors are right about the reason for the error. You can make the error go away like this:

    SELECT CAST(comment AS varchar(max)) FROM text_union
    SELECT CAST(comment AS varchar(max)) FROM text_union_all

  • is a reference to a “text” sql type a pointer to a seperate location, and the comparison operator is not compatible with pointers.


Leave a Reply Cancel reply

Exit mobile version