Compound "OR" evaluation in DB2

Question

I've searched the forums and found a few related threads but no definitive answer.

(case
when field1 LIKE '%T001%' OR field1 LIKE '%T201%' OR field1 LIKE '%T301%'...

In the above statement, if field1 is "like" t001, will the others even be evaluated?

(case
when (field1 LIKE '%T001%' OR field1 LIKE '%T201%' OR field1 LIKE '%T301%')...

Does adding parenthesis as shown above change the evaluation?

Answer 1

In general, databases short-circuit boolean operations. That is, they stop at the first value that defines the result -- the first "true" for OR, the first "false" for AND.

That said, there are no guarantees. Nor are there guarantees about the order of evaluation. So, DB2 could decide to test the middle, the last, and then the first. That said, these are pretty equivalent, so I would expect the ordering to be either first to last or last to first.

Remember: SQL is a descriptive language, not a procedural language. A SQL query describers the result set, but not the steps used to generate it.

Answer 2

You don't know.

SQL is a declarative language, not an imperative one. You describe what you want, the engine provides it. The database engine will decide in which sequence it will evaluate those predicates, and you don't have control over that.

If you get the execution plan today it may show one sequence of steps, but if you get it tomorrow it may show something different.