When I started to write Simple Puzzle with UNION last week, I had no clue this will be extremely popular blog post and will turn into a mini-series. Earlier I wrote three different blog posts on this subject and they are very well received.
All the three blog posts which I wrote earlier are kind of back to basics. There are few readers who have requested to post something which make them think. Well for those who requested here is another fourth puzzle in the same series.
- SQL SERVER – Simple Puzzle with UNION
- SQL SERVER – Simple Puzzle with UNION – Part 2
- SQL SERVER – Simple Puzzle with UNION – Part 3
Here are three different script which are using UNION, Subquery and NOT EQUAL TO operator. Execute all the three queries together. All the three queries will give the same result.
Query 1
SELECT *
FROM (
SELECT 1 AS Col
UNION ALL
SELECT 2) t
WHERE t.Col <> 1
Now let us check the execution plan. You will notice that execution plan converts Not Equal To operate to Equal To Operator and will compare the integer 2.
Query 2
SELECT *
FROM (
SELECT ‘1’ AS Col
UNION ALL
SELECT ‘2’) t
WHERE t.Col <> ‘1’
Now let us check the execution plan. You will notice that execution plan converts Not Equal To operate to Equal To Operator and will compare the integer 2.
Query 3
SELECT *
FROM (
SELECT ‘1’ AS Col
UNION ALL
SELECT ‘2’) t
WHERE t.Col <> 1
Now let us check the execution plan. You will notice that execution plan keeps Not Equal To operate as it is and compare it with 1 only.
Here is the question for you –
Why does the Query 3 does not convert Not Equal to Operator unlike Query 1 and Query 2?
If you know the valid answer please leave that in the comment section and I will publish it with due credit next week.
Reference: Pinal Dave (https://blog.sqlauthority.com)
10 Comments. Leave new
Because the returning type and the value used to compare the query are the same type on the first and second queries, the last query is using SELECT ‘1’ and comparing with 1 (string vs int) that’s the reason of the implicit conversion.
Saludos!
Jesus A. Ramirez
when values of both side of operator having same datatypes then implicit conversion not required, but in case of different datatypes its requires implicit datatype conversion.
in 3rd query Putting 1 inside single quotes in Select resulting it as a varchar by sql and in where claues it is comparing with 1 which is an int so it’s coverting the where value to int and comparing the result where as in Query 1 and 2 the Datatype is same varchar so it will always go for equal operator followed by or condition when records are more in number….
It’s simple like comparing two same Datatype always resulting with equal operator in the plan but if any conversion is happening there then it will keep the operator same which is in the where clause..
The reason is because there is an implicit conversion between int and char which is non deterministic and the optimization engine cannot make assumptions about the returned value. In the other examples the optimization engine is dealing with deterministic constants and can convert the relatively inefficient operator to an efficient = operator
Its is required to convert non-integer value to integer to compare with integer.
The function CONVERT_IMPLICIT(int,[Union1002],0)(1) is converting ‘2’ to integer before it is compared to the integer constant 1.
I think Myles is right.Implicit conversion between int and char which is non deterministic and the optimization engine cannot make assumptions about the returned value.
in 3rd query,anything inside single quotes is treated as “varchar(1)” (since,BY DEFAULT) by sql .
Here,’1′ in select clause is comparing which is of type “varchar” with 1 in where claues which is of type “int”.
so, there is an implicit conversion between int and varchar.
where as in Query 1 and 2 the Datatype is same varchar so it will always go for equal operator followed by or condition when records are more in number.
For table scan, engine takes the implicit values to be scanned in the table.
In case where implicit conversion requires, optimizer can not assume all the value may be converted to specific type. so it does not convert the where clause predicate.
The Query 3 uses the implicit conversion function to convert the character data type to an integer data type and thus it does not determine the outcome of return value i.e. its non deterministic. Thus it does not convert the not equal (”) operator.
Where as in other two queries, the optimization is getting deterministic values and converts relatively to an efficient equal (‘=’) operator.
Because the data types of the values for comparison operator is same in 1st & 2nd queries.