MYSQL syntax not evaluating not equal to in presence of NULL

Question

I am having trouble with a mysql query. I want to exclude values of 2. So I thought I would do following:

table products

id | name     | backorder
-------------------
1  | product1 | NULL
2  | product2 | NULL
3  | product3 | 2

SELECT name from `products` p
WHERE backorder <> '2'

However, This is not giving the desired result of product1, product 2 It is giving an empty results table.

On the other hand if I use

SELECT name from `products` p
WHERE backorder = '2'

Then it produces: product3. But I want to get those records where it is not equal to 2.

Something is not working with the <> '2'. Could it be that the NULL values are throwing it off? Can anyone suggest a fix.

Thanks in advance!

You can use the `NULL`-safe equality operator: `WHERE NOT backorder <=> 2` — eggyal, Apr 24 '13 at 08:24

score 118 · Accepted Answer · answered Apr 24 '13 at 08:06

118

use IS NULL or IS NOT NULL to compare NULL values because they are simply unknown.

SELECT name 
from   products p
WHERE  backorder IS NULL OR backorder <> 2

answered Apr 24 '13 at 08:06

John Woo

258,903
69
498
492

That worked. Will mark correct when SO allows me. One question...some records have the value 1 in them. To allow for case where value is NULL or 1 should I be able to do something like (backorder IS NULL OR backorder ='1') . This is not working for me at the moment. – user1904273 Apr 24 '13 at 08:16
just to clarify, do you want to get both `NULL` and with value of `1`? – John Woo Apr 24 '13 at 08:17
Yes, I want everything but value=2 – user1904273 Apr 24 '13 at 08:18
so do you want to get all records except for `backorder = 2` right? then use this condition `backorder IS NULL OR backorder <> '2'` demonstrated here: http://www.sqlfiddle.com/#!2/e0e57/2 – John Woo Apr 24 '13 at 08:19
Is the reason for having to do a separate NULL check because "Unknown value" could mean, "Well, it MIGHT be the value you're looking for, but I just don't know"? – TMH Apr 08 '16 at 12:25
it works but `IS NULL` is very slow for large data. is there any way to make it faster – Elyor Dec 15 '18 at 14:03

score 57 · Answer 2 · edited Feb 03 '21 at 16:15

57

Use the `<=>` operator.

You can use:

SELECT `name` FROM `products` `p`
WHERE NOT `backorder` <=> '2'

or

SELECT `name` FROM `products` `p`
WHERE !(`backorder` <=> '2')

See this answer for more information about the <=> operator:

What is this operator <=> in MySQL?

edited Feb 03 '21 at 16:15

Joshua Pinter

45,245
23
243
245

answered Jun 23 '17 at 13:50

simhumileco

31,877
16
137
115

3

Wow, this worked great. I didn't know `<=>` existed. I'm updating your answer with a bit more context... – Joshua Pinter Feb 02 '21 at 16:50
1

Great! Thank you @JoshuaPinter – simhumileco Feb 03 '21 at 07:49

score 8 · Answer 3 · edited Mar 16 '23 at 16:15

8

We can use this also:

SELECT p.name 
FROM   products p
WHERE  COALESCE(backorder,1) <> 2

edited Mar 16 '23 at 16:15

But those new buttons though..

21,377
10
81
108

answered May 07 '14 at 09:26

jbkhere

101
1
6

This breaks the ability to add indexes though – Brian Leishman Mar 06 '17 at 22:11

score -10 · Answer 4 · answered Apr 24 '13 at 08:06

-10

Try this and see.

SELECT name from `products` p
WHERE backorder != '2'

answered Apr 24 '13 at 08:06

sentil kumar

97
2
7

Have you checked this? – user3115056 Jan 14 '14 at 13:10
No. This doesn't work. The operators `<>` and `!=` are equivalent. See http://stackoverflow.com/questions/2066987/using-the-correct-or-preferable-not-equal-operator-in-mysql – Sergey Avdeev Aug 06 '15 at 17:16

MYSQL syntax not evaluating not equal to in presence of NULL

4 Answers4

Use the `<=>` operator.

Linked

Related

MYSQL syntax not evaluating not equal to in presence of NULL

4 Answers4

Use the <=> operator.

Linked

Related

Use the `<=>` operator.