SQL 'Or' operator. How does it work in the following scenario?

Question

I've been working on optimizing a query and have ran into a situation that's making me question how I've always used SQL's OR operator. (SQL Server 2000 also)

I have a query where the conditional (WHERE) clause looks something like this:

WHERE (Column1 = @Param1 or Column1 LIKE @Param1 + '%')
AND (@Param2 = '' OR Column2 = @Param2 OR Column2 LIKE @Param2 + '%')

Now, I've always understood that OR in SQL evaluated both expressions. So all records that evaluated true for the left expression would be returned along with all records that evaluated true on the right expression. For example:

SELECT * FROM TABLE1
WHERE COL1 = 'Test' OR Col2 = 'Data'

This would return back all records where COL1 is'Test' as well as any record where Col2 is 'Data'

In the example above, I modified the Column2 conditional to the following:

AND(Column2 LIKE ISNULL(@Param2, '') + '%')

All of the sudden, I get 0 rows returned.

Have I been mistaken in that OR only evaluates expressions until it find a TRUE result or is there a condition that would cause the 2 different to return different results?

Answer 1

"OR only evaluates expressions until it find a TRUE result"

It only has to, but that's not your problem (actually this is what was saving you in your original case). Your two queries are not really equivalent.

I'm thinking you have NULLs in Column2 which will never cause (Column2 LIKE ISNULL(@Param2, '') + '%') to be true - and in your original version the @Param2 = '' was masking this case, since it IS true (sometimes)

Perhaps:

(ISNULL(Column2, '') LIKE ISNULL(@Param2, '') + '%')

Remember the three-valued logic for NULLs:

TRUE and UNKNOWN: UNKNOWN
TRUE or UNKNOWN: TRUE

FALSE and UNKNOWN: FALSE
FALSE or UNKNOWN: UNKNOWN

But I'm not sure your optimization is really helping.

Answer 2

OR is not all-encompassing, especially as it's in parentheses. What you have in a larger since is: WHERE X AND Y. That fact that X and Y are themselves boolean expressions that make use of an OR is not important: they are evaluated separately and then results are fed to the AND operator.

[edit]:
Reading again, I may have misunderstood your question. With that in mind, I'll have to go with the other answer, because NULL LIKE '%' returns NULL, which is the same as false in this case. You might try this instead:

COALESCE(Column2,'') LIKE COALESCE(@param2,'') + '%'

Answer 3

MS-SQL will evaluate the left hand side first and not proceed unless it needs to.

This is the same with the AND connector, the left side will be evaluated and if false the right will not be evaulated.

Answer 4

FYI, there are very simple experiments you can do to see that not all conditions are necessarily evaluated.

I did this in Oracle but I expect you would have a similar result in SQL Server.

dev> select * from dual where 1=1 or 1/0 = 3;

D
-
X

The condition after the OR must not have been evaluated, since it would raise a divide-by-zero error.

This handling of boolean operators is generally known as "short-circuiting" and, AFAIK, is pretty standard in modern languages. It can also apply in an AND expression -- if the first condition is false, there is no point in evaluating the second condition, since the whole expression cannot possibly be TRUE.

More info: http://en.wikipedia.org/wiki/Short-circuit_evaluation

Anyway as Cade said, your real problem is probably mishandling of NULLs.