Question :
Does the SQL Server (2008 or 2012, specifically) CASE
statement evaluate all the WHEN
conditions or does it exit once it finds a WHEN
clause that evaluates to true? If it does go through the entire set of conditions, does that mean that the last condition evaluating to true overwrites what the first condition that evaluated to true did? For example:
SELECT
CASE
WHEN 1+1 = 2 THEN'YES'
WHEN 1+1 = 3 THEN 'NO'
WHEN 1+1 = 2 THEN 'NO'
END
The results is “YES” even though the last when condition should make it evaluate to “NO”. It seems that it exits once it finds the first TRUE condition. Can someone please confirm if this is the case.
Answer :
•Returns the result_expression of the first input_expression = when_expression that evaluates to TRUE.
Reference
https://docs.microsoft.com/sql/t-sql/language-elements/case-transact-sql
This is standard SQL behaviour:
-
A
CASE
expression evaluates to the first true condition. -
If there is no true condition, it evaluates to the
ELSE
part. -
If there is no true condition and no
ELSE
part, it evaluates toNULL
.
SQL Server usually does short-circuit evaluation for CASE statements (SQLFiddle):
--Does not fail on the divide by zero.
SELECT
CASE
WHEN 1/1 = 1 THEN 'Case 1'
WHEN 2/0 = 1 THEN 'Case 2'
END;
--Fails on the divide by zero.
SELECT
CASE
WHEN 1/1 = 99 THEN 'Case 1'
WHEN 2/0 = 99 THEN 'Case 2'
END;
There are however several types of statements that as of SQL Server 2012 do not correctly short-circuit. See the link from ypercube in the comments.
Oracle always does short-circuit evaluation. See the 11.2 SQL Language Reference. Or compare the following (SQLFiddle):
--Does not fail on the divide by zero.
SELECT
CASE
WHEN 1/1 = 1 THEN 'Case 1'
WHEN 2/0 = 1 THEN 'Case 2'
END
FROM dual;
--Fails on the divide by zero.
SELECT
CASE
WHEN 1/1 = 99 THEN 'Case 1'
WHEN 2/0 = 99 THEN 'Case 2'
END
FROM dual;
This same test can’t be done with MySQL because it returns NULL for division by zero. (SQL Fiddle)
It appears that MS SQL Server uses a short-circuit evaluation also.
In the following test I have 3 tests. The first one is always true, the second one fails without referencing the table, and the third fails only when the data is taken into account.
In this particular run both rows are returned successfully. If I comment out the first WHEN, or the first and second then I get failures.
CREATE TABLE casetest (test varchar(10))
GO
INSERT INTO casetest VALUES ('12345'),('abcdef')
GO
SELECT CASE WHEN LEN(test)>1 THEN test
WHEN 1/0 = 1 THEN 'abc'
WHEN CAST(test AS int) = 1 THEN 'def'
END
FROM casetest
GO
In MySQL it will exit the case statement on the first true option. If you have the possibility of multiple true values you want to place the preferred answer earlier in the sequence.
if the case statement used in the WHERE
condition and the first case when statement involve evaluating column values from the table, and the first row in the table does not satisfy this condition, the case statement will go to next case when statement.
declare @tbl table(id int)
insert into @tbl values(1)
insert into @tbl values(2)
insert into @tbl values(3)
--Fails on the divide by zero.
SELECT * FROM @tbl
where CASE
WHEN id = 2 THEN 1 -- first row in table will not satisfy the condition
WHEN 2/0 = 1 THEN 1
ELSE 0
END =1
-- when filter the records to only who will staisfy the first case when condition, it
will not fail on the divide by zero
SELECT * FROM @tbl
where ID=2 and -- first row in table will satisfy the condition
CASE
WHEN id = 2 THEN 1
WHEN 2/0 = 1 THEN 1
ELSE 0
END =1