using OR in SQL statement

Question

I have an issue with my select statement; basically, it goes something like

SELECT * FROM table WHERE colA LIKE '%test%' OR colA LIKE '%hold%' AND colb > 1

The colB does not evaluate unless I remove the "OR colA LIKE '%hold%'". Wondering how to make the OR apply only to the colA evaluation. I realize I could make OR the last evaluation but wanted to understand how to better work with OR for future sql statements.

It does work if I put OR at end.

SELECT * FROM table WHERE colA LIKE '%test%' OR colA LIKE '%hold%' AND colb > 1

No errors occur; just can't evaluate anything after OR statement and sometimes need to employ OR when using wildcards

Answer 1

AND has a higher precedence than OR. So as written, the condition breaks down into two independent clauses:

colA LIKE '%test%'

and

colA LIKE '%hold%' AND colb > 1

A row will be returned if it matches either of these clauses.

To get the result you want, use parentheses to group the conditions:

(colA LIKE '%test%' OR colA LIKE '%hold%') AND colb > 1

Answer 2

The command OR must be used with care because it can break all logic. It should generally be used in parentheses to configure the order of execution.

Example:

I want to select oranges or apples that cost more than $100:

SELECT
    *
FROM fruits
WHERE (name = 'orange' OR name = 'apple')
AND value > 100.