Is the SQL WHERE clause short-circuit evaluated?
Are boolean expressions in SQL WHERE clauses short-circuit evaluated ?
For example:
SELECT *
FROM Table t
WHERE @key IS NULL OR (@key IS NOT NULL AND @key = t.Key)
If @key IS NULL
evaluates to true
, is @key IS NOT NULL AND @key = t.Key
evaluated?
If no, why not?
If yes, is it guaranteed? Is it part of ANSI SQL or is it database specific?
If database specific, SQLServer? Oracle? MySQL?
ANSI SQL Draft 2003 5WD-01-Framework-2003-09.pdf
6.3.3.3 Rule evaluation order
[...]
Where the precedence is not determined by the Formats or by parentheses, effective evaluation of expressions is generally performed from left to right. However, it is implementation-dependent whether expressions are actually evaluated left to right, particularly when operands or operators might cause conditions to be raised or if the results of the expressions can be determined without completely evaluating all parts of the expression.
From the above, short circuiting is not really available.
If you need it, I suggest a Case statement:
Where Case when Expr1 then Expr2 else Expr3 end = desiredResult
Expr1
is always evaluated, but only one of Expr2
and Expr3
will be evaluated per row.