Do modern DBMS include short-circuit boolean evaluation?

Question

Many modern programming languages have short-circuit boolean evaluation such as the following:

if (x() OR y())

If x() returns true, y() is never evaluated.

Does SQL on modern DBMS (SQL Server, Sybase, Oracle, DB2, etc) have this property?

In particular if the left side of the boolean statement is a boolean constant, will it be short circuited?

---

Related: Do all programming languages have boolean short-circuit evaluation?

Answer 1

Yes and no.

(Below refers to SQL Server exclusively)

Some operators short circuit and some don't. OR CAN short circuit, but may not depending on the order of operations selected by the query engine.

CASE is (I believe) 100% guaranteed to short-circuit.

You can also try to force order of evaluation with nested parentheses, like:

IF ((X) OR Y)

But I'm not positive this is always consistent either.

The trouble with SQL in this regard is it's declarative, and the actual logic is performed by the engine. It may in fact be more efficient to check for Y first from your example and then check for X - if, for instance, Y is indexed and X requires a table scan.

For Reference:

From the ANSI-SQL documentation from this answer:

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.

Answer 2

Speaking specifically for SQL Server - sort of.

The ordering in which you specify your OR statements can't guarantee short-circuiting because the optimizer can re-order them at-will if it feels better performance gains can be made by doing so.

However, the underlying engine itself can and will short-circuit. It's just something that the user can't control.

The following article (which links to other excellent discussions/resources) has more on this topic: http://weblogs.sqlteam.com/jeffs/archive/2008/02/22/sql-server-short-circuit.aspx