I'm pretty sure this is a implicit conversion but I wasn't able to find information about it.
Suppose I have this SQL:
SELECT CASE WHEN (SELECT 1 WHERE (1=1)) = 1 THEN 1 ELSE 0 END
My subquery is returning one row and hence compared to 1. This leads my CASE WHEN to produce the 1 output.
Even if I set the subquery to return no rows like the sample below, the CASE statement returns the 0 result:
SELECT CASE WHEN (SELECT 1 WHERE (1=0)) = 1 THEN 1 ELSE 0 END
- Does the SQL interpreter make a conversion between "empty set" to 0 or 1 ?
I don't feel comfortable using this type of comparison. In my humble opinion, it's better to use EXISTS like this:
SELECT CASE WHEN EXISTS(SELECT 1 WHERE (1=1)) THEN 1 ELSE 0 END
Is there any advantage or disadvantage if I don't use EXISTS ?