I am using SQL Server.
This query:
SELECT 3/0 AS X returns an error, as expected:
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
But IF EXISTS treats X as a row that exists. This query:
IF EXISTS (SELECT 3/0 AS X) PRINT 'SUCCESS' ELSE PRINT 'FAILURE' returns 'SUCCESS'. I couldn't find anything in the IF EXISTS documentation that explains this behavior. Can anyone shed some light on this?
It does not exactly "ignore" errors specifically. It is ignoring everything in the select clause (with the exception of top (0)).
exists is checking to see if any rows are returned by the query. As an optimization, it ignores the select part of the query, because this does not affect the existence of rows.
Incidentally, you get the same effect with case:
select (case when 1=1 then 1 else 1/0 end) also returns 1 rather than an error.
The expression in the SELECT statement does not need to be evaluated in order for SQL Server to process the EXISTS predicate. Look at the execution plan to see what the optimizer does with this query:
Note the VALUES returned by the scalar operator are not those that were specified in the SELECT clause. No error occurs because the actual expression used in the query does not divide by zero.
