3

Possible Duplicate:
Is the SQL WHERE clause short-circuit evaluated?

I have the following question regarding this query:

select * from sometable 
where 1=1 or (select count(*) from table2 > 0)

If the first condition is true (1=1), does SQL Server still do the inner select? Or does it stop when the first condition is true (like C)

Community
  • 1
  • 1
francisco
  • 83
  • 1
  • 1
  • 3

4 Answers4

7

It stops when the (1=1) is true. You can check easily using Ctrl-M then Ctrl-E

Consider this query

select * from master..spt_values
where 1=1 or (select count(*) from master..sysobjects) > 0

The execution plan only shows a scan in master..spt_values and no activity in sysobjects.

Contrary to C, it does not stop when the LEFTMOST condition is true, instead the query optimizer works out independently of order presented which is the least cost to evaluate. In the case of the constants 1 vs 1 the winner is clear.

RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • But is this due to the fact that it's a constant comparison that can be optimized to always evaluate to true? I get the feeling that the OP is asking the broader question of whether or not the `where` clause short circuits. – Adam Robinson Feb 17 '11 at 18:23
  • @Adam I get that impression too - this query form is commonly used for optional parameters – RichardTheKiwi Feb 17 '11 at 18:25
5

It is short circuited only because you are comparing literals. The optimiser detects this trivial comparison.

But if you have forced parameterisation set then the literals are changed to parameters and SQL Server works on a general case that each side could be different. Subject to the long list of conditions for forced parameterisation...

gbn
  • 422,506
  • 82
  • 585
  • 676
1

As a general rule, SQL Server does not guarantee boolean operator short-circuit. Do not write queries that rely on boolean short-circuit for performance or correctness.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
0

I will point out that you may get different results if you say where 1=1 or id = 10, I know I added the 1=1 to a query and got different results. I would not use 1=1 with an OR clause under any circumstances.

HLGEM
  • 94,695
  • 15
  • 113
  • 186