CODEWITHSUNDEEP
mysqlsql
Linas
Linas

Reputation: 4408

query "not equal" doesn't work

I have very simple query like this:

SELECT * FROM `all_conversations` WHERE `deleted_1` != '1';

And my deleted_1 be default is null or some user id, but for some reason this query always returns me 0 rows, i also tried <> but still no luck what could be wrong?

EDTI So after running more querys i find out that my problems was default value of deleted_1 field, it was NULL so i modified my query and now it works fine:

SELECT *
FROM `all_conversations`
WHERE `deleted_1` != 'NULL'
AND `deleted_1` != 23

Upvotes: 36

Views: 92610

Answers (7)

MJ X
MJ X

Reputation: 9054

I agree with above Answer, just add extra detail if you have multiple AND condition like below query, you should put the column you check for not equals with or to null in first part of the Where condition, because the OR part makes your condition incorrect

  SELECT ts.*, ct.Name AS CategoryName
FROM MJ.Tasks  AS ts 
LEFT JOIN MJ.Lookup_Category AS ct ON ts.CategoryID = ct.ID
WHERE ts.Status!=1 OR ts.Status IS NULL AND ts.CategoryID = @CategoryID AND ts.UserID = @UserID AND  CAST(ts.EndDate AS DATE) = CAST(@EndDate AS DATE)

Upvotes: 0

Mohideen bin Mohammed
Mohideen bin Mohammed

Reputation: 20137

You cannot use arithmetic comparison operators such as =, <, or <> to test for NULL.

To demonstrate this for yourself, try the following query:

mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
|     NULL |      NULL |     NULL |     NULL |
+----------+-----------+----------+----------+

so you have to say,

SELECT * FROM `all_conversations` WHERE `deleted_1` <> '1' and `deleted_1` is null;

your_field IS NULL explicitly since NULL cant be club with values using arithmetic operators and it has own value BOOLEAN

Upvotes: 0

Viktor Zeman
Viktor Zeman

Reputation: 499

I recommend to use NULL-safe operator and negation

SELECT * FROM `all_conversations` WHERE NOT(`deleted_1` <=> '1');

Upvotes: 12

indika
indika

Reputation: 31

Try This.. Hope It will work for you

SELECT *
FROM `all_conversations`
WHERE `deleted_1` IS NOT NULL
AND `deleted_1` <> 23

Upvotes: 3

TimWolla
TimWolla

Reputation: 32701

SELECT * FROM all_conversations WHERE deleted_1 <> 1 OR deleted_1 IS NULL

NULL values need special treatment: http://dev.mysql.com/doc/refman/5.1/en/working-with-null.html

I'd suggest using the diamond operator (<>) in favor of != as the first one is valid SQL and the second one is a MySQL addition.

Upvotes: 77

greut
greut

Reputation: 4363

Can you try this: deleted_1 is not null and deleted_1 != '1'?

mysql> select 0 is not null and 0 != '1', 1 is not null and 1 != '1', null is not null and null != '1';
+----------------------------+----------------------------+----------------------------------+
| 0 is not null and 0 != '1' | 1 is not null and 1 != '1' | null is not null and null != '1' |
+----------------------------+----------------------------+----------------------------------+
|                          1 |                          0 |                                0 |
+----------------------------+----------------------------+----------------------------------+

Or this deleted_1 is null or deleted_1 != '1':

mysql> select 0 is null or 0 != '1', 1 is null or 1 != '1', null is null or null != '1';
+-----------------------+-----------------------+-----------------------------+
| 0 is null or 0 != '1' | 1 is null or 1 != '1' | null is null or null != '1' |
+-----------------------+-----------------------+-----------------------------+
|                     1 |                     0 |                           1 |
+-----------------------+-----------------------+-----------------------------+

It really depends on what you wanna get back.

Upvotes: 5

Teun Zengerink
Teun Zengerink

Reputation: 4393

How about removing the single quotes around the 1?

SELECT * FROM `all_conversations` WHERE `deleted_1` != 1;

Upvotes: -2

Related Questions