Sql-server – Empty result set and Case When = 1

casesql server

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 ?

Best Answer

Does the SQL interpreter make a conversion between "empty set" to 0 or 1 ?

No, The query didn't return 0 because the empty result is considered equal to 0 but because the empty result set is considered NULL and null isn't equal to anything.

As you found out

SELECT
CASE WHEN (SELECT 1 WHERE (1=0)) = 1 THEN 1 ELSE 0 END

Returns 0

But 

SELECT
CASE WHEN (SELECT 1 WHERE (1=0)) = 0 THEN 1 ELSE 0 END

Also returns 0.

So the empty result set is not 1 and not 0, it's NULL as evidenced by

SELECT
CASE WHEN (SELECT 1 WHERE (1=0)) is NULL THEN 1 ELSE 0 END

which returns 1.

See this dbfiddle with that code.

NULL is unknown so SQL Server doesn't know what it's equal to.

In my humble opinion, it's better to use EXISTS

If you rely on the existence of a result set, I agree EXISTSis probably the way to go. That's what the construction is made for.
If you want to compare 2 values off course you'll need to use CASE but for your stated scenario I'd use EXISTS

Your comparison will break if your result set has more than one row or column.

For example

SELECT
CASE WHEN (SELECT 1,2 WHERE (1=1)) = 1 THEN 1 ELSE 0 END

returns

Msg 116 Level 16 State 1 Line 2 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

and 

SELECT
CASE WHEN (SELECT 1 WHERE (1=1) UNION SELECT 2 WHERE (1=1)) = 1 THEN 1 ELSE 0 END

returns

Msg 512 Level 16 State 1 Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Related Question