2

I searched a lot for this simple operation. But I could not get an answer. I have a simple query:

select column1, column2... 
from table1 
where condition1 and condition2 and condition3

The above query will returns some rows if all the conditions are true.

My question is whether it will give result as 'Fail' once it found the first condition (condition1) is failed?

The execution of rest of the operations is meaning less and it will take more time if the second and third conditions have huge operations.

Can you please explain this operations?

jarlh
  • 42,561
  • 8
  • 45
  • 63
Arun Kumar T
  • 620
  • 4
  • 12
  • 26
  • 4
    It's up to the optimizer, but you can trust it that it will understand that evaluating multiple conditions when the first already failed is unnecessary. Even the [order of the conditions should be irrelevant](http://stackoverflow.com/questions/11436469/does-order-of-where-clauses-matter-in-sql) for you. – Tim Schmelter Dec 09 '15 at 13:25
  • 2
    [On SQL Server boolean operator short-circuit](http://rusanu.com/2009/09/13/on-sql-server-boolean-operator-short-circuit/) - [Don’t depend on expression short circuiting in T-SQL](http://blogs.msdn.com/b/bartd/archive/2011/03/03/don-t-depend-on-expression-short-circuiting-in-t-sql-not-even-with-case.aspx) – Martin Smith Dec 09 '15 at 13:28

2 Answers2

3

The operation that you are referring to is short-circuiting. As far as I know, most SQL databases do implement this. That is, they stop evaluating clauses when the result of the where condition is known.

This comes with a caveat though: they do not guarantee the order of evaluation. So, the optimizer might evaluate them in any order. Note: you should not worry about simple comparisons. The only issue of performance would come with the use of functions (or perhaps like expressions on long strings), especially user-defined functions.

If you think you are smarter than the optimizer (which is quite possible), you can use case:

where (case when not (condition1) then 0
            when not (condition2) then 0
            when not (condition3) then 0
            else 1
       end) = 1

However, I would discourage this for two key reasons:

  • It prevents the optimizer from using an index.
  • The code is much less understandable.

In general, these types of optimizations are not important for query performance. Other issues, such as having well-defined indexes and proper data types are usually much more important.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

You may check execution plan, there is an option in SQL Server management studio.

Edit: Reference: How do I obtain a Query Execution Plan?

Community
  • 1
  • 1
Ovais Khatri
  • 3,201
  • 16
  • 14