CODEWITHSUNDEEP
mysqlwhere-clause
Vendetta
Vendetta

Reputation: 97

Difference between OR , AND with where in mysql

i'm learning Mysql

but i felt confused after the examples below :

select * from `users` where username = 'admin' or true;

here it returned all the rows in users table !

(username = 'admin' or true ) should be true ? so where true

but in this example :

select * from `users` where username = 'admin' and true;

it returned one row (where username = 'admin')

but (username = 'admin' and true) should be true too !

so what's is the difference?

Upvotes: 0

Views: 83

Answers (4)

davejal
davejal

Reputation: 6133

you should read it differently.

the clause checks to see if username equals 'admin'.

read it as follows

(username = 'admin') and (1)

and for the or

(username = 'admin') or (1)

so the second will return all values in your db, because it checks if condition 1 or 2 is met. And condition 2 is always true.

WHERE 1

Upvotes: 3

steffen
steffen

Reputation: 16948

-- this is always true
WHERE 1 

-- this is only true for rows where the username is admin
WHERE username = 'admin'

Now check this truth table:

x  y | x and y | x or y
-----------------------
F  F |    F    |   F
F  T |    F    |   T
T  F |    F    |   T
T  T |    T    |   T

https://en.wikipedia.org/wiki/Boolean_algebra

If you take

  • x for WHERE username = 'admin' and
  • y for WHERE 1

you should understand the results.

Upvotes: 6

AntDC
AntDC

Reputation: 1917

In a room there are 10 people.
1 is a woman, the rest are men.

How many in the room are People OR Women = 10. 
How many in the room are People AND Women = 1. 
How many in the room are People AND Men = 9. 
How many in the room are Men OR Women = 10. 
How many in the room are Men AND Women = 0.       
  In todays day and age, this is questionable ;)

Still I hope it helps

Upvotes: 1

Nir Levy
Nir Levy

Reputation: 12953

Mysql considers 1 as true so it is simple logic- expression OR true will always be true (so you get all rows) and expression AND true is equivalent to expression, so you get only the rowa that meets your condition

It looks a bit wierd, but mysql checks the expreesion (including the 1) fpr every row, even though 1 is not part of the table. It is still considered wheb decideling whether to select each row

Upvotes: 1

Related Questions