2

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 ?

  • 2
    it interprets it as NULL...and NULL is not equal to 1... – Lamak Jan 31 '18 at 14:04
  • 2
    If the sub query returns more than one row the original will raise an error. EXISTS wont. So they aren't always the same. If the sub query is correlated and guaranteed to return 0 or 1 rows per outer row you might also consider outer join and checking for null in the CASE. – Martin Smith Jan 31 '18 at 14:19
10

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.