CODEWITHSUNDEEP
mysqlsql
user1904273
user1904273

Reputation: 4764

MYSQL syntax not evaluating not equal to in presence of NULL

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!

Upvotes: 93

Views: 70293

Answers (4)

jbkhere
jbkhere

Reputation: 111

We can use this also:

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

Upvotes: 9

simhumileco
simhumileco

Reputation: 34525

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?

Upvotes: 63

sentil kumar
sentil kumar

Reputation: 95

Try this and see.

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

Upvotes: -11

John Woo
John Woo

Reputation: 263703

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

Upvotes: 128

Related Questions