CODEWITHSUNDEEP
mysqlsql
Rei
Rei

Reputation: 841

"!= or !=" selects everything

Let's say I have a table with the following contents: (just some random values)

year
2005
2009
2010
2912
2982
2947

When I query:

SELECT userid FROM tbluser WHERE year!=2005 or year!=2010

It will output all the years. Why is that?

Upvotes: 0

Views: 126

Answers (4)

Sebastian Mach
Sebastian Mach

Reputation: 39109

The other answers suggest what to do already, but I'd like you to make the test yourself (funnily, I am doing it now...):

You have 

WHERE year!=2005 or year!=2010

Now, check some number for list against that condition. E.g., use 2011:

WHERE 2011!=2005    <-- indeed 2011 is not equal to 2005, so this is TRUE
   or 2011!=2010    <-- indeed 2011 is not equal to 2010, so this is TRUE

insert the preliminary results into your condition:

WHERE TRUE
   or TRUE          <-- if something is TRUE or TRUE, then surely the result
                        is TRUE as well

If you think this further, then the condition is true for every value on the world, because a single value can't be equal to multiple, different values.

E.g., use 2005:

WHERE 2005!=2005    <-- 2005 _is_ equal to 2005, so this is FALSE
   or 2005!=2010    <-- 2005 is not equal to 2010, so this is TRUE

then

WHERE FALSE
   or TRUE          <-- Read: Where FALSE is true or TRUE is true, summa TRUE.

Upvotes: 3

Haegin
Haegin

Reputation: 530

If the year is 2005 then it won't be 2010 thus the condition will evaluate to true. If the year is 2010 then it won't be 2005 thus the same will happen. If the year is neither both will evaluate to true. You probably mean to use and instead of or.

Upvotes: 3

James Kingsbery
James Kingsbery

Reputation: 7496

I think what you want is 

SELECT userid FROM tbluser WHERE year!=2005 AND year!=2010

What you are looking to say is that the year cannot be 2005 or 2010 (!(year==2005 OR year==2010)) which is logically equivalent to (year!=2005 AND year !=2010) (See De Morgan's Law)

Upvotes: 9

Haim Evgi
Haim Evgi

Reputation: 125584

the year cant be 2005 and 2010 so the or condition return true always

what you need is to use and

SELECT userid FROM tbluser WHERE year!=2005 and year!=2010

or to use not in

SELECT userid FROM tbluser WHERE year not in (2005,2010)

Upvotes: 0

Related Questions