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!
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
- SQLFiddle Demo
SQLFiddle Demo (added some records)
Working with NULL Values
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?
Sorry to open this
We can use this also
SELECT name
from products p
WHERE COALESCE(backorder,1) <> 2