query "not equal" doesn't work

Question

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

`WHERE \`deleted_1\` != 'NULL'` You shouldn´t do this. NULL is special, have a look at my answer. — TimWolla, Jan 24 '12 at 21:46
Possible duplicate of [Not equal <> != operator on NULL](https://stackoverflow.com/questions/5658457/not-equal-operator-on-null) — philipxy, May 24 '19 at 20:20

TimWolla · Accepted Answer · 2012-01-25T11:39:25.927

77

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.

edited Jan 25 '12 at 11:39

answered Jan 24 '12 at 21:35

TimWolla

31,849
8
63
96

4

I'd suggest using != because more readable, and you are using mysql, not sql :). – Andrew Jun 02 '17 at 15:55

score 11 · Answer 2 · answered Sep 01 '16 at 17:57

11

I recommend to use NULL-safe operator and negation

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

answered Sep 01 '16 at 17:57

Viktor Zeman

489
5
6

Just what I was looking for! – Gediminas Šukys Mar 06 '17 at 07:00
This is one sexy operator. – Watson Oct 29 '20 at 19:08
What makes me very curious here is why this works when others fail. Simple query: SELECT * FROM AdCoupons WHERE Deleted <> '1' ORDER BY Category . . . I have never experienced this not working. Yet it fails where yours does not. – RationalRabbit Oct 02 '22 at 23:00

greut · Answer 3 · 2012-01-24T21:40:43.120

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.

score 2 · Answer 4 · edited Aug 04 '15 at 11:24

2

Try This.. Hope It will work for you

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

edited Aug 04 '15 at 11:24

Kampai

22,848
21
95
95

answered Aug 04 '15 at 11:05

indika

21
2

score 0 · Answer 5 · answered Mar 12 '19 at 13:07

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

score 0 · Answer 6 · answered Jun 28 '20 at 06:50

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)

score -2 · Answer 7 · answered Jan 24 '12 at 21:30

-2

How about removing the single quotes around the 1?

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

answered Jan 24 '12 at 21:30

Teun Zengerink

4,277
5
30
32

This was my first thought as well, but http://dev.mysql.com/doc/refman/5.5/en/type-conversion.html seems to imply that this should happen automatically... – ziesemer Jan 24 '12 at 21:31
still nothing, at first i thought it could be problem that most of my fields are null, but even if some have a number it still doesn't select anything. – Linas Jan 24 '12 at 21:34
Hmm after running few more querys it finaly returned me rows where `deleted_1` is not `NULL` it's realy strange because i'm sure before it didn't return anything even if there was some numbers so i guess my problem is that field by default is `NULL` maybe i should set it to 0 then – Linas Jan 24 '12 at 21:37
2

it really depends what you want to obtain, rows that aren't `NULL` and aren't `1` or just rows that aren't `1`. – greut Jan 24 '12 at 21:42

query "not equal" doesn't work

7 Answers7

Linked

Related