SQL SERVER – Simple Puzzle with UNION – Part 5

It seems that my yesterday’s Simple Puzzle with UNION intrigued many readers. Here is my fifth and final part of this Puzzles with UNION. I strongly suggest to read yesterday’s puzzle before continuing today’s puzzle as it is very much based on yesterday’s puzzle.

• SQL SERVER – Simple Puzzle with UNION
• SQL SERVER – Simple Puzzle with UNION – Part 2
• SQL SERVER – Simple Puzzle with UNION – Part 3
• SQL SERVER – Simple Puzzle with UNION – Part 4

Well, Now if you have solved yesterday’s puzzle today’s puzzle will be a bit easy for you.

Here are two almost similar queries and both of them give us an almost identical answer. However, their execution plans are very different.

Query 1

SELECT *
FROM (
SELECT '1' AS Col
UNION ALL
SELECT '2') t
WHERE t.Col <> 1

Now let us observe the execution plan for this query.

When we pay attention to the execution plan of this query – we can notice that there is implicit conversion as well as there is a warning which is related to implicit conversion. Additionally, when you see the filter it also contains Not Equal To operator which we have used in the query as well.

Query 2

SELECT *
FROM (
SELECT 1 AS Col
UNION ALL
SELECT 2) t
WHERE t.Col <> '1'

Now when you execute both of the queries.

When we play attention to the execution plan of this query – we can notice that there is no implicit conversion even though the data type of the column and variable is mismatched to each other. Additionally, there is interesting behavior of the filter operator as well. In this case, the Not Equal To operator is automatically converted to Equal To operator.

Now here is the question back to you –

Why Query 1 and Query 2 even though looks identically and in theory has almost same logic their behavior in terms with execution plan is so different?

Please leave your answer in the comment section. Next week, I will compile all the answers and will post the valid answer with due credit.

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