CODEWITHSUNDEEP
phpmysql
Robert
Robert

Reputation: 10390

MySql Or statement functionally not making sense

Why is it that the MySql "or" operator behaves like an "and" operator?

Example:

mysql>SELECT * FROM employee_tbl 
    ->WHERE daily_typing_pages= 250 OR  
    ->daily_typing_pages= 220 OR  daily_typing_pages= 170; 
+------+------+------------+--------------------+
| id   | name | work_date  | daily_typing_pages |
+------+------+------------+--------------------+
|    1 | John | 2007-01-24 |                250 |
|    2 | Ram  | 2007-05-27 |                220 |
|    3 | Jack | 2007-05-06 |                170 |
|    4 | Jill | 2007-04-06 |                220 |
+------+------+------------+--------------------+

why does it select all 3 records. Shouldn't it return the first match and be done with it? My mind right now is telling me that the "and" operator is the one that should by nature return all 3 rows. This behavior is different than how programming languages like PHP work. Any references to official documentation would be helpful or links to how the OR and the AND work would help too.

Upvotes: 0

Views: 34

Answers (2)

John Conde
John Conde

Reputation: 219804

All three records match the criteria you set in your WHERE statement as those rows have a daily_typing_pages value of 250, 220, or 170. In other words, any row that matches any of those values will be returned.

If you used AND none of those rows would be returned as none of them have a value of 250 and 220 and 170.

Upvotes: 1

skrilled
skrilled

Reputation: 5371

No, just read the query out loud like this.

I want everything from my employee table if daily typing pages is 250, or if daily typing pages is 220, or if daily typing pages is 170.

If you told me to get you all drinks from 7-11 that are either orange, blue, or green. I'd bring you back all those drinks.

If you want to limit, you need to explicitly state it:

SELECT * FROM employee_tbl 
    ->WHERE daily_typing_pages= 250 OR  
    ->daily_typing_pages= 220 OR  daily_typing_pages= 170 LIMIT 1;

Upvotes: 2

Related Questions