6

In a where clause, if the first condition is FALSE, then the second condition is checked in SQL Server?

For example

select * 
from users
where (condition 1) AND (condition 2)

If condition 1 is False, will condition 2 be checked?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Morteza Jangjoo
  • 1,780
  • 2
  • 13
  • 25
  • 1
    There's no guarantee in SQL Server if or in which order the statements will be processed in a WHERE clause, [Details](https://dba.stackexchange.com/a/5337/123611) – ahmed abdelqader Mar 15 '20 at 09:03

3 Answers3

3

It is non-deterministic. You cannot rely on order of evaluation.

SQL Server query optimizer is responsible for parsing T-SQL query and creating execution plan based on table sizes, indexes, etc. So same T-SQL statement against same database schema might be executed differently depending on database content, configuration, etc.

Query optimizer prioritizes speed of execution and is able to parallelise execution of a single T-SQL statement. It is highly unlikely that execution of filter clauses ends up being executed one after another.

Nenad
  • 24,809
  • 11
  • 75
  • 93
2

Regardless of priority, the conditions will check until first false...

Example:

select 'Divide by zero error.' 
where 1=1 and (1/0 = 1)


select 'No Divide by zero error' 
where 1=0 and (1/0 = 1)
Mahmoud Amini Arjmand
  • 79
  • 4
  • This answer is incorrect. You will find, if you reverse the conditions, the result will be the same. My guess is the optimiser can tell 1=0 is false without even checking the other condition, even if it comes after the AND. – Fletcher Johns Jun 30 '23 at 01:37
1

SQL is declarative. Not procedural.

The optimiser may use short circuit evaluation or may not.

It also may convert that to something different. If column is integer type all three conditions collapse to

WHERE column > 1

Similarly the optimiser might find some conditions are guaranteed to be true or false by check constraints or other predicates in the query and don't need evaluating at all. Or use an access path (such as filtered index) that guarantees all rows match a part of the predicate so no run time evaluation is needed. Read This

Peter Lakatus
  • 46
  • 2