WHERE LOWER(description) LIKE '%plum%' OR '%chery%' || WHERE LOWER(description)-CodePudding

Whats the differences between these two? Both of them can run without errors but giving me different answer. Here's the full code for each of them.

SELECT DISTINCT winery
FROM winemag_p1
WHERE LOWER(description) LIKE '%plum%' OR '%chery%' OR '%rose%' OR '%hazelnut%'
ORDER BY winery

SELECT DISTINCT winery
FROM winemag_p1
WHERE LOWER(description) LIKE '%plum%'
   or LOWER(description) LIKE '%cherry%'
   or LOWER(description) LIKE '%rose%'
   or LOWER(description) LIKE '%hazelnut%'
ORDER BY winery

CodePudding user response：

OR doesn't distribute in programming languages like it does in English. LIKE has higher precedence than OR, so the first condition is equivalent to

WHERE (LOWER(description) LIKE '%plum%') 
    OR '%chery%' 
    OR '%rose%' 
    OR '%hazelnut%'

When you use a literal string in OR, it's converted to a number; any string that doesn't begin with a number is converted to 0, so this becomes equivalent to

WHERE (LOWER(description) LIKE '%plum%') OR 0 OR 0 OR 0

In boolean contexts, 0 is FALSE and non-zero is TRUE. So, since anything OR FALSE is equivalent to anything, the final result is that your condition is equivalent to just

WHERE LOWER(description) LIKE '%plum%'