CODEWITHSUNDEEP
sqlsql-servert-sqloperators
Ahmet Altun
Ahmet Altun

Reputation: 4039

Behavior of SQL OR and AND operator

We have following expression as T-Sql Query:

Exp1 OR Exp2

Is Exp2 evaluated when Exp1 is True? I think there is no need to evaluate it.

Similarly; for,

Exp1 AND Exp2

is Exp2 evaluated when Exp1 is false?

Upvotes: 2

Views: 13480

Answers (4)

RichardTheKiwi
RichardTheKiwi

Reputation: 107696

SQL Server query operators OR and AND are commutative. There is no inherent order and the query optimizer is free to choose the path of least cost to begin evaluation. Once the plan is set, the other part is not evaluated if a result is pre-determined.

This knowledge allows queries like

select * from master..spt_values
where (type = 'P' or 1=@param1)
  and (1=@param2 or number < 1000)
option (recompile)

Where the pattern of evaluation is guaranteed to short circuit when @param is set to 1. This pattern is typical of optional filters. Notice that it does not matter whether the @params are tested before or after the other part.

If you are very good with SQL and know for a fact that the query is best forced down a certain plan, you can game SQL Server using CASE statements, which are always evaluated in nested order. Example below will force type='P' to always be evaluated first.

select *
from master..spt_values
where
    case when type='P' then
        case when number < 100 then 1
    end end = 1

If you don't believe order of evaluation of the last query, try this

select *
from master..spt_values
where
    case when type='P' then
        case when number < 0 then
            case when 1/0=1 then 1
    end end end = 1

Even though the constants in the expression 1/0=1 is the least cost to evaluate, it is NEVER evaluated - otherwise the query would have resulted in divide-by-zero instead of returning no rows (there are no rows in master..spt_values matching both conditions).

Upvotes: 1

nvogel
nvogel

Reputation: 25526

SQL Server doesn't necessarily evaluate expressions in left to right order. Evaluation order is controlled by the execution plan and the plan is chosen based on the overall estimated cost for the whole of a query. So there is no certainty that SQL will perform the kind of short circuit optimisation you are describing. That flexibility is what makes the opimiser useful. For example it could be that the second expression in each case can be evaluated more efficiently than the first (if it is indexed or subject to some constraint for example).

SQL also uses three-value logic, which means that some of the equivalence rules used in two-value logic don't apply (although that doesn't alter the specific example you describe).

Upvotes: 2

Mitch Wheat
Mitch Wheat

Reputation: 300519

SQL Server sometimes performs boolean short circuiting, and sometimes does not.

It depends upon the query execution plan that is generated. The execution plan chosen depends on several factors, including the selectivity of the columns in the WHERE clause, table size, available indexes etc.

Upvotes: 0

Michael Petrotta
Michael Petrotta

Reputation: 60902

Unlike in some programming languages, you cannot count on short-circuiting in T-SQL WHERE clauses. It might happen, or it might not.

Upvotes: 8

Related Questions