Short CIrcuit in WHERE clause [duplicate]

Posted on

Question :

How to make short circuit in where clause, e.g:

select 1 where 1=2 AND 1/0=0

In the above example 1/0=0 condition should not be evaluate as the first condition is false.

Answer :

Just like ypercube commented, Sql Server doesn’t adhere to similar a boolean evaluation as, say, Java or C#. There are lots of articles about this, but facts are a bit hard to find.

As per the documentation:

The order of evaluation of logical operators can vary depending on
choices made by the query optimizer.

This behavior is caused by the fact that SQL is a declarative language. In such a language one describes the desired output, not the steps how to build the output. It is up to the RDBMS how to build the output. This often is counter-intuitive to programmers used to procedural and OOP approach, in which one tells explicitly step-by-step what the application should do.

If you got queries that mis-behave with boolean logic evaluation order assumption, I’m afraid you got to re-write those.

Leave a Reply

Your email address will not be published. Required fields are marked *