Oracle query: WHERE NOT LIKE is not excluding record when only one record exists

I have the following query

select cand_id 
from cand_kw
WHERE client_id='mamasandpapas' AND UPPER(kw) LIKE '%MARAH%' OR UPPER(kw) LIKE '%ANDREW%' AND UPPER(kw) NOT LIKE '%KOCH%'

In the database there are records with the following:

Andrew Postings
Andrew Postings
Andrew McDee
Marah Koch

So there are three rows with and Andrew in it but only one record with a Marah in it.

if I change my query to the following then the two Andrew Postings are excluded correclty

select cand_id
from cand_kw
 WHERE client_id='mamasandpapas' AND UPPER(kw) LIKE '%MARAH%' OR UPPER(kw) LIKE '%ANDREW%' AND UPPER(kw) NOT LIKE '%POSTINGS%'

So it looks like if only one record exists then the results are returned even though they should be excluded.

This is using a very old version of Oracle (9i) and the field its search (kw) stands for keywords and is a CLOB containing a bunch of keywords.

This is a very old system not of my design.

Can anybody explain in the first case why Marah Koch is not being excluded and the Andrew Postings records are?

Solution

The way it's written, the NOT LIKE "%KOCH%" will only evaluate when LIKE "%ANDREW%" evaluates to True. Here's the flow of your original WHERE clause in regards to the record with Marah Koch.

First SQL sees if client_id is 'mamasandpapas'. It is, great! Next, it sees an AND, meaning it whatever follows must also be true for the WHERE clause to be true, luckily, Upper(kw) is indeed LIKE '%MARAH%'. Then it sees OR next. SQL decides it can ignore the rest because if any expression on either side of OR evaluates to true, then it's done evaluating because Upper(kw) LIKE '%MARAH%' has already evaluated to true, so it doesn't even get to Upper(kw) NOT LIKE '%KOCH%'.

Likewise, in your second WHERE clause example, it seems to work only because in the cases of Andrew Postings, after the client_id evaluates to true, the AND says see if Upper(kw) is LIKE'%MARAH%', oh it's not, well now we move to the other side of the OR, Upper(kw) is LIKE '%ANDREW%' but wait, now I see an AND which means that both Upper(kw) LIKE'%ANDREW%' AND whatever is on the other side need to be true for the WHERE to be true. If you add a record to your table of Marah Postings, it will still be returned in the second example.

The terminology I learned for this was "Naked ORs". In other words, it is probably best to "dress" any OR statements with parentheses for both readability and because that's how most people understand the logic of ANDs and ORs.