Many modern programming languages have short-circuit boolean evaluation like the following:
if (x() OR y())
x() returns true,
y() isn't examined.
Does SQL on modern DBMS (SQL Server, Sybase, Oracle, DB2, etc) have this property?
Particularly when the left side from the boolean statement is really a boolean constant, could it be short circuited?
Speaking particularly for SQL Server - kind of.
The ordering that you specify your OR claims can't guarantee short-circuiting since the optimizer can re-organize them at-will whether it feels better performance gains can be created in so doing.
However, the actual engine itself will short-circuit. It is simply something which the consumer can't control.
The next article (which links with other excellent discussions/assets) has more about this subject: http://weblogs.sqlteam.com/jeffs/archive/2008/02/22/sql-server-short-circuit.aspx
SQL Server solely)
Some operators short circuit plus some don't.
OR CAN short circuit, but might not with respect to the order of procedures selected through the query engine.
CASE is (In my opinion) 100% certain to short-circuit.
You may also attempt to pressure order of evaluation with nested parentheses, like:
IF ((X) OR Y)
But I am not positive this really is always consistent either.
The problem with SQL in connection with this is it's declarative, and also the actual logic is carried out through the engine. It might actually become more efficient to check on for
Y first out of your example after which look for
X - if, for example,
Y is indexed and
X takes a table scan.
In the ANSI-SQL documentation from this answer:
In which the priority isn't based on the Formats or by parentheses, effective evaluation of expressions is usually carried out from left to right. However, it's implementation-dependent whether expressions are really examined left to right, particularly when operands or operators could potentially cause conditions to become elevated or maybe the outcomes from the expressions can be established without completely evaluating every part from the expression.