MYSQL语法在评估时不等于null

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

我遇到了mysql查询的问题。我想排除2的值。所以我想我会做以下事情：

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.

但是，这并没有给出product1产品2的预期结果。它给出了一个空结果表。

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.

然后它产生：product3。但是我希望得到那些不等于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!

有些东西不适用于<>'2'。可能是NULL值正在抛弃它吗？任何人都可以建议修复。提前致谢！

4 个解决方案

#1

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

使用IS NULL或IS NOT NULL来比较NULL值，因为它们只是未知的。

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

SQLFiddle Demo
SQLFiddle演示
SQLFiddle Demo (added some records)

SQLFiddle演示（添加了一些记录）
Working with NULL Values

使用NULL值

#2

Sorry to open this

很抱歉打开这个

We can use this also

我们也可以使用它

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

#3

You can use:

您可以使用：

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

要么

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

#4

-4

Try this and see.

试试看，看看。

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

#1