CODEWITHSUNDEEP
mysqllogical-operatorsshort-circuiting
Norlihazmey Ghazali
Norlihazmey Ghazali

Reputation: 9060

Logical expression in Mysql statement not giving desired result

Okey, first of all, let me draw the related table and dummy data that being used before going further(simple table structure to re-produce the issue):

|----------------------------------------------|
|users                                         |
|----------------------------------------------|
|ID | name            | email                  | 
|----------------------------------------------| 
|1  | jack jill       | [email protected]     |
|----------------------------------------------|                  
|2  | jack jacky      | [email protected]    |
|----------------------------------------------|

And here is mysql query statement being used :

SELECT *
FROM `users`
WHERE `ID` = 1
AND  `name` LIKE '%jacky%'
OR  `email` LIKE '%jacky%'

Expected result : Row no 1

Actual result : Row no 2

Question :

1) Is the logical expression in above statement do the short circuit evaluation?

2) If yes( from question no 1 ), why it not return the 1st row as the first expression already evaluated to TRUE.

3) If not( from question no 1 ), which expression its evaluated and at what order the operator precedence occurred?

All these are playing in my mind right now, and i'm really confuses on how it evaluated. Even it just a simple SQL statement. Really appreciated if someone can explain deep down about this.

Upvotes: 0

Views: 84

Answers (1)

trincot
trincot

Reputation: 350242

In SQL, the AND operator has precedence over OR. So you probably want to override that with parentheses like this:

SELECT *
FROM `users`
WHERE `ID` = 1
AND  (   `name` LIKE '%jacky%'
     OR  `email` LIKE '%jacky%'
     )

In your version the last condition (i.e. email LIKE '%jacky%') was enough to get a match. You could imagine the default precedence like this:

SELECT *
FROM `users`
WHERE (     `ID` = 1
       AND  `name` LIKE '%jacky%'
      )
OR    `email` LIKE '%jacky%'

With the corrected version there will in fact be no more match, as there is no jacky in the name field of the first row, nor in its email field.

Upvotes: 2

Related Questions