MySQL not equals to isn't working

Question

This is my code:

$query = mysql_query("SELECT P_Id, Thing,Something Date
    FROM priv
    WHERE Something = '$thing' AND thing2 <> 'This'
    LIMIT 1"
) or die(mysql_error());

Code is working properly without "thing2 <> 'this'" statement, but when I implement "thing2 <> 'this'", it returns 0 result. As if there were no empty rows in the table, but there are empty (NULL) rows in the thing2 row of the table.

The problem is, that this "not equals" statement is not working, and I've tried everything, but it won't return any values with it.

edit(word of explanation): Columns looks like this - P_Id | Something | Date | Thing1 | Thing 2 and i need to receive P_Id from a row where Something = Something AND Thing1 NOT EQLAS to 'this'(above example) and Thing2 ALSO NOT EQUALS to 'this'.I know it sounds crazy.Sorry

Ok, so i have tried some experiments, and it apears that "thing2 <>" thingy is not working on things1/thing2 columns, but other columns like "Something" table where are also 'this' entries it works properly(finding nerest row where there is not 'this' entry).But why this isn't working with things1 and 2???i have tried rename things column, but no result.

@user2107321: no, the question does **not** contain all the information. You *could* produce a small sample table, tell us the content and show us the result of a test query. That's called a [SSCCE](http://sscce.org) (be sure to follow and read that link!) and it's a *very good idea* both for finding the problem yourself *and* for being able to communicate the problem concisely. — Joachim Sauer, Mar 19 '13 at 14:30
table looks like this - P_Id | Something | Date | Thing1 | Thing 2 and i need to receive P_Id from a row where Something = Something AND Thing1 NOT EQLAS to 'this'(above example) and Thing2 ALSO NOT EQUALS to 'this'.I know it sounds cray.Sorry — user2107321, Mar 19 '13 at 14:44
Ok, did you look at the SSCCE site I linked to? Please do so again. 1.) create a **new** table that has some of the necessary columns. 2.) add *a few* rows, that look similar to the actual data (with some `NULL` and empty string values). 3.) try your query (in a modified form of course). 4.) **if** you manage to reproduce your problem, post **all** the information of steps #1-#3 here. 5.) if you *can't* reproduce it, find out what's the difference between the new toy table/query and the original one. Good luck. — Joachim Sauer, Mar 19 '13 at 16:03

score 8 · Answer 1 · edited May 23 '17 at 12:28

8

something <> NULL will evaluate to NULL. And if used in a query that's pretty much the same as 0 (or false).

Remember that SQL uses three-valued logic and not simple binary logic.

You might need to check for NULL explicitly using IS NULL:

... OR THING IS NULL

Alernatively you can use the NULL-safe equals operator <=> with a negation (this is MySQL specific, however, it's not standard SQL):

... AND NOT (THING <=> 'This')

edited May 23 '17 at 12:28

Community

1
1

answered Mar 19 '13 at 14:21

Joachim Sauer

302,674
57
556
614

To solve this, you can use the "[null-safe](http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_equal-to)" equals (`<=>`) and the `NOT` operator (as there is no "null-safe not equals"). `WHERE NOT (thing2 <=> 'this')` – gen_Eric Mar 19 '13 at 14:22
@RocketHazmat: interesting, I didn't know that MySQL had such an operator. Mind if I add it to my answer? – Joachim Sauer Mar 19 '13 at 14:24
@JoachimSauer: Go for it! :-) – gen_Eric Mar 19 '13 at 14:25
<=> is not working it returns row where thing2 equals to 'This' – user2107321 Mar 19 '13 at 14:34
@user2107321: did you include the negation `NOT`? – Joachim Sauer Mar 19 '13 at 14:34
Ok, get serious :) .I really don't know what is wrong with my query.What am i missing? – user2107321 Mar 19 '13 at 14:38
1

@user2107321: I **am** serious. What you're missing is a **clear and precise description of the problem you're seeing and of your input data**. You keep repeating us "it returns x" and "don't know what's wrong" and "what am I missing" when **you** are the only person with the necessary information to answer this question. **Either** provide us with **details** and create and post a [Short, Self Contained, Correct (Compilable), Example](http://sscce.org) **or** solve the problem yourself. – Joachim Sauer Mar 19 '13 at 14:40
@user2107321: What does your query return? What should it return? What are some example rows from your table? – gen_Eric Mar 19 '13 at 14:41
To Rocket: table looks like this - P_Id | Something | Date | Thing1 | Thing 2 and i need to receive P_Id from a row where Something = Something AND Thing1 NOT EQLAS to 'this'(above example) and Thing2 ALSO NOT EQUALS to 'this'.I know it sounds crazy.Sorry – user2107321 Mar 19 '13 at 14:49
@user2107321: that's not even close to an SSCCE. Also: you never even mentioned `Thing1` in your question above, how exactly where we suppsoed to *know* about that? – Joachim Sauer Mar 19 '13 at 14:53
Ok, so i have tried some experiments, and it apears that "thing2 <>" thingy is not working on things1/thing2 columns, but other columns like "Something" table where are also 'this' entries it works properly(finding nerest row where there is not 'this' entry).But why this isn't working with things1 and 2???i have tried rename things column, but no result. – user2107321 Mar 19 '13 at 15:36
@user2107321: do you mean "rows" when you write "tables"? Because the only table you mention in your question is `priv`. Also: Friggin' create a SSCCE! Without that you will get no further input from me. (and edit it into your question, following the information in the comments is a mess). – Joachim Sauer Mar 19 '13 at 15:38
OK, i've figured out what was causing trouble.It had something to do with NULL rows in thing1/thing2 columns i just filled it up with values and now it catches the right thing.Thank You for your answers. – user2107321 Mar 19 '13 at 17:29

score 0 · Answer 2 · answered May 28 '20 at 10:14

0

I accept @JoachimSauer's way. Also, I prefer to try below one too.

... WHERE Something = '$thing' AND NOT (IFNULL(thing2,'') = 'This')
LIMIT 1```

answered May 28 '20 at 10:14

Kuppusamy

453
3
11

MySQL not equals to isn't working

2 Answers2

Linked